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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: MySQL连接超时时间wait_timeout导致间歇性报错:communication link failure


  1. 查看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

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

  1. 只修改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的值
  • 交互式连接修改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的值

  1. 只修改全局变量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值

总结

  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)

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数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
13天前
|
关系型数据库 MySQL 网络安全
DBeaver连接MySQL提示Access denied for user ‘‘@‘ip‘ (using password: YES)
“Access denied for user ''@'ip' (using password: YES)”错误通常与MySQL用户权限配置或网络设置有关。通过检查并正确配置用户名和密码、用户权限、MySQL配置文件及防火墙设置,可以有效解决此问题。希望本文能帮助您成功连接MySQL数据库。
28 4
|
29天前
|
安全 关系型数据库 MySQL
【赵渝强老师】MySQL的连接方式
本文介绍了MySQL数据库服务器启动后的三种连接方式:本地连接、远程连接和安全连接。详细步骤包括使用root用户登录、修改密码、创建新用户、授权及配置SSL等。并附有视频讲解,帮助读者更好地理解和操作。
|
1月前
|
NoSQL 安全 关系型数据库
2024Mysql And Redis基础与进阶操作系列(6)作者——LJS[含MySQL 多表之一对一/多;多对多;多表联合查询等详解步骤及常见报错问题所对应的解决方法]
MySQL 多表之一对一/多;多对多;多表联合之交叉连接;内连接;左、右、外、满、连接;子查询及关键字;自连接查询等详解步骤及常见报错问题所对应的解决方法
|
1月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
1月前
|
SQL NoSQL 关系型数据库
|
1月前
|
存储 SQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(1)作者——LJS[含MySQL的下载、安装、配置详解步骤及报错对应解决方法]
Mysql And Redis基础与进阶操作系列(1)之[MySQL的下载、安装、配置详解步骤及报错对应解决方法]
|
1月前
|
SQL 关系型数据库 MySQL
|
2月前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
101 6
|
1月前
|
存储 SQL NoSQL