How to fix a MySQL "packet bigger than 'max_allowed_packet' bytes" error (Error 1153)
I nearly always rely on the command line when moving data and / or schema changes between two different MySQL servers, exporting data with mysqldump
then importing via something like:
mysql -u foo -p dbname < script.sql
When running this the other day though I was presented with the following error:
ERROR 1153: Got a packet bigger than ‘max_allowed_packet’ bytes
Fortunately help was at hand via this forum post. All I needed to do was run some SQL statements to set some global variables in MySQL like so:
set global max_allowed_packet = 1000*1024*1024;
set global net_buffer_length = 1000000;
To check whether this worked was merely a matter of running:
-- should return 1048576000
select @@max_allowed_packet;
Of course, you could always set the max_allowed_packet
in your my.cnf
file instead.
Update
After upgrading my MacBook Pro to OS X Snow Leopard, error 1153 was replaced with:
ERROR 2006: MySQL server has gone away
Fortunately the above still fixed things. Of course, if I’d listened to Phil Sherry in the first place I might not have had to re-install MySQL after upgrading OS X (although installing MySQL via the binary package installer did seem to install in /usr/local/bin
leaving me puzzled as to why it disappeared after the Snow Leopard upgrade. But that’s a story for another day…).
*[SQL]: Structured Query Language