MySQL server has gone away on Import

A common issue I run into when migrating applications from one server to another is MySQL not handling large imports properly. Today; as a side project I took a brief amount of time to work on migrating our corporate website from Drupal to WordPress because we don’t need the powerhorse that Drupal is and WordPress is more relevant and lightweight for what we need to provide. During this trial I again came across this error that I see a few times a month:

[[email protected] wordpress]# mysql drupal_tmp < /home/ec2-user/backup-drupal.sql
ERROR 2006 (HY000) at line 3710: MySQL server has gone away

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

In the situation above I was attempting to import a 2.5GB database file to the drupal_tmp database with lots of large strings. So this was causing the connection to close when importing an object larger than the default of 1M.

An easy fix for this is just to increase the max_allowed_packet size on the server in your /etc/my.cnf or whichever configuration file your MySQL server is using.

Just add or modify the max_allowed_packet value 100M normally does the trick for me.

max_allowed_packet = 100M

And a quick restart of mysqld and you should be able to import without issue. If you see further issues just try increasing the max_allowed_packet value slowly.

Write a Comment

Your email address will not be published. Required fields are marked *

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