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

I have come across this issue in the past with PHP and MySQL.

I remember that my solution broke the binary up into chunks and looped over the array. First it would INSERT a new row, then it would UPDATE the row using the CONCAT statement.

It was nasty, but it did the trick.

Actually, in the end, we stored the binary contents on file and kept a pointer to the file in the database. It was much more efficient.

Answer 2

Extending the max_allowed_packet for this reason is the result of a bad implementation. I suggest reading this article/implementation for file storage. You can store files of any size.

Answer 3

There is also max alowed upload limit in php.ini file. It's not directly connected to MySQL but if you are using php to send data it could be your problem.

; Maximum allowed size for uploaded files.
upload_max_filesize = 16M

; Maximum size of POST data that PHP will accept.
post_max_size = 16M

Answer 4

Thanks everybody

Beredon, I'll try the concat stuff... thanks

DreamWerx, you're right, I've already seen that article, it looks nice, but I didn't want to go thru all that trouble, just wanted to find which was the 16MB setting I was missing...

Perhaps it's some setting on the client, in this case it would be the mysql library that comes with php 4,

PHP Version 4.4.9 mysql client API version: 3.23.49

anybody can give a clue about it???

Answer 5

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.