MySQL连接超过8小时自动断连报错

一. 问题场景复现(场景复现)

大致情形都是这样，开发测试时程序都是正常的，当放到正式环境上不在做重启操作后一到第二天就出先莫名错误，比如在我的项目中就是定时任务执行，每天凌晨一点执行一次，也就是24小时每隔24小时执行，远远超出了8小时

报错信息

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully to the server was 43200 milliseconds 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 connection property 'autoReconnect=true' to avoid this problem.
 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.send(MysqlIO.java:3270)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1932)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
 at org.hibernate.loader.Loader.doQuery(Loader.java:697)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
 at org.hibernate.loader.Loader.doList(Loader.java:2232)
 at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
 at org.hibernate.loader.Loader.list(Loader.java:2124)
 at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
 at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
 at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
 at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
 at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
 at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:835)
 at com.cn.util.db.TargetRecordDaoImpl.findbyIdAndDate(TargetRecordDaoImpl.java:23)
 at com.cn.util.parser.ExcelOperate.readExcel(ExcelOperate.java:324)
 at com.cn.util.parser.ExcelParser.parser(ExcelParser.java:41)
 at com.cn.util.timer.CRMExcelParserTarger.execute(CRMExcelParserTarger.java:76)
 at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
 at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
Caused by: java.net.SocketException: Software caused connection abort: socket write error
 at java.net.SocketOutputStream.socketWrite0(Native Method)
 at java.net.SocketOutputStream.socketWrite(Unknown Source)
 at java.net.SocketOutputStream.write(Unknown Source)
 at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
 at java.io.BufferedOutputStream.flush(Unknown Source)
 at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3251)
 ... 24 more

二 . 问题原因分析

在网上查阅资料,寻找原因

 Mysql服务器默认的“wait_timeout”是8小时【也就是默认的值默认是28800秒】，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection，通俗的讲就是一个连接在8小时内没有活动，就会自动断开该连接。
wait timeout的值可以设定，但最多只能是2147483秒，不能再大了。也就是约24.85天

三 . 解决方案的寻找

1.(不能解决问题) 修改mysql配置

在MySQL通过my.ini 中更改wait_timeout的时间

# The TCP/IP Port the MySQL Server will listen on
port=3306下面添加
# this is myown dinifition for mysql connection timeout
wait_timeout=31536000
interactive_timeout=31536000

无论超过最大限度多大的数值，只能被MySQL解析为24.85天，24.85天后还是会出错

2.(不能解决问题) 通过修改yml配置文件 &autoReconnect=true&failOverReadOnly=false ,mysql 5之后没有这个属性了

driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/xx?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8&autoReconnect=true&failOverReadOnly=false

但是MySQL 5之后的数据库把这个功能给去了，说是会有副作用

3.(解决)修改yml文件配置: testWhileIdle: true , testOnBorrow: true ,  testOnReturn: true

testOnConnect： 在连接初始化时执行连接可用性检查
testOnBorrow：在借出连接时检查连接可用性
testOnReturn：在客户端归还连接时检查连接可用性
testWhileIdle：在检查闲置连接时同时检查连接可用性

TestOnBorrow=false时，由于不检测池里连接的可用性，于是假如连接池中的连接被数据库关闭了，应用通过连接池getConnection时，都可能获取到这些不可用的连接，且这些连接如果不被其他线程回收的话，它们不会被连接池被废除，也不会重新被创建，占用了连接池的名额,TestOnBorrow具体可参照这篇解释的比较好的博客

三 . 扩展延伸----配置文件连接池属性配置含义

spring:
  datasource:
    dynamic:
      # 是否开启 SQL日志输出，生产环境建议关闭，有性能损耗
      p6spy: false
      hikari:
        connection-timeout: 30000
        max-lifetime: 1800000
        max-pool-size: 15
        min-idle: 5
        connection-test-query: select 1
      # 配置默认数据源
      primary: mysql
      datasource:
        # 数据源-1，名称为 primary
        mysql:
          username: xxx
          password: xxxx
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/xx?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8&autoReconnect=true&failOverReadOnly=false
          initialSize: 5
          minIdle: 5
          maxActive: 20
          maxWait: 60000
          timeBetweenEvictionRunsMillis: 60000
          minEvictableIdleTimeMillis: 30000
          validationQuery: SELECT 1 FROM DUAL
          testWhileIdle: true
          testOnBorrow: true
          testOnReturn: true

boot的yml配置连接池属性详解 

• validationQuery：检查连接可用性的语句，一般来说mysql设置成SELECT 1, oracle设置成select 1 from dual, sql server设置 成SELECT 1 ，在检查连接可用性时，连接池会执行这些语句，如果返回结果不为空，则说明连接可用。
• validationQueryTimeout：执行连接检查语句时的超时时间。

上面两个选项告诉连接池如何检查连接有效性，下面几个选项告诉连接池在何时检查连接有效性

• testOnConnect： 在连接初始化时执行连接可用性检查
• testOnBorrow：在借出连接时检查连接可用性
• testOnReturn：在客户端归还连接时检查连接可用性
• testWhileIdle：在检查闲置连接时同时检查连接可用性