Work Around MySQL’s “Big Packet” Error

I worked at importing Unicode data into’s database, when I encountered a peculiar MySQL error that I haven’t seen before yet:

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

I knew that my import SQL files were quite large, but I’ve used larger ones in the past without problem. Well, off to a search engine I went.

It turned out, that MySQL complained about importing large chunks of data that exceeded what it was configured for. Fortunately, one can enlarge the threshold quite easily:

set global net_buffer_length=1000000;
set global max_allowed_packet=3000000000;

I tried these settings with steadily increasing values, but no dice! The data wouldn’t import. I called it a day and left the project.

The next day I thought harder about what I did different this time compared to the last times when I imported GBs worth of data. Then the scales fell from my eyes!

For performance reasons I had recoded parts of the import to use one huge INSERT statement with lots of VALUES (...), (...), ..., where before the number of values was capped at ~500 entries. Now, this monstrous INSERT broke the packet size limit.

I re-introduced splitting the import data into smaller INSERT statements and lo! the MySQL error vanished immediately.

And the moral of the story: Premature optimization is the root of all evil.