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: MySQL.com 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:
[mysqld] 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.