MySQL Server has gone away when importing large sq

Question

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

'MySql server has gone away'

What to do?

Answer 1

I solved my issue with this short /etc/mysql/my.cnf file :

[mysqld]
wait_timeout = 600
max_allowed_packet = 100M

Answer 2

I updated "max_allowed_packet" to 1024M, but it still wasn't working. It turns out my deployment script was running:

mysql --max_allowed_packet=512M --database=mydb -u root < .\db\db.sql

Be sure to explicitly specify a bigger number from the command line if you are donig it this way.

Answer 3

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

Answer 4

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

Answer 5

I had this error and other related ones, when I imported at 16 GB SQL file. For me, editing my.ini and setting the following (based on several different posts) in the [mysqld] section:

max_allowed_packet      = 110M
innodb_buffer_pool_size=511M
innodb_log_file_size=500M
innodb_log_buffer_size = 800M
net_read_timeout        = 600
net_write_timeout       = 600

If you are running under Windows, go to the control panel, services, and look at the details for MySQL and you will see where my.ini is. Then after you edit and save my.ini, restart the mysql service (or restart the computer).

If you are using HeidiSQL, you can also set some or all of these using that.

Answer 6

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.