Troubleshooting MySQL

MySQL has gone away (Error 2006)

Problem

Or maybe you saw an error about packet length? This can be caused by the client and/or server trying to handle data larger than the maximum packet length. The default is 1 MB. Some of the larger records (e.g., form data) can easily exceed this maximum when multiple records are placed into the same SQL statement (e.g., when performing a SQL dump or loading scripts).

Solution

Increase the max_allowed_packet setting for MySQL

• Edit your INI file (e.g. for Windows, C:\Program Files\MySQL\MySQL Server x.x\my.ini).
  • You must edit the actual INI file in use. MySQL comes with several alternative configurations (INI configurations). If you use the administration program, you should be able to find the max_allowed_packet setting — I found it using MySQL Administrator under the "Health" section's "System Variables" tab (Connections → Networking → max_allowed_packet in the hierarchy). [This is with MySQL 4.1.9 and MySQL Administrator version 1.0.19, the details may differ between versions).
• On Linux, edit the /etc/my.cnf file
• Under the [mysqld] section, add the line:

max_allowed_packet=64M

• You can safely increase this setting up to 128M or higher (max for 5.0 is 1GB, I believe) as MySQL only uses the memory as needed.

Restart MySQL

• Your client may need to be adjusted as well. For mysql.exe (the MySQL command line), you can use the command:

mysql --max_allowed_packet=64M

• other clients may have a different method for changing this value

I would suggest googling on something like "MySQL has gone away" or mysql max_allowed_packet for up-to-date references on this topic.

In my case, I was trying to load a 32 megabyte SQL script and kept getting the not-so-helpful "MySQL has gone away" error, indicating the the MySQL server was having trouble and disconnecting in the middle of the query. I set the max_allowed_packet to 64M and my query ran successfully. Hopefully this tip will save you some time. -Burke