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

 

 

 

One comment

  • AA

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

Leave a Reply

Your email address will not be published. Required fields are marked *