mysql从5.7.17迁移到5.7.22业务报错

[huangzhimeng]

mysql从5.7.17迁移到5.7.22后，业务半个月报错2次（报错时间毫无规律） 以下是业务报错日志，truncate表后重启执行程序业务恢复正常。 原库和新库的 wait_timeout都是8个小时

2018-05-20 06:30:30.020 [schedulerFactoryBean_Worker-3] INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2018-05-20 06:30:30.127 [schedulerFactoryBean_Worker-3] INFO org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
2018-05-20 06:30:30.178 [schedulerFactoryBean_Worker-3] INFO com.mark.stat.sync.service.impl.ReportTripCoServiceImpl - [LOGGERID: 1526769000010-541] 表重算捕获异常===org.springframework.dao.RecoverableDataAccessException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 624 milliseconds ago. The last packet sent successfully to the server was 624 milliseconds ago.
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: delete from mark_report
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 624 milliseconds ago. The last packet sent successfully to the server was 624 milliseconds ago.
; SQL []; Communications link failure

The last packet successfully received from the server was 624 milliseconds ago. The last packet sent successfully to the server was 624 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 624 milliseconds ago. The last packet sent successfully to the server was 624 milliseconds ago.
2018-05-20 06:30:30.179 [schedulerFactoryBean_Worker-3] INFO com.mark.stat.sync.service.impl.JobExecuteServiceImpl - [LOGGERID: 1526769000010-541] 重算任务释放锁
2018-05-20 06:40:00.009 [schedulerFactoryBean_Worker-4] INFO com.mark.stat.sync.service.impl.JobExecuteServiceImpl - [LOGGERID: 1526769600009-702] 重算任务开始获取锁
2018-05-20 06:40:00.009 [schedulerFactoryBean_Worker-4] INFO com.mark.stat.sync.service.impl.JobExecuteServiceImpl - [LOGGERID: 1526769600009-702] 重算任务获取锁成功
2018-05-20 06:40:00.019 [schedulerFactoryBean_Worker-4] INFO com.mark.stat.sync.service.impl.JobExecuteServiceImpl - [LOGGERID: 1526769600009-702] 表重算任务开始-------- start --------
2018-05-20 06:40:00.145 [schedulerFactoryBean_Worker-4] INFO com.mark.stat.sync.service.impl.ReportTripCoServiceImpl - [LOGGERID: 1526769600009-702] [timecost(ms): 136] 查询需要重算的记录
2018-05-20 06:40:20.682 [schedulerFactoryBean_Worker-4] INFO com.mark.stat.sync.service.impl.ReportTripCoServiceImpl - [LOGGERID: 1526769600009-702] 表重算捕获异常===org.springframework.dao.RecoverableDataAccessException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 136 milliseconds ago. The last packet sent successfully to the server was 136 milliseconds ago.
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: delete from mark_report
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 136 milliseconds ago. The last packet sent successfully to the server was 136 milliseconds ago.
; SQL []; Communications link failure

The last packet successfully received from the server was 136 milliseconds ago. The last packet sent successfully to the server was 136 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

[huangzhimeng]

求大神解答！！

[huangzhimeng]

补充debug日志 2018-05-20 06:30:30.127 [schedulerFactoryBean_Worker-3] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Finished creating instance of bean 'Hana' 2018-05-20 06:30:30.127 [schedulerFactoryBean_Worker-3] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - Looking up default SQLErrorCodes for DataSource [{ CreateTime:"2018-05-18 15:25:52", ActiveCount:0, PoolingCount:1, CreateCount:237, DestroyCount:235, CloseCount:15498, ConnectCount:15499, Connections:[ {ID:470392784, ConnectTime:"2018-05-20 06:30:00", UseCount:3, LastActiveTime:"2018-05-20 06:30:28"", CachedStatementCount:3} ] } [ { ID:470392784, poolStatements:[ {hitCount:1,sql:"SELECT * FROM QRTZ_SCHEDULER_STATE WHERE SCHED_NAME = 'schedulerFactoryBean'" }, {hitCount:1,sql:"UPDATE QRTZ_SCHEDULER_STATE SET LAST_CHECKIN_TIME = ? WHERE SCHED_NAME = 'schedulerFactoryBean' AND INSTANCE_NAME = ?" }, {hitCount:0,sql:"SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'schedulerFactoryBean' AND TRIGGER_STATE = ? AND NEXT_FIRE_TIME <= ? AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= ?)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC" } ] } ]] 2018-05-20 06:30:30.144 [schedulerFactoryBean_Worker-3] DEBUG org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Unable to translate SQLException with Error code '0', will now try the fallback translator 2018-05-20 06:30:30.146 [schedulerFactoryBean_Worker-3] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'redisConnectionFactory' 2018-05-20 06:30:30.165 [schedulerFactoryBean_Worker-3] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Rolling back transaction to savepoint 2018-05-20 06:30:30.165 [schedulerFactoryBean_Worker-3] DEBUG org.springframework.jdbc.datasource.JdbcTransactionObjectSupport - Could not explicitly release JDBC savepoint java.sql.SQLException: connection holder is null at com.alibaba.druid.pool.DruidPooledConnection.checkStateInternal(DruidPooledConnection.java:1138) ~[druid-1.0.29.jar!/:1.0.29] at com.alibaba.druid.pool.DruidPooledConnection.checkState(DruidPooledConnection.java:1131) ~[druid-1.0.29.jar!/:1.0.29]

[执笔记忆的空白]

引用 3 楼 lgd76 的回复:

鉴于是mysql升级引起的问题，估计是兼容性问题。你们mysql升级后，有没重新建库和表？建议建新库和表，并导入旧数据。

认同， 升级数据库后，应该导出建表DDL和DML， 然后进行数据更新

[地球太平洋]

鉴于是mysql升级引起的问题，估计是兼容性问题。你们mysql升级后，有没重新建库和表？建议建新库和表，并导入旧数据。

[huangzhimeng]

直接从mysqldump导出，souce导入的，