MySQL之wait/interactive_timeout

MySQL之wait/interactive_timeout

引言

在用mysql客戶端對資料庫進行操作時,打開終端窗口,如果一段時間沒有操作,再次操作時,常常會報如下錯誤:

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的設置有關。

1. interactive_timeout參數定義

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.

interactive_timeout針對互動式連接,wait_timeout針對非互動式連接。所謂的互動式連接,即在mysql_real_connect()函數中使用了CLIENT_INTERACTIVE選項。說得直白一點,通過mysql客戶端連接資料庫是互動式連接,通過jdbc連接資料庫是非互動式連接。

默認值:28800,單位秒,即8個小時

2. wait_timeout參數定義

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.

伺服器關閉非交互連接之前等待活動的秒數。在線程啟動時,根據全局wait_timeout值或全局interactive_timeout值初始化會話wait_timeout值,取決於客戶端類型(由mysql_real_connect()的連接選項CLIENT_INTERACTIVE定義)。

默認值:28800,單位秒,即8個小時


控制連接最大空閑時長的參數及驗證

控制連接最大空閑時長的參數是: wait_timeout

驗證: 1. 只修改wait_timeout參數

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 |+---------------------+----------------+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後再執行操作,連接已經斷開。

驗證: 2. 只修改interactive_timeout參數 **

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 |+---------------------+----------------+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 |+---------------------+----------------+rows in set (0.06 sec)

可以看到,等待10s後再執行操作,連接沒有斷開。


會話變數wait_timeout的繼承問題

如果是互動式連接,則繼承全局變數interactive_timeout的值,如果是非互動式連接,則繼承全局變數wait_timeout的值。

驗證1: 只修改全局變數interactive_timeout的值

1. 互動式連接修改INTERACTIVE_TIMEOUT值

  • 打開一個Mysql客戶端修改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 |+---------------------+----------------+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 |+---------------------+----------------+rows in set (0.00 sec)

  • 開啟另外一個mysql客戶端,查看會話變數的值

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 |+---------------------+----------------+rows in set (0.00 sec)

WAIT_TIMEOUT的值已經變為10,繼承INTERACTIVE_TIMEOUT的值。

2. 非互動式連接修改INTERACTIVE_TIMEOUT值

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: 10WAIT_TIMEOUT: 28800

wait_timeout的值依舊是28800,沒有繼承INTERACTIVE_TIMEOUT的值

驗證2: 只修改全局變數wait_timeout的值

1. 互動式連接修改wait_timeout值

  • 打開一個Mysql客戶端修改wait_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 |+---------------------+----------------+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 |+---------------------+----------------+rows in set (0.00 sec)

  • 開啟另外一個mysql客戶端,查看會話變數的值

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 |+---------------------+----------------+rows in set (0.03 sec)

wait_timeout的值依舊是28800,沒有繼承剛才設置的WAIT_TIMEOUT值

2. 非互動式連接修改wait_timeout值

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)); } }}

輸出結果

INTERACTIVE_TIMEOUT: 28800WAIT_TIMEOUT: 20

同時,新增了一段程序,等待20s後,再次執行查詢,報如下錯誤:

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

wait_timeout的變為20,繼承剛才設置的WAIT_TIMEOUT值


總結

  1. 控制連接最大空閑時長的wait_timeout參數。
  2. 對於非互動式連接,類似於jdbc連接,wait_timeout的值繼承自伺服器端全局變數wait_timeout。對於互動式連接,類似於mysql客戶單連接,wait_timeout的值繼承自伺服器端全局變數interactive_timeout。
  3. 判斷一個連接的空閑時間,可通過show processlist輸出中Sleep狀態的時間

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 |+----+------+----------------------+------+---------+------+-------+------------------+rows in set (0.03 sec)


參考

  • cnblogs.com/ivictor/p/5
  • cnblogs.com/Alight/p/41
  • cnblogs.com/jiunadiansh

更多內容請關注公眾號

weixin.qq.com/r/MygJEfb (二維碼自動識別)

推薦閱讀:

使用 Python 讀寫 CSV 文件(二)
如何匹配兩段文本的語義?
Z語言實現基本原理
變態黑客系列(8):使用熱感應讀取鍵盤密碼
探秘「棧」之旅

TAG:計算機科學 | MySQL | 科技 |