关于MySQL的wait_timeout连接超时问题报错解决方案

一时失言乱红尘 2023-01-19 09:41 144阅读 0赞

### 1. 问题描述 ###

想必大家在用`MySQL`时都会遇到连接超时的问题，如下图所示：

### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    ; SQL []; The last packet successfully received from the server was 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

大概意思是当前的`connection`所进行过的最新请求是在`52,587`秒之前，这个时间是大于服务所配置的`wait_timeout`时间的。

### 2. 原因分析 ###

`MySQL`连接时，服务器默认的`“wait_timeout”`是`8小时`，也就是说一个`connection`空闲超过`8小时`，`Mysql`将自动断开该`connection`。`connections`如果空闲超过8小时，`Mysql`将其断开，而`DBCP`连接池并不知道该`connection`已经失效，如果这时有`Client`请求`connection`，`DBCP`将该失效的`Connection`提供给`Client`，将会造成异常。

**`mysql`分析：**

打开`MySQL`的控制台，运行:`show variables like ‘%timeout%’`，查看和连接时间有关的`MySQL`系统变量，得到如下结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpamluZ2ppbmdjaG4_size_16_color_FFFFFF_t_70_pic]  
其中`wait_timeout`就是负责超时控制的变量，其时间为长度为`28800s`，就是`8个小时`，那么就是说`MySQL`的服务会在操作间隔8小时后断开，需要再次重连。也有用户在`URL`中使用`jdbc.url=jdbc:mysql://localhost:3306/nd?autoReconnect=true`来使得连接自动恢复，当然了，这是可以的，不过是`MySQL4`及其以下版本适用。`MySQL5中已经无效了，必须调整系统变量来控制`。

### 3. 解决方式 ###

`MySQL5`手册中对两个变量有如下的说明：

*  `interactive_timeout`：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在`mysql_real_connect()`中使用`CLIENT_INTERACTIVE`选项的客户端。又见`wait_timeout`
 *  `wait_timeout`:服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局`wait_timeout`值或全局`interactive_timeout`值初始化会话`wait_timeout`值，取决于客户端类型(由`mysql_real_connect()`的连接选项`CLIENT_INTERACTIVE`定义)，又见`interactive_timeout`

如此看来，两个变量是共同控制的，那么都必须对他们进行修改了。继续深入这两个变量`wait_timeout`的取值范围是`1-2147483(Windows)，1-31536000(linux)`，`interactive_time`取值随`wait_timeout`变动，它们的默认值都是`28800s`。

`MySQL`的系统变量由配置文件控制，当配置文件中不配置时，系统使用默认值，这个`28800`就是默认值。要修改就只能在配置文件里修改。`Windows`下在`%MySQL HOME%/bin`下有`mysql.ini`配置文件，打开后在如下位置添加两个变量，赋值。（这里修改为`388000`）

#### 3.1 方法一：增加 `MySQL` 的 `wait_timeout` 属性的值 （`不推荐`） ####

修改mysql安装目录下的配置文件 `my.ini`文件（如果没有此文件，复制`“my-default.ini”`文件，生成`“复件 my-default.ini”`文件。将`“复件 my-default.ini”`文件重命名成`“my.ini”` ），在文件中设置：

wait_timeout=31536000 
    interactive_timeout=31536000

这两个参数的默认值是`8小时(60*60*8=28800)`, 也可以使用mysql命令对这两个属性进行修改.

> 注意：  
> 1.wait\_timeout的最大值只允许2147483 （24天左右）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpamluZ2ppbmdjaG4_size_16_color_FFFFFF_t_70_pic 1]

#### 3.2 方法二：减少连接池内连接的生存周期 ####

减少连接池内连接的生存周期，使之小于上一项中所设置的`wait_timeout` 的值。 修改 `c3p0` 的配置文件，在 Spring 的配置文件中设置：

#### 3.3 方法三：定期使用连接池内的连接 ####

定期使用连接池内的连接，使得它们不会因为闲置超时而被 `MySQL` 断开。  
修改 `c3p0` 的配置文件,在 `Spring` 的配置文件中设置：

附上`dbcp`和`c3p0`的标准配置

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">  
    　　<property name="driverClass"><value>oracle.jdbc.driver.OracleDriver</value></property>  
    　　<property name="jdbcUrl"><value>jdbc:oracle:thin:@localhost:1521:Test</value></property>  
    　　<property name="user"><value>Kay</value></property>  
    　　<property name="password"><value>root</value></property>  
    　　  
    　　<property name="minPoolSize" value="10" />  
    　　  
    　　<property name="maxPoolSize" value="100" />  
    　　  
    　　<property name="maxIdleTime" value="1800" />  
    　　  
    　　<property name="acquireIncrement" value="3" />  
    　　<property name="maxStatements" value="1000" />  
    　　<property name="initialPoolSize" value="10" />  
    　　  
    　　<property name="idleConnectionTestPeriod" value="60" />  
    　　  
    　　<property name="acquireRetryAttempts" value="30" />  
    　　<property name="breakAfterAcquireFailure" value="true" />  
    　　<property name="testConnectionOnCheckout" value="false" />  
    </bean>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpamluZ2ppbmdjaG4_size_16_color_FFFFFF_t_70_pic]: /images/20221021/065b8e045c484e43bfb56356900e26b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpamluZ2ppbmdjaG4_size_16_color_FFFFFF_t_70_pic 1]: /images/20221021/e6d461d7274e4140ae5c87f88ace1e19.png