SpringBoot集成JPA出现SQLSTATE(08S01)连接损坏异常求排查复现方案

现有相关问题与遇到的场景不完全匹配，因此发布此问题咨询。间歇性出现SQLSTATE(08S01)、ErrorCode(0)告警，告警触发后，使用SpringBoot JPARepository执行数据库select查询操作时会立即抛出异常。

异常栈信息 #

WARN级别异常栈：

com.zaxxer.hikari.pool.ProxyConnection:::ProxyConnection.java:::checkException:::182:::HikariPool-1 - Connection com.mysql.cj.jdbc.ConnectionImpl@ee48bb3 marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 949,021 milliseconds ago. The last packet sent successfully to the server was 949,022 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.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:2161)
at com.mysql.cj.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:2145)
at com.zaxxer.hikari.pool.ProxyConnection.setReadOnly(ProxyConnection.java:423)
at com.zaxxer.hikari.pool.HikariProxyConnection.setReadOnly(HikariProxyConnection.ja

调用SpringBoot JPARepository逻辑时抛出的异常：

:::D3931305D3A84D82AACF29594A0432C8::::::Monitor:::com.zaxxer.hikari.pool.ProxyLeakTask:::ProxyLeakTask.java:::cancel:::91:::Previously reported leaked connection com.mysql.cj.jdbc.ConnectionImpl@ee48bb3 on thread http-nio-9090-exec-69 was returned to the pool (unleaked)
2021-09-07 **21:39:52,100:::ERROR:::D3931305D3A84D82AACF29594A0432C8:::saveRunConfigurations:::388:::Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed:
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed:**
at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:448)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:572)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:360)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
.
.

现有配置 #

HikariCP配置项：

spring.datasource.hikari.maximumPoolSize=150
spring.datasource.hikari.minimumIdle=20
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.connectionTimeout=900000
spring.datasource.hikari.maxLifetime=1000000
spring.datasource.hikari.validationTimeout=30000
spring.datasource.hikari.connectionTestQuery=SELECT 1
spring.datasource.hikari.leakDetectionThreshold=90000

MySQL timeout相关参数：

MySQL [(none)]> SHOW GLOBAL VARIABLES LIKE "%wait%";
+---------------------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------------------+----------+
| innodb_fatal_semaphore_wait_threshold | 600 |
| innodb_lock_wait_timeout | 50 |
| innodb_spin_wait_delay | 6 |
| lock_wait_timeout | 31536000 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| shutdown_wait_connection_timeout | 500 |
| thread_pool_batch_wait_timeout | 10000 |
| wait_timeout | 180 |


MySQL [(none)]> SHOW VARIABLES LIKE "%timeout%";
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 120 |
| net_write_timeout | 240 |
| rpl_stop_slave_timeout | 31536000 |
| shutdown_wait_connection_timeout | 500 |
| slave_net_timeout | 60 |
| tcp_linger_timeout | 10 |
| thread_pool_batch_wait_timeout | 10000 |
| wait_timeout | 28800 | 

已尝试复现方案（均未成功） #

• 使用与生产完全一致的配置，测试环境未复现问题
• 最初怀疑是wait_timeout取值过低导致，将该参数调高至28800后仍未复现
• 对测试环境MySQL加压提升负载，未复现问题
• 猜测可能是防火墙限制等网络因素导致，但暂不清楚具体需要核查哪些项
• 尝试调低spring.datasource.hikari.maxLifetime参数至100000，仍未复现

排查方向 #

1. 配置冲突校验：首先确认生产环境MySQL全局wait_timeout为180s，JDBC非交互式连接默认继承全局wait_timeout，但当前Hikari的maxLifetime为1000000ms≈16.6分钟，远大于180s，连接池内的空闲连接会被MySQL提前断开，连接池复用失效连接时就会触发报错。另外需确认Hikari是否开启了testWhileIdle，未开启的话即使配置了connectionTestQuery也不会在连接空闲时校验有效性。
2. 网络链路检查：排查生产环境应用与数据库之间的四层负载、防火墙的空闲连接回收时间，多数云厂商负载均衡默认空闲连接回收时间为900s，业务低峰期连接空闲超过该阈值会被中间链路直接断开，且不会发送RST包，应用侧拿到死连接访问就会触发通信异常。
3. 连接泄漏排查：异常栈出现连接泄漏提示，需排查代码中是否存在事务长时间未提交、获取连接后未正常关闭的场景，长事务持有连接的时间超过MySQL或中间链路的超时时间，再执行操作就会触发连接断开。

复现方案 #

1. 配置类场景复现：将测试环境MySQL全局wait_timeout设为60s，Hikari的maxLifetime设为120s，关闭testWhileIdle，启动服务后发起一次数据库请求，等待2分钟以上再发起请求即可复现异常。
2. 网络类场景复现：用iptables配置测试环境应用到MySQL的连接空闲60s后自动丢弃包，模拟防火墙或负载均衡的空闲连接回收逻辑，启动服务后发起一次请求，等待1分钟以上再发起请求即可复现。
3. 长事务场景复现：编写测试接口，开启事务后sleep 200s再执行数据库操作，将MySQLwait_timeout设为180s，调用该接口即可复现异常。

临时修复方案 #

• 将Hikari的maxLifetime调整为比MySQL全局wait_timeout短至少30s，例如全局wait_timeout为180s时，maxLifetime设为120000ms（2分钟）
• 开启Hikari的testWhileIdle=true，保证连接空闲时会用connectionTestQuery校验有效性，失效连接会被提前清理
• JDBC连接串添加autoReconnect=true&failOverReadOnly=false参数，连接断开后自动重试重连

内容的提问来源于stack exchange，提问作者vipul jadhav