登录 注册

MySQL 之 wait_timeout 和 interactive_timeout 参数

本贴最后更新于 2289 天前，其中的信息可能已经时移俗易

引言

在用 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

可以看到，等待 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:  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 值

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)

参考

更多内容请关注公众号

  • MySQL

    MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    690 引用 • 535 回帖
昆明 位置
1 感谢
1 赞同
343 4 54 104 1 21 30 2 13 106

相关帖子

回帖
MySQL 之 wait_timeout 和 interactive_timeout 参数

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
bangbang
折腾不休，奋斗不止 昆明
回帖
4
 帖子
17
 积分
647