mysql之连接超时wait_timeout问题及解决方案

目录

• 问题现象
• 解决方案
• 现只列举几个重要的参数
• springboot 配置
• 总结

问题现象

com.mysql.jdbc.CommunicationsException: The last packet successfully received from the server was58129 seconds ago.The last packet sent successfully to the server was 58129 seconds ago, which 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 connectio编程客栈n property 'autoReconnect=true' to avoid this problem. 

解决办法： 

(备注：mysql5以上的，设置autoReconnect=true 是无效的 只有4.x版本，起作用)

1. 即使在创建Mysql时url中加入了autoReconnect=true参数,一但这个连接两次访问数据库的时间超出了服务器端wait_timeout的时间限制,还是会

CommunicationsException: The last packet successfully received from the server was xxx milliseconds ago.

2. 服务器端的参数可以用

  show global variables like 'wait_timeout';
  set global wait_timeout=10;

来进行设置,但是wait_timeout值不应该设的太高.

3. 较好的策略是对处于idle状态的connection定时发送一个sql,来刷新服务器上的时间戳.这可以使用c3p0r的连接

如果连接闲置8小时 (8小时内没有进行数据库操作), mysql就会自动断开连接, 要重启tomcat.

mysql数据库有一个wait_timeout的配置，默认值为28800(即8小时).

在默认配置不改变的情况下，如果连续8小时内都没有访问数据库的操作，再次访问mysql数据库的时候，mysql数据库会拒绝访问。

解决方案

第一种途径使用命令行在mysql提示符下>set  global wait_timeout=1814400

这种方式是一种临时方法，重启服务就会返回默认值了。

第二种途径修改my.ini配置文件

[mysqld]
wait_timeout=31536000 
interactive_timeout=31536000 

在mysqld下面添加以上两行，后面的数字是时间

首先服务中找到mysql，然后右键属性，在可执行文件的路径中，使劲向后拖动鼠标就可以看到my.ini的文件了

另外：关于Mysql链接参数的说明如下：

mysql JDBC Driver

常用的有两个，一个是gjt（Giant Java Tree）组织提供的mysql驱动，其JDBC Driver名称（JAVA类名）为：org.gjt.mm.mysql.Driver

详情请参见网站：http://www.gjt.org/

或在本网站下载mysql JDBC Driver(mm.jar)

另一个是mysql官方提供的JDBC Driver，其JAVA类名为：com.mysql.jdbc.Driver

驱动下载网址：http://dev.mysql.com/downloads/，进入其中的MySQL Connector/J区域下载。

mysql JDBC URL格式如下：

jdbc:mysql://[host:port],[host:port].../[database][?参数名1][=参数值1][&参数名2][=参数值2]...

现只列举几个重要的参数

如javascript下表所示：

对应中文环境，通常mysql连接URL可以设置为：

jdbc:mysql://localhost:3306/test?user=编程root&password=&useUnicode=true&characterEncoding=gbk&autoReconnect=true&failOverReadOnly=false

在使用数据库连接池的情况下，最好设置如下两个参数：

autoReconnect=true&failOverReadOnly=false

需要注意的是，在XML配置文件中，url中的&符号需要转义成&。比如在tomcat的server.xml中配置数据库连接池时

mysql jdbc url样例如下：

jdbc:mysql://localhost:3306/test?user=root&password=&useUnicode=true&characterEncoding=gbk

&autoReconnect=true&arJdSkJtYNmp;amp;failOverReadOnly=false

场景出现的理论依据

MySQL 的默认设置下，当一个连接的空闲时间超过8小时后，MySQL 就会断开该连接，而 c3p0/dbcp 连接池则以为该被断开的连接依然有效。在这种情况下，如果客户端代码向c3p0/dbcp 连接池请求连接的话，连接池就会把已经失效的连接返回给客户端，客户端在使用该失效连接的时候即抛出异常。

如果你只是个DBA，你会想着，为什么数据库连接自己断了，是不是哪里有配置，我得去看看，那么你得到的解决方案-可能就是这样的

#my.cnf
wait_timeout=31536000
interactive_timeout=31536000

加大wait_timeout的时间。

But 现实环境中需要你考虑的是：

• 你设置多久检查一次连接有效的时间 依据是什么？
• 默认加大/减小wait_timeout除了解决当前问题，会不会带来其他影响？

个人当前觉得此题 第一需考虑的是： 

你业务当前高峰期mysql_connection是多少？保留多久connection在高峰期都不会撑爆你数据库连接池？ 

如果你知道这个池-那么是改mysql ？还是改c3p0？还是双管齐下都是有据可循且不会带来后遗症的-最佳解决方案

如我当前有环境，一个现网的后台管理系统，使用人数在50以内，那么我wait_timeout 就是默认8小时，c3p0不用做连接有效性检查等，都是万事ok的。

而我还有一个EPG前台管理系统，用户量在300万以内，如果我wait_timjseout为8小时，那我一到高峰期肯定就是死翘翘的，会有太多的TCP连接没关闭，数据库连接数肯定是不够的。

因EPG的一个访问-一次对数据库操作量不大，查询完数据就完成ok啦，wait_timeout 设置在120s内应该是够用啦，那么相对应的c3p0中 设置小于wait_timeout 的时间有效性检查 -就能确保获取到连接是有效的。

请根据业务场景，来配置参数，不要解决了A问题，带来了B问题。

springboot 配置

#下面为连接池的补充设置，应用到上面所有数据源中
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒 
spring.datasource.minEvictableIdleTimeMillis=300000

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。