MySQL Max Allowed Packet Size Exceeded Fix

30 November -0001
October 1, 2003

Recently I ran into a problem using MySQL where a Perl script I was running threw an error that read 'DBD::mysql::st execute failed: MySQL Server has gone away'. More investigation found that the error was in fact that the MySQL Max Allowed Packet Size had been exceeded and an insert had failed. To check the 'Max_allowed_packet_size' variable, at the command line issue:

mysqladmin variables

and you should see a whole host of variables spill out on the page. If you have any problems logging into mysqladmin you may want to try the '-u' flag (for user) or the '-p' flag to get a password prompt. In any case, updating this variable is pretty easy. Once you've got the varible's current value you should be good to go (FYI: documents that the max value here is 16 megs for versions 3.2 and 1 Gig for version 4.0+). On a Linux box you can make this change permanent by creating (or altering appropriately) a file in /etc called my.cnf which should include:

set-variable = max_allowed_packet=4000000

In my case I couldn't find the /etc/my.cnf file so I had to do a little searching. Turns out there are four templates for this file in /usr/share/mysql. These are: my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. You have to read these files and determine which configuration file (.cnf) is right for you. Next copy the appropriate file to /etc as my.cnf with

cp my-medium.cnf /etc/my.cnf

and edit the values for max_allowed_packet (you may have to add this value, include the 'set-varible=' part). Then restart MySQL (from /etc/rc.d/init.d issue './mysql -restart' or the -stop then -start combo depending) and you should be back in business.