Mistakes: Importing Data with MySQL

I spent the better part of today trying to import—in various attempts—between 800 MB and 3.1 GB of data into a local MySQL server. The whole time I was Doing It Wrong™. Now I feel like this:

I was using pipe viewer, which obscured the error for most of the day. (If there’s a way to stop pv from eating messages to stderr, please let me know!) But eventually I figured out it was hitting the max_allowed_packet limit and my import was dying. I was using the MySQL docs as my reference point, and they imply that the following should work:

$ mysql --max_allowed_packet=32M db < data.sql

I know now that this does not work. And I feel like a moron. If you read carefully, which I did not, you’ll see that they’re talking about server startup, not client startup, even though the example uses “mysql” instead of “mysqld”.

When I stopped using pipe viewer, I got this series of errors:

$ mysql --max_allowed_packet=16M db < data.sql ERROR 1153 (08501) at line 175458: Got a packet \ bigger than 'max_allowed_packet' bytes $ mysql --max_allowed_packet=128M db < data.sql ERROR 1153 (08501) at line 175458: Got a packet \ bigger than 'max_allowed_packet' bytes $ mysql --max_allowed_packet=256M db < data.sql ERROR 1153 (08501) at line 175458: Got a packet \ bigger than 'max_allowed_packet' bytes $ mysql --max_allowed_packet=1G db < data.sql ERROR 1153 (08501) at line 175458: Got a packet \ bigger than 'max_allowed_packet' bytes

Now here’s the thing, data.sql was just shy of 80 MB. So obviously something was wrong.

A quick edit to /etc/my.cnf:

[mysqld] ... max_allowed_packet=32M ...

Then all I had to do was restart mysqld, and the next time I tried:

mysql db < data.sql

It finished without a problem.

7 hours and a massive facepalm later… I hope this keeps someone from wasting the same amount of time I did on such a dumb mistake.