"Error Code: 2013. Lost connection to MySQL server during query" after 300 seconds

[simondup]

Hi,

I have been getting "Error Code: 2013. Lost connection to MySQL server during query" when a query hits 300 seconds.

I am connecting to CloudSQL (MySQL 5.7.39) using MySQLWorkbench (Ver 8.0.1.3) for Mac.

I have followed the usual solution and set some of the global variables for timeouts in MySQL

After 300 seconds I can see that the query is still running on the server but the error has appeared in the client.

So I tried a completely different client to isolate MySQLWorkbench and ran the query using Querious (Ver 3.3.2) and there again after 300 seconds the query fails with error 2013.

The client timeout settings are also set to above 300 seconds

There is nothing in the MySQL error logs with regard to the query.

Any suggestions ?

Thanks

Simon

[christianpaula]

Hi @simondup,

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:

1. In the application menu, select Edit > Preferences > SQL Editor.
2. Look for the MySQL Session section and increase the DBMS connection read time out value.
3. Save the settings, exit MySQL Workbench and reopen the connection.

Adjust the timeout variables in your MySQL configuration files

Alternatively, if you’re using a MySQL configuration file to control the settings of your connections, then you can edit the my.cnf file (Mac) used by your MySQL connection.

Open that configuration file using the text editor of your choice and try to find the following variables in mysqld:

[mysqld]
connect_timeout = 31536000
net_read_timeout = 31536000
wait_timeout = 28800
interactive_timeout = 28800

The wait_timeout and interactive_timeout variables shouldn’t cause any problem because they usually have 28800 seconds (or 8 hours) as their default value.

To prevent the timeout error, you need to increase the connection_timeout and net_read_timeout variable values.

Thank you