MySQL Server has gone away when importing large sql file

I tried to import a large sql file through phpMyAdmin...But it kept showing error

'MySql server has gone away'

What to do?

 Answers

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 the their values, type in the console:
select @@wait_timeout;
select @@max_allowed_packet;

---

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.

Hope this helps someone.

---

The other reason this can happen is running out of memory. Check /var/log/messages and make sure that your my.cnf is not set up to cause mysqld to allocate more memory than your machine has.

Your mysqld process can actually be killed by the kernel and then re-started by the "safe_mysqld" process without you realizing it.

Use top and watch the memory allocation while it's running to see what your headroom is.

make a backup of my.cnf before changing it.

---

If your data includes BLOB data:

Note that an import of data from the command line seems to choke on BLOB data, resulting in the 'MySQL server has gone away' error.

To avoid this, re-create the mysqldump but with the --hex-blob flag:

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_hex-blob

which will write out the data file with hex values rather than binary amongst other text.

PhpMyAdmin also has the option "Dump binary columns in hexadecimal notation (for example, "abc" becomes 0x616263)" which works nicely.

Note that there is a long-standing bug (as of December 2015) which means that GEOM columns are not converted: Back up a table with a GEOMETRY column using mysqldump? so using a program like PhpMyAdmin seems to be the only workaround (the option noted above does correctly convert GEOM columns).

---

If it takes a long time to fail, then enlarge the wait_timeout variable.

If it fails right away, enlarge the max_allowed_packet variable; it it still doesn't work, make sure the command is valid SQL. Mine had unescaped quotes which screwed everything up.

Also, if feasible, consider limiting the number of inserts of a single SQL command to, say, 1000. You can create a script that creates multiple statements out of a single one by reintroducing the INSERT... part every n inserts.

---

I had similar error today when duplicating database (MySQL server has gone away...), but when I tried to restart mysql.server restart I got error

ERROR! The server quit without updating PID ...

This is how I solved it: I opened up Applications/Utilities/ and ran Activity Monitor

 quit mysqld

then was able to solve the error problem with

mysql.server restart

---

Make sure mysqld process does not restart because of service managers like systemd.

I had this problem in vagrant with centos 7. Configuration tweaks didn't help. Turned out it was systemd which killed mysqld service every time when it took too much memory.