When an application runs a query in MySQL, from time to time, it may receive various errors. Some are related to syntax errors in the query text itself, some occur because the statement attempted an illegal operation such as for example writing a duplicate value into a column with unique constraint. But there are a few that are not as easy to figure out, because they have no direct relationship with the actual work being done. One of such error messages reads "Lost connection to MySQL server during query". What does it actually mean? If it appears frequently, how to diagnose what the problem may be?
The first thing to know about the "Lost connection to MySQL server during query" message is that this is not a MySQL error. It is a MySQL client error. The difference may seem subtle, but it is significant. A MySQL error would be a problem reported by database server, i.e. handling a query did not succeed, because the server decided it couldn't be done. A client error, on the other hand, is generated by application itself, or one of its modules in this case, for reasons that are beyond the MySQL server's control. That's why this error message won't be seen anywhere on the server.
"Lost connection to MySQL server during query" occurs when application establishes connection to MySQL, sends a query, but then does not hear back from the server before the connection is closed. In other words, a query was already sent when from the client's point of view the database disappeared.
What are the common reasons?
- A connection was killed by MySQL: Typically because someone or something ran
KILL thread_idcommand. If there are any daemons monitoring database for idle connections, such as for example pt-kill script, there could be a race condition, where a thread was qualified for killing due to long idle time, but shortly before it happened, a query arrived. The error would also happen when a long running query was killed. - MySQL was shut down or crashed while query was being executed: Check both application and database error logs and compare timestamps of these evens on either side.
- A network problem occurred: However unlikely, something between the client system and the server system may cause the connection to be interrupted. Often verifying this problem is next to impossible.
In many cases, however, diagnosing the root cause for this error message may prove to be difficult. And the more difficult the less control you have over the software environment and the infrastructure in which your applications and databases work. There is probably nothing to worry about if this only happens occasionally, but seeing this more frequently, on a regular basis, should be a cause for concern.