MySQL连接超时时间wait_timeout导致间歇性报错：communication link failure-阿里云开发者社区

查看mysql服务器端的连接超时时间，单位秒，默认时8小时

show global variables like 'wait_timeout'
show global variables like 'interactive_timeout'
show global variables like '%timeout';

show variables like 'wait_timeout'
show variables like 'interactive_timeout'
相当于
show session variables like 'wait_timeout'
show session variables like 'interactive_timeout'

修改数据库超时时间设为8小时(默认)

set global wait_timeout=28800;
set session WAIT_TIMEOUT=28800;

控制连接最大空闲时长的参数及验证

控制连接最大空闲时长的参数是: wait_timeout

只修改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后再执行操作，连接已经断开

只修改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的值。

只修改全局变量interactive_timeout的值

交互式连接修改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的值。

非交互式连接修改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的值

只修改全局变量wait_timeout的值

交互式连接修改INTERACTIVE_TIMEOUT值
打开一个Mysql客户端修改INTERACTIVE_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值

非交互式连接修改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值

总结

控制连接最大空闲时长的wait_timeout参数。
对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout。对于交互式连接，类似于mysql客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout。
判断一个连接的空闲时间，可通过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)

springBoot项目集成druid时

注意两个配置：test-while-idle 和 time-between-eviction-runs-millis

#druid连接池配置
spring:
 datasource:
   db:
     type: com.alibaba.druid.pool.DruidDataSource
     driver-class-name: com.mysql.jdbc.Driver    #高版本使用 com.mysql.cj.jdbc.Driver
     url: jdbc:mysql://localhsot:3306/db?autoReconnect=true&useSSL=false&failOverReadOnly=false&maxReconnects=10
     username: xxxx
     password: xxxx
   druid:
     max-active: 100                #指定连接池中最大的活跃连接数.
     initial-size: 10               #指定连接的初始值
     min-idle: 10                   #指定必须保持连接的最小值
     max-wait: 60000                #指定连接池等待连接返回的最大等待时间，设置1分钟；默认-1不限时间
     test-on-borrow: false          #获取连接时候验证，会影响性能，默认为false
     test-while-idle: true          #验证连接的有效性
     time-between-eviction-runs-millis: 300000     #空闲连接回收的时间间隔，与test-while-idle一起使用，设置5分钟
     min-evictable-idle-time-millis: 1800000       #连接池空闲连接的有效时间 ，设置30分钟
     validation-query: select 1
     remove-abandoned-timeout: 30   #隔30秒回收断开的连接
     remove-abandoned: true         #当连接超过了removeAbandonedTimout时间，删除泄露的连接，默认false
     log-abandoned: true            #当Statement或连接被泄露时打印程序的stack traces日志
     filter:
       slf4j:
         enabled: true                    #开启slf4j debug日志打印
         statement-log-enabled: false     #关闭statement相关debug日志打印
         result-set-log-enabled: false    #关闭result-set相关debug日志打印

如果想在开发环境复现这种问题的话：

首先设置mysql变量

set global interactive_timeout=30
set global wait_timeout=30;　（超时时间由28800改为30秒）

然后再把项目druid的配置文件test-while-idle=false

最后启动项目，第一次请求sql才会初始化连接池，等待30+秒，再次请求sql就会出错误：CommunicationsException

MySQL连接超时时间wait_timeout导致间歇性报错：communication link failure

控制连接最大空闲时长的参数及验证

会话变量wait_timeout的继承问题

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像