The difference between interactive_timeout and wait_timeout in MySQL is mysqlwaittimeout.

The difference between interactive_timeout and wait_timeout in MySQL is mysqlwaittimeout.

When you use the mysql client to operate the database, open the terminal window. If the operation is not performed for a period of time, the following error is often reported:

ERROR 2013 (HY000): Lost connection to MySQL server during query

ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...

 

This error message indicates that the current connection has been disconnected and a new connection is required.

 

How is the connection duration confirmed?

In fact, this is related to the settings of interactive_timeout and wait_timeout.

 

First, take a look at the definition of the two parameters in the official documentation.

Interactive_timeout

The default value is 28800, in seconds, that is, 8 hours.

The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

 

Wait_timeout

The default value is also 28800 s.

The number of seconds the server waits for activity on a noninteractive connection before closing it.On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

 

According to the above definition, the difference between the two is obvious.

1> interactive_timeout is for interactive connections, while wait_timeout is for non-interactive connections. The CLIENT_INTERACTIVE option is used in the mysql_real_connect () function.

To put it bluntly, connecting to a database through a mysql client is an interactive connection, and connecting to a database through jdbc is a non-interactive connection.

2> when the connection is started, check whether the wait_timeout value of the session variable inherits from the global variable wait_timeout or interactive_timeout Based on the connection type.

 

Let's test it and confirm the following problems:

1. Which parameter controls the maximum idle duration of the connection.

2. Inheritance of the session variable wait_timeout

 

Q1: Which parameter controls the maximum idle duration of the connection?

A1: wait_timeout

Verify

Only the wait_timeout parameter is modified.

Mysql> select variable_name, variable_value from information_schema.session_variables where variable_name in ('interactive _ timeout', 'Wait _ timeout '); + rows + -------------- + | variable_name | variable_value | + rows + ---------------- + | INTERACTIVE_TIMEOUT | 28800 | WAIT_TIMEOUT | 28800 | + rows + ---------------- + 2 rows in set (0.03 sec) mysql> set session WAIT_TIMEOUT = 10; Query OK, 0 rows affected (0.00 sec) ------- wait 10 s and then execute mysql> select variable_name, variable_value from information_schema.session_variables where variable_name in ('interactive _ timeout', 'Wait _ timeout'); ERROR 2013 (HY000): Lost connection to MySQL server during query

You can see that the connection has been disconnected after 10 seconds.

 

Only modify the interactive_timeout Parameter

Mysql> select variable_name, variable_value from information_schema.session_variables where variable_name in ('interactive _ timeout', 'Wait _ timeout '); + rows + -------------- + | variable_name | variable_value | + rows + ---------------- + | INTERACTIVE_TIMEOUT | 28800 | WAIT_TIMEOUT | 28800 | + rows + ---------------- + 2 rows in set (0.06 sec) mysql> set session INTERACTIVE_TIMEOUT = 10; Query OK, 0 rows affected (0.00 sec)
---------- Wait 10 seconds and then execute mysql> select variable_name, variable_value from information_schema.session_variables where variable_name in ('interactive _ timeout', 'Wait _ timeout '); + rows + -------------- + | variable_name | variable_value | + rows + ---------------- + | INTERACTIVE_TIMEOUT | 10 | WAIT_TIMEOUT | 28800 | + rows + ---------------- + 2 rows in set (0.06 sec)

 

Q2: inheritance of the session variable wait_timeout

A2: for interactive connections, the value of the global variable interactive_timeout is inherited. For non-interactive connections, the value of the global variable wait_timeout is inherited.

Verification:

Only change the value of the global variable interactive_timeout.

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout'); 
+---------------------+----------------+| variable_name       | variable_value |+---------------------+----------------+| INTERACTIVE_TIMEOUT | 28800          || WAIT_TIMEOUT        | 28800          |+---------------------+----------------+2 rows in set (0.13 sec)mysql> set global INTERACTIVE_TIMEOUT=10;Query OK, 0 rows affected (0.00 sec)mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+| variable_name       | variable_value |+---------------------+----------------+| INTERACTIVE_TIMEOUT | 10             || WAIT_TIMEOUT        | 28800          |+---------------------+----------------+2 rows in set (0.00 sec)

 

Enable another mysql client to view the value of the session variable.

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+| variable_name       | variable_value |+---------------------+----------------+| INTERACTIVE_TIMEOUT | 10             || WAIT_TIMEOUT        | 10             |+---------------------+----------------+2 rows in set (0.00 sec)

It is found that the value of WAIT_TIMEOUT has changed to 10.

 

However, through the jdbc test, the value of wait_timeout is still 28800

public class Jdbc_test {    @SuppressWarnings("static-access")    public static void main(String[] args) throws Exception {         Connection conn = null;         Statement stmt = null;         ResultSet rs = null;         String url = "jdbc:mysql://192.168.244.10:3306/test";         String user = "root";         String password = "123456";         Class.forName("com.mysql.jdbc.Driver");         conn = DriverManager.getConnection(url, user, password);         stmt = conn.createStatement();         String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";         rs = stmt.executeQuery(sql);         while (rs.next()) {             System.out                     .println(rs.getString(1)+":  "+rs.getString(2));         }    }}

The output is as follows:

INTERACTIVE_TIMEOUT:  10WAIT_TIMEOUT:  28800

 

 Only the value of the global variable wait_timeout is modified.

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');+---------------------+----------------+| variable_name       | variable_value |+---------------------+----------------+| INTERACTIVE_TIMEOUT | 28800          || WAIT_TIMEOUT        | 28800          |+---------------------+----------------+2 rows in set (0.17 sec)mysql> set global WAIT_TIMEOUT=20;Query OK, 0 rows affected (0.07 sec)mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');+---------------------+----------------+| variable_name       | variable_value |+---------------------+----------------+| INTERACTIVE_TIMEOUT | 28800          || WAIT_TIMEOUT        | 20             |+---------------------+----------------+2 rows in set (0.00 sec)

 

Enable another mysql client to view the value of the session variable.

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');+---------------------+----------------+| variable_name       | variable_value |+---------------------+----------------+| INTERACTIVE_TIMEOUT | 28800          || WAIT_TIMEOUT        | 28800          |+---------------------+----------------+2 rows in set (0.03 sec)

The value of WAIT_TIMEOUT remains 28800.

 

View jdbc results

public class Jdbc_test {    @SuppressWarnings("static-access")    public static void main(String[] args) throws Exception {         Connection conn = null;         Statement stmt = null;         ResultSet rs = null;         String url = "jdbc:mysql://192.168.244.10:3306/test";         String user = "root";         String password = "123456";         Class.forName("com.mysql.jdbc.Driver");         conn = DriverManager.getConnection(url, user, password);         stmt = conn.createStatement();         String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";         rs = stmt.executeQuery(sql);         while (rs.next()) {             System.out                     .println(rs.getString(1)+":  "+rs.getString(2));         }         Thread.currentThread().sleep(21000);         sql = "select 1 from dual";         rs = stmt.executeQuery(sql);         while (rs.next()) {             System.out                     .println(rs.getInt(1));         }    }}

View jdbc results

INTERACTIVE_TIMEOUT:  28800WAIT_TIMEOUT:  20

At the same time, a new program is added. Wait 20 s and run the query again. The following error is reported:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureLast packet sent to the server was 12 ms ago.    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)    at java.lang.reflect.Constructor.newInstance(Unknown Source)    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)    at com.victor_01.Jdbc_test.main(Jdbc_test.java:29)Caused by: java.net.SocketException: Software caused connection abort: recv failed    at java.net.SocketInputStream.socketRead0(Native Method)    at java.net.SocketInputStream.socketRead(Unknown Source)    at java.net.SocketInputStream.read(Unknown Source)    at java.net.SocketInputStream.read(Unknown Source)    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452)    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906)    ... 8 more

 

Summary

1. The wait_timeout parameter controls the maximum idle duration of the connection.

2. For non-interactive connections, similar to jdbc connections, the value of wait_timeout inherits from the global variable wait_timeout on the server side.

For interactive connections, similar to a mysql client single connection, the value of wait_timeout inherits from the global variable interactive_timeout on the server.

3. Determine the idle time of a connection. You can use show processlist to output the Sleep status time.

mysql> show processlist;+----+------+----------------------+------+---------+------+-------+------------------+| Id | User | Host                 | db   | Command | Time | State | Info             |+----+------+----------------------+------+---------+------+-------+------------------+|  2 | root | localhost            | NULL | Query   |    0 | init  | show processlist ||  6 | repl | 192.168.244.20:44641 | NULL | Sleep   | 1154 |       | NULL             |+----+------+----------------------+------+---------+------+-------+------------------+2 rows in set (0.03 sec)

 

Reference

1. http://www.cnblogs.com/cenalulu/archive/2012/06/20/2554863.html

2. http://www.cnblogs.com/Alight/p/4118515.html

3. http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/