I worked at importing Unicode data into Codepoints.net’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.