If you spend time running lots of MySQL queries, you might come across the Error Code: 2013. Lost connection to MySQL server during query. This article offers some suggestions on how to avoid or fix the problem.
Why this happens
This error appears when the connection between your MySQL client and database server times out. Essentially, it took too long for the query to return data so the connection gets dropped.
Most of my work involves content migrations. These projects usually involve running complex MySQL queries that take a long time to complete. I’ve found the WordPress wp_postmeta table especially troublesome because a site with tens of thousands of posts can easily have several hundred thousand postmeta entries. Joins of large datasets from these types of tables can be especially intensive.
Avoid the problem by refining your queries
In many cases, you can avoid the problem entirely by refining your SQL queries. For example, instead of joining all the contents of two very large tables, try filtering out the records you don’t need. Where possible, try reducing the number of joins in a single query. This should have the added benefit of making your query easier to read. For my purposes, I’ve found that denormalizing content into working tables can improve the read performance. This avoids time-outs.
Re-writing the queries isn’t always option so you can try the following server-side and client-side workarounds.
Server-side solution
If you’re an administrator for your MySQL server, try changing some values. The MySQL documentation suggests increasing the net_read_timeout or connect_timeout values on the server.
Client-side solution
You can increase your MySQL client’s timeout values if you don’t have administrator access to the MySQL server.
MySQL Workbench
You can edit the SQL Editor preferences in MySQL Workbench:
- In the application menu, select Edit > Preferences > SQL Editor.
- Look for the MySQL Session section and increase the DBMS connection read time out value.
- Save the settings, quite MySQL Workbench and reopen the connection.
Navicat
How to edit Navicat preferences:
- Control-click on a connection item and select Connection Properties > Edit Connection.
- Select the Advanced tab and increase the Socket Timeout value.
Command line
On the command line, use the connect_timeout variable.
Python script
If you’re running a query from a Python script, use the connection argument:
con.query('SET GLOBAL connect_timeout=6000')
