解决JDBC连接Mysql长时间无动作连接失效的问题

错误场景介绍

做的有一个项目使用JDBC手动创建Connection实现了一个简单的自定义数据库连接池,用来支持Canal解析数据库Binlog指定业务库的插入修改sql来进行数据库分表备份(按照月份)操作.

但是发现当一个一段时间(较长)没有进行数据库操作时,连接都失效了,导致SQL执行失败失效提示为No operations allowed after connection closed

查明原因

经过搜索发现这个问题是由于Mysql默认一个已创建的长连接28800秒(八小时)内没有任何动作则会断开连接,该值对应参数为wait_timeout.当超时时间内有执行动作则会重新计时

查验

查询mysql超时连接时长命令show global variables like'wait_timeout'查看当前设置的超时断开连接时长.

将其改为10,本地服务运行功能发现重现了No operations allowed after connection closed错误,即确实是连接超时失效

解决方法

1. 修改Mysql配置

该方法不能根治这个问题,因为不能确认服务空闲时长而精确设置timeout并且还会造成多余连接长时间未断开而影响性能,所以不建议使用.建议在代码层面进行解决

通过set global wait_timeout=time(秒)来修改最长连接等待超时时间,但是这样设置当Mysql重启失效

可以通过修改my.ini文件永久改动超时时间,如下配置

interactive_timeout=28800000
wait_timeout=28800000

2. 连接丢弃重新创建连接

使用conn.isValid(int timeout)(秒)判断是否失效返回true表示连接有效,返回false表示连接失效.当失效时则重新获取一个数据库连接即可,之前的对象由于引用丢失会被回收掉.

3. 增加自动重连选项

在URL最后添加autoReconnect=true参数,jdbc:mysql://hostaddress:3306/xhb?autoReconnect=true.我这里对这个没有效果,可能是对框架连接池有用.

4. 定时执行一个动作进行超时时间刷新

比如默认时间是八小时,则每七小时对连接执行一次select 1语句来刷新该连接在数据库的超时等待时长

也可以1 2 4一起使用,来防止突然一个流量静默期间后突发流量高峰而导致获取连接不及时

补充：连接总是被mysql回收_一般连接池是怎么处理mysql自动回收长时间

若干套 MySQL 环境，只有一套：

行为异常，e5a48de588b63231313335323631343130323136353331333436316239怀疑触发 bug

性能异常，比其他环境都要低

在这种场景下，我们一般的做法是首先控制变量，查看软硬件配置，以及 MySQL 的参数配置。关于 MySQL 的参数配置对比，如果我们人工对比的话只会关注某些重点参数，而缺少了整体细节上的的对比。在这里我们推荐给大家 Percona Toolkit 中的一个工具 pt-config-diff

更准确的复制延时 pt-heartbeat在 MySQL 中，复制延迟可以理解为由两部分组成：1. 主库已经生成了 BINLOG，但是还没有发送给从库 -- 我们在这里称之为：日志延迟2. 从库已经接收到了 BINLOG，但是还没有应用完成 -- 我们在这里称之为：应用延迟MySQL 原生的查看复制延迟的手段为：show slave status\G中的Seconds_Behind_Master。这种观测手法只能观测出应用延迟。在异步复制或降级的半同步复制下，误差较大，无法准确的反映出整体复制延时。

1. 在 Master 上循环插入：insert into database.heartbeat (master_now) values(NOW())

2. database.heartbeat 的变更会跟随主从复制流向从库

3. 系统当前时间 - 从库表中的时间 = 从库实际的复制延时

更简单的参数配置建议 pt-variable-advisortoolkit 中包含了一个简单的 MySQL 参数优化器，可以对参数配置做简单的优化建议。

更准确的复制延时 pt-heartbeat在 MySQL 中，复制延迟可以理解为由两部分组成：1. 主库已经生成了 BINLOG，但是还没有发送给从库 -- 我们在这里称之为：日志延迟2. 从库已经接收到了 BINLOG，但是还没有应用完成 -- 我们在这里称之为：应用延迟MySQL 原生的查看复制延迟的手段为：show slave status\G中的Seconds_Behind_Master。这种观测手法只能观测出应用延迟。在异步复制或降级的半同步复制下，误差较大，无法准确的反映出整体复制延时。

更易用的调试工具 pt-pmp在某些情况下，我们肯定会遇到某些故障无法从日志，以及状态命令中找到原因，需要深入到程序逻辑级别。又或者我们需要立即通过非常规手段恢复故障数据库，但是又想保留足够多的故障信息。来避免我们事后复现问题的头疼。pt-pmp 便是在这种场景下帮助我们的工具。它会使用 gdb 来打印 mysqld 的堆栈信息，并把调用链相同的线程堆栈合并。堆栈合并的功能对于 MySQL 这种多线程的应用非常有帮助，会节省我们大量的时间。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。如有错误或未考虑完全的地方，望不吝赐教。