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

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

Answer 2

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.

Answer 3

i got a similar error.. to solve this just open my.ini file..here at line no 36 change the value of maximum allowed packet size ie. max_allowed_packet = 20M

Answer 4

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.

Answer 5

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).