MySQL Server has gone away when importing large sql file

Question

As stated here:

Two most common reasons (and fixes) for the MySQL server has gone away (error 2006) are:

Server timed out and closed the connection. How to fix:

1. check that wait_timeout variable in your mysqld’s my.cnf configuration file is large enough. On Debian: sudo nano /etc/mysql/my.cnf, set wait_timeout = 600 seconds (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart. I didn't check, but the default value for wait_timeout might be around 28800 seconds (8 hours).

2. Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file. On Debian: sudo nano /etc/mysql/my.cnf, set max_allowed_packet = 64M (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart.

Edit:

Notice that MySQL option files do not have their commands already available as comments (like in php.ini for instance). So you must type any change/tweak in my.cnf or my.ini and place them in mysql/data directory or in any of the other paths, under the proper group of options such as [client], [myslqd], etc. For example:

[mysqld]
wait_timeout = 600
max_allowed_packet = 64M

Then restart the server. To get their values, type in the mysql client:

> select @@wait_timeout;
> select @@max_allowed_packet;

For me this solution didn't work out so I executed

SET GLOBAL max_allowed_packet=1073741824;

in my SQL client.

If not able to change this with MYSql service running, you should stop the service and change the variable in "my.ini" file.

For example:

max_allowed_packet=20M

If you are working on XAMPP then you can fix the MySQL Server has gone away issue with following changes..

open your my.ini file my.ini location is (D:\xampp\mysql\bin\my.ini)

change the following variable values

max_allowed_packet = 64M
innodb_lock_wait_timeout = 500

If you are running with default values then you have a lot of room to optimize your mysql configuration.

The first step I recommend is to increase the max_allowed_packet to 128M.

Then download the MySQL Tuning Primer script and run it. It will provide recommendations to several facets of your config for better performance.

Also look into adjusting your timeout values both in MySQL and PHP.

How big (file size) is the file you are importing and are you able to import the file using the mysql command line client instead of PHPMyAdmin?

If you are using MAMP on OS X, you will need to change the max_allowed_packet value in the template for MySQL.

1. You can find it at: File > Edit template > MySQL my.cnf

2. Then just search for max_allowed_packet, change the value and save.