MySQL之wait_timeout和interactive_timeout引數

沈歐邦發表於2018-08-16
MySqlAI

引言

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

ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No 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

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)


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:  10
WAIT_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`,`wa
it_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`,`wa
it_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值

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

    }
}

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

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

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

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

參考

  • https://www.cnblogs.com/ivictor/p/5979731.html
  • http://www.cnblogs.com/Alight/p/4118515.html
  • http://www.cnblogs.com/jiunadianshi/articles/2475475.html


相關文章