mysql: can't set max_allowed_package to anything grater than 16MB

Question

I'm not sure if this is the right place to post these kind of questions, if it's not so, please (politely) let me know... :-)

I need to save files greater than 16MB on a mysql database from a php site...

I've already changed the c:\xampp\mysql\bin\my.cnf

and set max_allowed_packet to 16 MB, and everything worked fine

then I set it to 32 MB but there´s no way I can handle a file bigger than 16 MB

I get the following error:

'MySQL server has gone away'

(the same error I had when max_allowed_packet was set to 1MB)

there must be some other setting that doesn´t allow me to handle files bigger than 16MB

maybe the php client, I guess, but I don't know where to edit it

this is the code I'm running

when file.txt is smaller than 16.776.192 bytes long, it works fine, but

if file.txt has 16.777.216 bytes i get the aforementioned error

oh, and the field download.content is a longblob...

$file = 'file.txt';

$file_handle = fopen( $file, 'r' );

$content = fread( $file_handle, filesize( $file ) );

fclose( $file_handle );

db_execute( 'truncate table download', true );

$sql = 
"insert into download( 
    code, title, name, description, original_name, 
    mime_type, size, content, 
    user_insert_id, date_insert, user_update_id, date_update )
values (
    'new file', 'new file', 'sas.jpg', 'new file', '$file',
    'mime', " . filesize( $file ) . ", '" . addslashes( $content ) . "',
    0, " . db_char_to_sql( now_char(), 'datetime' ) . ", 0, " . db_char_to_sql( now_char(), 'datetime' ) . " )";

db_execute( $sql, true );

(the db_execute funcion just opens the connections and executes the sql stuff)

running on windows XP sp2 server version: 5.0.67-community PHP Version 4.4.9 mysql client API version: 3.23.49

using: ApacheFriends XAMPP (Basispaket) version 1.6.8 that comes with + Apache 2.2.9 + MySQL 5.0.67 (Community Server) + PHP 5.2.6 + PHP 4.4.9 + PEAR + phpMyAdmin 2.11.9.2 ...

this is part of the content of c:\xampp\mysql\bin\my.cnf

# The MySQL server
[mysqld]
port= 3306
socket= "C:/xampp/mysql/mysql.sock"
basedir="C:/xampp/mysql" 
tmpdir="C:/xampp/tmp" 
datadir="C:/xampp/mysql/data"
skip-locking
key_buffer = 16M
# max_allowed_packet = 1M
max_allowed_packet = 32M
table_cache = 128
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

Answer 1

Edit: my original answer is incorrect.

The version of the mysql client library php was linked against was (significantly) older than the mysql server version, which made it appear that what I wrote was true; however it now seems not to be the case. When I recompile php against the correct mysql client libraries updating the server-side variable is all that's required.

I'll leave the response here because someone else may find the problem and it could well be related (as was mine) to client libraries. It's also (given the original question states v3 library and v4 server) quite possibly the answer to the original question.

Run

phpinfo()

and look for "Client API version" in the mysql section to check the version.

-- old answer from here

The problem is that while there is a server variable "max_allowed_packet", there is also one set in the client. You can set this in the mysql client using

mysql --max_allowed_packet 

or by using

set-variable   = max-allowed-packet=64M

in the [client] section of the my.cnf

Unfortunately php doesn't read my.cnf and doesn't allow you to set this client-side variable either. You are therefore stuck with the compile-time limit in the php source:

ext/mysql/libmysql/net.c:ulong max_allowed_packet=16*1024*1024L;

If you recompile the php module with this limit modified it should fix your problem but as others have pointed out, you should really be doing things differently.