SQLSTATE(08s01)
[2022-08-24 20:36:19:961] [INFO] - com.****.importData(GlobalCityTemperatureServiceImpl.java:48) - 启动时间2022-08-24 20:36:19
[2022-08-24 20:39:07:976] [WARN] - com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:184)
- HikariPool-6 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@7492c395
(No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[2022-08-24 20:39:42:940] [INFO] - com.****.importData(GlobalCityTemperatureServiceImpl.java:206)
- 完成500/12275 插入数据库记录数164595 目前共插入:164595
[2022-08-24 20:43:13:718] [INFO] - com.****.importData(GlobalCityTemperatureServiceImpl.java:206)
- 完成1000/12275 插入数据库记录数171653 目前共插入:336248
[2022-08-24 20:47:20:317] [INFO] - com.****.importData(GlobalCityTemperatureServiceImpl.java:206)
- 完成1500/12275 插入数据库记录数177305 目前共插入:513553
[2022-08-24 20:50:49:635] [INFO] - com.****.importData(GlobalCityTemperatureServiceImpl.java:206)
- 完成2000/12275 插入数据库记录数173026 目前共插入:686579
[2022-08-24 20:55:02:508] [INFO] - com.****.importData(GlobalCityTemperatureServiceImpl.java:206)
- 完成2500/12275 插入数据库记录数172998 目前共插入:859577
[2022-08-24 20:59:26:739] [WARN] - com.zaxxer.hikari.pool.ProxyConnection.checkException(ProxyConnection.java:182)
- HikariPool-6 - Connection com.mysql.cj.jdbc.ConnectionImpl@5824e49d marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.25.jar:8.0.25]
[2022-08-24 20:59:46:545] [ERROR] - org.springframework.transaction.interceptor.TransactionAspectSupport
.completeTransactionAfterThrowing(TransactionAspectSupport.java:654) -
Application exception overridden by rollback exception
org.springframework.dao.RecoverableDataAccessException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
### The error may exist in file [E:\****************\target\classes\mapper\GlobalCityTemperatureMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into temperature(xx) values (
### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
; Communications link failure; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException:
Communications link failure
##################省略异常详情
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
Caused by: java.io.IOException: Socket is closed
[2022-08-24 20:59:48:121] [ERROR] - com.nebula.base.aspect.WebLogAspect.doRecoveryActions(WebLogAspect.java:45)
- [Result] 异常GlobalCityTemperatureController[importData] 返回 org.springframework.transaction.TransactionSystemException:
Could not roll back JPA transaction; nested exception is org.hibernate.TransactionException:
Unable to rollback against JDBC Connection
场景 大批量插入数据 查看服务器内存记录 和MySQL容器（docker部署的MySQL服务）发现MySQL在报错的时间点重启了一次
附上mysql my.cnf配置
skip-name-resolve max_allowed_packet = 1G max_connections=1000 max_user_connections=500 wait_timeout=12000
根据https://blog.csdn.net/zhoudingding/article/details/114283351的情况 怀疑是服务器内存配额的问题 换到大内存服务器的数据库 并且适当减少块大小（也就是单批次向mysql发送的数据量）然并卵
22:30:31.206 WARN com.zaxxer.hikari.pool.PoolBase.isConnectionAlive() @184 - HikariPool-6 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@296639b9 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value. 22:31:04.678 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成500/12275 插入数据库记录数164595 目前共插入:164595 22:34:27.002 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成1000/12275 插入数据库记录数171653 目前共插入:336248 22:38:03.158 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成1500/12275 插入数据库记录数177305 目前共插入:513553 22:41:18.082 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成2000/12275 插入数据库记录数173026 目前共插入:686579 22:45:12.164 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成2500/12275 插入数据库记录数172998 目前共插入:859577 22:48:51.153 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成3000/12275 插入数据库记录数173838 目前共插入:1033415 22:51:55.536 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成3500/12275 插入数据库记录数153192 目前共插入:1186607 22:55:05.865 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成4000/12275 插入数据库记录数146870 目前共插入:1333477 22:57:44.212 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成4500/12275 插入数据库记录数138543 目前共插入:1472020 23:00:58.811 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成5000/12275 插入数据库记录数168682 目前共插入:1640702 23:04:17.561 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成5500/12275 插入数据库记录数176632 目前共插入:1817334 23:08:09.162 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成6000/12275 插入数据库记录数168757 目前共插入:1986091 23:10:11.599 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成6500/12275 插入数据库记录数118279 目前共插入:2104370 23:13:24.819 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成7000/12275 插入数据库记录数165616 目前共插入:2269986 23:16:59.786 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成7500/12275 插入数据库记录数174177 目前共插入:2444163 23:20:52.085 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成8000/12275 插入数据库记录数170507 目前共插入:2614670 23:24:15.432 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成8500/12275 插入数据库记录数171535 目前共插入:2786205 23:27:49.381 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成9000/12275 插入数据库记录数179322 目前共插入:2965527 23:31:54.999 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成9500/12275 插入数据库记录数180789 目前共插入:3146316 23:35:49.349 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成10000/12275 插入数据库记录数160151 目前共插入:3306467 23:38:52.014 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成10500/12275 插入数据库记录数133072 目前共插入:3439539 23:42:16.280 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成11000/12275 插入数据库记录数147891 目前共插入:3587430 23:46:05.282 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成11500/12275 插入数据库记录数177190 目前共插入:3764620 23:50:13.429 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成12000/12275 插入数据库记录数166278 目前共插入:3930898 23:51:47.913 INFO com.*.impl.GlobalCityTemperatureServiceImpl.importData() @206 - 完成12275/12275 插入数据库记录数83052 目前共插入:4013950 23:52:07.234 WARN com.zaxxer.hikari.pool.ProxyConnection.checkException() @182 - HikariPool-7 - Connection com.mysql.cj.jdbc.ConnectionImpl@3f71f1f1 marked as broken because of SQLSTATE(08S01), ErrorCode(0) com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
附上服务器的监控信息
参考 https://stackoverflow.com/questions/28561649/google-cloud-sql-hikari-cp-communications-link-failure
