When you run MySQL queries, sometimes you may encounter an error saying you lost connection to the MySQL server as follows:
Error Code: 2013. Lost connection to MySQL server during query
The error above commonly happens when you run a long or complex MySQL query that runs for more than a few seconds.
To fix the error, you may need to change the timeout-related global settings in your MySQL database server.
Increase the connection timeout from the command line using –connect-timeout option
If you’re accessing MySQL from the command line, then you can increase the number of seconds MySQL will wait for a connection response using the --connect-timeout option.
By default, MySQL will wait for 10 seconds before responding with a connection timeout error.
You can increase the number to 120 seconds to wait for two minutes:
mysql -uroot -proot --connect-timeout 120
You can adjust the number 120 above to the number of seconds you’d like to wait for a connection response.
Once you’re inside the mysql console, try running your query again to see if it’s completed successfully.
Using the --connect-timeout option changes the timeout seconds temporarily. It only works for the current MySQL session you’re running, so you need to use the option each time you want the connection timeout to be longer.
If you want to make a permanent change to the connection timeout variable, then you need to adjust the settings from either your MySQL database server or the GUI tool you used to access your database server.
Let’s see how to change the timeout global variables in your MySQL database server first.
Adjust the timeout global variables in your MySQL database server
MySQL database stores timeout-related global variables that you can access using the following query:
SHOW VARIABLES LIKE "%timeout";
Here’s the result from my local database. The highlighted variables are the ones you need to change to let MySQL run longer queries:
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------------+----------+
To change the variable values, you can use the SET GLOBAL query as shown below:
SET GLOBAL connect_timeout = 600;
The above query should adjust the connect_timeout variable value to 600 seconds. You can adjust the numbers as you see fit.
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) or my.ini file (Windows) 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 = 10
net_read_timeout = 30
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 connect_timeout and net_read_timeout variable values. I’d suggest setting it to at least 600 seconds (10 minutes)
Adjust timeout related variables in your MySQL GUI tools
If you’re using GUI MySQL tools like MySQL Workbench, Sequel Ace, or PHPMyAdmin, then you can also find timeout-related variables that are configured by these tools in their settings or preferences menu.
For example, in MySQL Workbench for Windows, you can find the timeout-related settings in Edit > Preferences > SQL Editor as shown below:
If you’re using Mac, then the menu should be in MySQLWorkbench > Preferences > SQL Editor as shown below:
If you’re using Sequel Ace like me, then you can find the connection timeout option in the Preferences > Network menu.
Here’s a screenshot from Sequel Ace Network settings:
For other GUI tools, you need to find the option yourself. You can try searching the term [tool name] connection timeout settings in Google to find the option.
And those are the four solutions you can try to fix the MySQL connection lost during query issue.
I hope this tutorial has been helpful for you 🙏
