Quartz Cluster出现异常Couldn‘t rollback jdbc connection. Communications link failure during rollback().

记录使用Quartz Cluster出现异常已经解决方案

问题描述

....省略部分错误
The last packet successfully received from the server was 3,689,831 milliseconds ago. 
The last packet sent successfully to the server was 3,689,833 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.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 3,689,831 milliseconds ago. 

....省略部分错误

问题分析

错误日志告诉了我们，从服务器成功接收到最后一个数据包是3689831毫秒前，最后一个成功发送到服务器的数据包是3689833毫秒前的数据包，超过了mysql配置的wait_timeout配置的时间。告诉我们需要增加客户端超时的服务器配置，或者使用autoReconnect=true配置数据库连接来避免这个问题，

解决方案

1、修改mysql中wait_timeout配置

show global variables like '%time%' ;
SET GLOBAL wait_timeout = 28800;(秒)

2、数据库链接增加autoReconnect=true和autoReconnectForPools=true
autoReconnect：会向客户端抛出一个SQLException异常，但是会尝试重新建立连接
autoReconnectForPools：在每次执行SQL之前都会尝试ping服务器

autoReconnect在官方文档中不推荐使用，如果启用这个属性，MySQL驱动将会对当前事务的已过期或者被废弃的链接发出查询请求抛出异常，并会在发起下一次请求之前尝试重新建立链接，只有在程序无法正确处理死链或者已被回收的链接所导致的SQLException异常时使用，建议修改MySQL的wait_timeout参数，使其大于8个小时

参考资料:MySQL 文档