Bug #45019 changing max_allowed_packet value doesn't always allow us to send large queries
Submitted: 21 May 2009 22:41 Modified: 25 Jun 2009 7:05
Reporter: Anna Poplawski Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0 and 5.1-34 OS:Microsoft Windows (Windows 2003 Server on HP machine)
Assigned to: CPU Architecture:Any
Tags: long query, max_allowed_packet, query size

[21 May 2009 22:41] Anna Poplawski
On most machines we have used, setting max_allowed_packet=32M allows us to send INSERT statements up to 32 megabytes in length to the MySQL Server. However, when we tried to do exactly the same thing, using the same data, on HP machines running Windows 2003 Server, we get the error "MySQL Server has gone away" whenever we send an INSERT statement larger than about 2.05 MB.  The number of rows in the successful INSERT statement is about 87,000.   

How to repeat:
Set max_allowed_packet to a value larger than the size of the query you intend to send.  On an HP Server machine, running Windows 2003 OS, issue a single INSERT query with a length of 2.5 or 3 MB.  We have done this using the the C API communicating via the MySQL ODBC driver, and by issuing the query directly in the mysql client.  In both cases, the client and the MySQL Server are on the same machine.  Both methods yield the same results.
[22 May 2009 7:31] Sveta Smirnova
Thank you for the report.

There could be a lot of reasons for such behavior. Most likely wrong max_allowed_packet settings for particular connection or some network problem. It can be MySQL bug also if everything set up correctly.

But we can not define what happened from this description. Please when you have access to such machine try to catch more information: set log-warnings=2 in my.cnf, then collect error log and configuration file; provide output of SHOW VARIABLES LIKE 'max_allowed_packet' in the same session you run problem INSERT.
[22 May 2009 17:12] Anna Poplawski
We already collected the information you requested.  I'm sorry I left it out of the original bug report.

We queried "show variables like 'max_allowed_packet'" in the mysql client before running the INSERT query.  It always displayed the value that we specified in the my.ini file.  We've tried setting it to 96M, 32M, and 8M.  None of those allowed us to send an INSERT statement longer than 2.05MB. 

The exact INSERT query that failed on the Windows 2003 Server machine was executed successfully on Windows XP machines with the same max_allowed_packet setting.

When we ran the query with log-warnings=2, the only thing that appeared in the error log file was
"Aborted connection 10 to db: Got an error reading communication packets".
(The actual number varied when we ran the query multiple times.)
[25 May 2009 7:05] Sveta Smirnova
Thank you for the feedback.

Could you also please upload output of SHOW CREATE TABLE problem_table and problem INSERT statement?
[25 Jun 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".