Seen this error before from MySQL?

MySQL server has gone away

I’ve hit this numerous times over the years. The common case has been when I’ve kept hold of a database handle for a long time and then tried to use it after a long period of inactivity. This is the primary cause, as mentioned by the docs. Often that’s easily solved by setting the DB handle in our script to auto reconnect, or setting a long value for wait_timeout on the MySQL server.

Yesterday however this didn’t work. Despite auto reconnect being set, and a generous amount of time set for the relevant timeouts, the server was still dropping the connection after approximately 15 minutes. Some time later, after a bit of digging in the script, I managed to get a demonstrable test case where the error would occur after less than 60 seconds. This definitely didn’t seem to be a genuine timeout!

It was at this point I started to wonder if I’d actually managed to set the auto reconnect attribute at all. I outputted the state of the DB driver at various points during the script but it always seemed fine. For good measure I also outputted the raw SQL, just to see what was being sent to the server.

This is where the penny dropped.

The query is auto generated by the script depending on the result of other earlier queries. In this case, our problem query was attempting to run with millions of values inside a SQL IN clause. Let’s go back to the docs for this error:

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent)...

Then a couple of paragraphs later:

Some other common reasons for the MySQL server has gone away error are:

• [6 other reasons... then...]
• You can also get these errors if you send a query to the server that is incorrect or too large.

Mystery solved. Breaking up the query into chunks solved the problem.

I think the moral of the story is to fully read the docs. Other take aways from this debugging episode are that it’s worth seeing the full state of the problem at the start (i.e. if I’d dumped the SQL query right at the beginning it would have been more obvious that the query was questionable large).

Having said that, it would be nice if MySQL didn’t give error messages using smoke and mirrors.