Max Packet Size PHP Error

We ran into this error PHP Warning: Error while sending QUERY packet. PID=25016 the other day when trying to run an update query on a database with approximately 3.8 million records in it. New data had become available for 2.1 million of these records.

These were submitted using a PHP program which fetched and parsed enormous CSV files before using a PDO statement to insert or update the records into MySQL. Upon running the program, the PHP warning would appear.

A quick Google suggested that the way to resolve this issue was to increase the ‘max_allowed_packet’ setting in the ‘my.cnf’ configuration file. This had no effect. We increased the timeout settings for good measure, which achieved an identical level of success.

Then came the proverbial lightbulb moment: make the csv files smaller. This was more difficult than it sounds, given that they were so big our systems couldn’t actually open the file in a text editor.

And so, to command line. Handily, there’s a ‘split’ command for Linux which splits text files into several new files. We used split -l 25000 -d [file_name] [new_file_prefix] to split the csv into smaller files, each containing 25,000 rows, called [new_file_prefix00] and so on.

As you can probably work out, -l dictates the number of lines each file should contain, while -d means that the new files end in digits rather than letters, e.g. 00,01… rather than aa, ab…

You can also use -b instead of -l to split the csvs into files of a particular size in bytes, which may be useful in some situations but in our case had the unfortunate result of unceremoniously splitting the files mid-record.

Our new smaller csv files updated successfully inside MySQL, though we’re still none the wiser as to what was causing the original error. Still, we hope that one or two people might find this helpful, given that the split command is not necessarily one you’d come across every day.