MYSQL import fails with “ERROR 2006 (HY000) at line 44: MySQL server has gone away”
Recently I was attempting to import a large (136mb) SQL backup for a new client.
I first created a database and db user in PHPMYADMIN (most current version).
Then using my Windows workstation, I launched MySQL Workbench 6.1 (my favorite tool for importing/exporting) and tried to do a Server–>Data Import.
Pointed it to the 136mb file and first had a problem:
17:45:03 Restoring Z:\whateverdatabase.sql Running: mysql.exe --defaults-file="c:\users\aaa\appdata\local\temp\tmp8pbfwe.cnf" --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < "Z:\\whateverdatabase.sql" ERROR 2006 (HY000) at line 44: MySQL server has gone away Operation failed with exitcode 1 17:45:04 Import of Z:\whateverdatabase.sql has finished with 1 errors
Want an easy solution for this? It turns out there is a limit in the MySQL database for the size of imports…
Run this SQL statement against the MySQL database:
SET GLOBAL max_allowed_packet=250x1024x1024;In my case, I picked a size (250mb) (above) that is larger than my SQL import.
Execute that script.
Re-run the import. Works perfectly!
17:47:42 Restoring Z:\whateverdatabase.sql Running: mysql.exe --defaults-file="c:\users\aaa\appdata\local\temp\tmpwpesfe.cnf" --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < "Z:\\whateverdatabase.sql" 17:50:18 Import of Z:\whateverdatabase.sql has finished
Thanks, that saved my skin. I was also using Workbench and scoured google, several posts mentioned changing max_allowed_packet which i did through the Workbench -> Options menu, but no joy. Running the above command via the query editor…works :))