Bug #2110 MySQL server has gone away (max_allowed_packet)

• Submitted: 13 Dec 2003 17:05
• Modified: 13 Dec 2003 20:04
• Reporter: André Wösten
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.16
• OS: Linux (Linux)
• Assigned to: Michael Widenius
• CPU Architecture: Any

[13 Dec 2003 17:05] André Wösten

Description:
Hello,

I have a table in which I store binary data, so I'm using LONGBLOB's in this case. A PHP Script is inserting the data in the table using mysql_query, which is working fine with files smaller than 16 Megabytes. 

Inserting binary data greater than 16 Megabytes results in following error:
"MySQL server has gone away"

I setted the max_allowed_packet variable to 64M, as you can see here (I defined it in the the /etc/my.cnf and also as parameter):

mysql> show variables like 'max_%';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| max_allowed_packet    | 67107840   |

I also modified variables such as net_buffer_length and several other server variables, but it didn't help. So I packed the query into a .sql file to insert it manually by using 'mysql' (mysql -u root -pmypassword < query.sql). This worked surprisingly, so I decided to check the inserted data:

mysql -u root -p
Enter password ..blabla
use mydatabase;
select * from mytable;

Then he breaked with the error message: Packet too large.
So I googled for this message and found an article which told me to set max_allowed_packet as client - so mysql - parameter. It worked, again surprisingly! Now, I tested to 'select' the data from the PHP Script which didn't work.

My question is, why doesn't it work from the PHP Script ? - I setted all necessary PHP Parameters to a max. size.

I'm using:
- Debian Linux 2.4.18
- MySQL 4.0.16
- PHP 4.3.4
- Apache 2.0.48

Some help would be really appreciated.

Sincerely,

André

How to repeat:
Check:
http://naaina.ath.cx/webportal 

to check everyting on your own. On the left site you see two files, small_file.zip, which I unpack into the table, which is shown on the right side then. If you try to unpack large_file.zip, you can see the error.

The code is located at:
http://naaina.ath.cx/webportal/index.phps

I know, this is not a page, on which you're helping people coding PHP, but I really think it's MySQL's mistake.

[13 Dec 2003 20:04] Michael Widenius

Sorry, I don't know how to set max_allowed_packet in PHP, but I hope to be able to explain how things work, which should enable you to fix this quickly.

Both server and client has the max_allowed_packet parameter for the client/server protocol).  This parameters is a safety parameter that tells how big packet it should accept (as a safety meassure to not run out of memory).

Note that the packets are allocated dynamicly and MySQL will not allocate more memory than is needed even if max_allowed_packet is too big.

If the reader gets a packet bigger than max_allowed_packet then it will assume something is wrong and abort the connection.

In other words:
- If the client sends a packet to a MySQL server that is bigger than max_allowed_packet, the server will terminate the connection.
- If the server sends a packet to a MySQL client that is bigger than max_allowed_packet, the client will terminate the connection.

To fix your problems (retriving a big blob to PHP) you have to set max_allowed_packet to a big value in your PHP environment.

Hope the above helps.
Regards,
Monty