ERROR 2006 (HY000): MySQL server has gone away

Today while creating local copy of one of my WordPress sites I got a following message when extracting the Mysql dump:
ERROR 2006 (HY000) at line 1086: MySQL server has gone away
Hmmmmm…
Well this first dump was created by Sypex Dumper so I tried getting another dump from mysqldump and that one went well. Being curious of what happened with the first dump I played a little bit with the dumpfile and got another message:
ERROR 1153 (08S01) at line 1086: Got a packet bigger than 'max_allowed_packet' bytes
This is much more interesting. The packet too large error happens when you send a packet to the server that is larger than the size of max_allowed_packet directive in my.ini. By default it’s only 1 Megabyte so if you have long sql-queries in your dump (for instance if you have TEXT fields in the database) you’ll probably get this error. Ok, changing the server value have helped, but now we are getting that first mystic error again. What’s wrong?
The thing is that besides the server directive there is also the max_allowed_packet limit on the client side and it’s 16M for mysql client by default. So we have to change our restore call to something like:
shell> mysql --max_allowed_packet=200M db_name < path_to_dump.sql
and one again ensure that my.ini section has the same or bigger value:
[mysqld]
max_allowed_packet=200M

Now after mysqld restart everything will work fine.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.