Cara menggunakan mysql default query timeout

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

mysql -uroot -proot --connect-timeout 120

SET GLOBAL connect_timeout = 600; 

The above query should adjust the

mysql -uroot -proot --connect-timeout 120

mysql -uroot -proot --connect-timeout 120

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

mysql -uroot -proot --connect-timeout 120

mysql -uroot -proot --connect-timeout 120

To prevent the timeout error, you need to increase the

mysql -uroot -proot --connect-timeout 120

mysql -uroot -proot --connect-timeout 120

mysql -uroot -proot --connect-timeout 120

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:

MySQL Workbench Windows timeout settings

If you’re using Mac, then the menu should be in MySQLWorkbench > Preferences > SQL Editor as shown below:

MySQL Workbench Mac timeout settings

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:

Sequel Ace connection timeout option

For other GUI tools, you need to find the option yourself. You can try searching the term

mysql -uroot -proot --connect-timeout 120