在用mysql客戶端對數據庫進行操作時，打開終端窗口，如果一段時間沒有操作，再次操作時，常常會報如下錯誤：
|
1
2
3
|
ERROR 2013 (HY000): Lost connection to MySQL server during queryERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect... |
這個報錯信息就意味著當前的連接已經斷開，需要重新建立連接。
那么，連接的時長是如何確認的？
其實，這個與interactive_timeout和wait_timeout的設置有關。
首先，看看官方文檔對于這兩個參數的定義
interactive_timeout
默認是28800，單位秒，即8個小時
|
1
|
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
默認同樣是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 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.
根據上述定義，兩者的區別顯而易見
1> interactive_timeout針對交互式連接，wait_timeout針對非交互式連接。所謂的交互式連接，即在mysql_real_connect()函數中使用了CLIENT_INTERACTIVE選項。
說得直白一點，通過mysql客戶端連接數據庫是交互式連接，通過jdbc連接數據庫是非交互式連接。
2> 在連接啟動的時候，根據連接的類型，來確認會話變量wait_timeout的值是繼承于全局變量wait_timeout，還是interactive_timeout。
下面來測試一下，確認如下問題
1. 控制連接最大空閑時長的是哪個參數。
2. 會話變量wait_timeout的繼承問題
Q1：控制連接最大空閑時長的是哪個參數
A1：wait_timeout
驗證
只修改wait_timeout參數
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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)-------等待10s后再執行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 |
可以看到，等待10s后再執行操作，連接已經斷開。
只修改interactive_timeout參數
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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)----------等待10s后執行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 | 28800 |+---------------------+----------------+2 rows in set (0.06 sec) |
Q2：會話變量wait_timeout的繼承問題
A2：如果是交互式連接，則繼承全局變量interactive_timeout的值，如果是非交互式連接，則繼承全局變量wait_timeout的值。
驗證：
只修改全局變量interactive_timeout的值
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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) |
開啟另外一個mysql客戶端，查看會話變量的值
|
1
2
3
4
5
6
7
8
|
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) |
發現，WAIT_TIMEOUT的值已經變為10了。
但通過jdbc測試，wait_timeout的值依舊是28800
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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));}}} |
結果輸出如下：
INTERACTIVE_TIMEOUT: 10
WAIT_TIMEOUT: 28800
只修改全局變量wait_timeout的值
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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) |
開啟另外一個mysql客戶端，查看會話變量的值
|
1
2
3
4
5
6
7
8
|
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) |
WAIT_TIMEOUT的值依舊是28800.
查看jdbc的結果
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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));}}} |
查看jdbc的結果
|
1
2
|
INTERACTIVE_TIMEOUT: 28800WAIT_TIMEOUT: 20 |
同時，新增了一段程序，等待20s后，再次執行查詢，報如下錯誤：
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
Last 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 failedat 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 |
總結
1. 控制連接最大空閑時長的wait_timeout參數。
2. 對于非交互式連接，類似于jdbc連接，wait_timeout的值繼承自服務器端全局變量wait_timeout。
對于交互式連接，類似于mysql客戶單連接，wait_timeout的值繼承自服務器端全局變量interactive_timeout。
3. 判斷一個連接的空閑時間，可通過show processlist輸出中Sleep狀態的時間
|
1
2
3
4
5
6
7
8
|
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) |
以上所述是小編給大家介紹的MySQL中interactive_timeout和wait_timeout的區別，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
原文鏈接：http://www.cnblogs.com/ivictor/archive/2016/10/20/5979731.html
