Skip to content

How to import a big mysql dump (dealing with error 2006)

Importing a MySQL dump is theoretically pretty trivial:
mysql -u [username] -p [databasename] < [C:\Path\DumpName.sql]

However, in order to achieve a better speed by making fewer but large queries, phpMyAdmin tends to do things like:

INSERT INTO `sometable` (`field1`, `field12`, `field3`) VALUES
(1, 2, 40),
(1, 3, 21),
(8, 7, 35),
(1, 8, 29),
(1, 5, 41),
(5, 1, 39);

... but with much much more lines. For instance I had such a query containing over 36,000 table lines packed into a single INSERT. That's great for speed (the whole database import, total almost 38,000 lines, took a fraction of a second), but not so great for a default MySQL installation: the first time I tried to run it, I got this error message:

ERROR 2006 (HY000): MySQL server has gone away

I eventually found that this was due to too large packets. The solution is then simply to configure MySQL to accept larger packets: at the end of your my.ini (that’s the MySQL configuration file, on Windows it’s in a folder parent to the MySQL binaries folder), add the following line:

max_allowed_packet = 128M

That should be enough to deal with very large inserts already, but if you keep getting the error (after restarting the server to apply the change) I guess you can try even higher values.

Posted in MySQL.

0 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

Some HTML is OK

or, reply to this post via trackback.

Sorry about the CAPTCHA that requires JS. If you really don't want to enable JS and still want to comment, you can send me your comment via e-mail and I'll post it for you.

Please solve the CAPTCHA below in order to fight spamWordPress CAPTCHA