The difference between interactive_timeout and wait_timeout in Mysql _mysql

When using the MySQL client to operate the database, open the terminal window, if there is no operation for a period of time, the following error will often be reported:

Error 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone
No Connection. Trying to reconnect ...

This error message means that the current connection is disconnected and needs to be reconnected.

So, how is the duration of the connection confirmed?

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

First, take a look at the official document definition for these two parameters

Interactive_timeout

The default is 28800, unit seconds, or 8 hours.

The number of seconds the server waits for activity on a interactive connection before closing it. An interactive client are defined as a client that uses the Client_interactive option to Mysql_real_connect (). Also wait_timeout.

Wait_timeout

The default is also 28800s

The number of seconds the server waits for activity on a noninteractive connection before closing it.

On thread startup, the session Wait_timeout value are initialized from the global wait_timeout value or from the global int Eractive_timeout value, depending on the type of client (as defined by the Client_interactive connect option to Mysql_real _connect ()). Also interactive_timeout.

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

1> interactive_timeout for interactive connections, wait_timeout for non-interactive connections. The so-called interactive connection, that is, the client_interactive option is used in the Mysql_real_connect () function.

To be blunt, the MySQL client connection database is an interactive connection, and the database via JDBC connection is non-interactive.

2> confirm that the value of the session variable Wait_timeout is inherited from the global variable wait_timeout or interactive_timeout, depending on the type of connection when the connection is started.

Here is a test to confirm the following questions

1. Control which parameter is long when the connection is most idle.

2. Session variable Wait_timeout inheritance problem

Q1: Control which parameter is long when the connection is maximum idle

A1:wait_timeout

Verify

Modify only Wait_timeout parameters

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)

mysql> set session wait_timeout=10;
Query OK, 0 rows Affected (0.00 sec)
-------Wait 10s before executing
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

As you can see, wait for 10s before you perform the operation and the connection is disconnected.

Modify only Interactive_timeout parameters

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.06 sec)
mysql> set Session interactive_timeout=10 ;
Query OK, 0 rows Affected (0.00 sec)
----------Wait 10s to execute
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 | Ten |
| Wait_timeout | 28800 |
+---------------------+----------------+
2 rows in Set (0.06 sec)

Q2: The inheritance of Session variable Wait_timeout

A2: If it is an interactive connection, the value of the global variable interactive_timeout is inherited and the value of the global variable wait_timeout is inherited if it is a non-interactive connection.

Verify:

To modify only the values of 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 | Ten |
| Wait_timeout | 28800 |
+---------------------+----------------+
2 rows in Set (0.00 sec)

Open 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 | Ten |
| Wait_timeout | Ten |
+---------------------+----------------+
2 rows in Set (0.00 sec)

found that the value of wait_timeout has changed to 10.

But with 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 resulting output is as follows:

Interactive_timeout:10
wait_timeout:28800

To modify only the values of global variable Wait_timeout

Mysql> Select Variable_name,variable_value from Information_schema.global_variables where variable_name in (' Interactive_timeout ', ' wa
it_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 ', ' wa
it_timeout '); +---------------------+------------- ---+
| variable_name | Variable_value
| +---------------------+----------------+
| Interactive_timeout | 28800 |
| Wait_timeout | |
+---------------------+----------------+
2 rows in Set (0.00 sec)

Open 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 is still 28800.

View the results of JDBC

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 the results of JDBC

interactive_timeout:28800
wait_timeout:20

At the same time, a new program, waiting for 20s, to execute the query again, reported the following error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure

Last packet sent to the server was 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 the 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

Summarize

1. Control the Wait_timeout parameter with maximum idle length.

2. For noninteractive connections, similar to JDBC connections, wait_timeout values inherit from the server-side global variable wait_timeout.

For interactive connections, similar to the MySQL customer single connection, the Wait_timeout value inherits from the server-side global variable interactive_timeout.

3. Determine the idle time of a connection, through show processlist output in the sleep state 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 )

The above is a small set to introduce the MySQL interactive_timeout and wait_timeout differences, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!