Solving a MySQL “server has gone away” gotcha

I recently spent ages trying to get to the bottom of a MySQL “server has gone away” error. I tried everything and went back and forth with the hosting company. We tried adding RAM, strealining the queries, removing foreign keys, etc, to no avail.

The script in question was a migration script. It read in some data from a third-party via cURL, stored the data in paginated JSON files and then converted the JSON to MySQL.

The red herring was that we thought the amount of data involved was cuasing memory issues. It turns out that had nothing to do with it. It was to do with the code that connected to the database. The order of the script went like this:

1. Connect to the database
2. Read in data via cURL
3. Store data as JSON
4. DELETE * from current tables (i.e. to start again from scratch)
5. Iterate through JSON files and INSERT data

The script would fail when the database queries started making us think it was to do with the amount of data contained within the JSON files — but the files were only about 20MB in total. So, then we thought maybe it was running out of memory building the foreign keys.

It turns out it was because when you connect to the database but the connection stays idle for too long, the connection is broken, resulting in the vague “server has gone away” error (the cURL part of my script was fairly slow: 5+ minutes).

Changing the order of the code to this fixed the issue:

1. Read in data via cURL
2. Store data as JSON
3. Connect to the database
4. Do all MySQL queries