HikariCP - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl
环境:
springboot 2.5.6
mybatis-spring-boot-starter 2.1.4
初始配置:
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mn-open-platform?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: 1234567890
hikari:
auto-commit: true
connection-test-query: SELECT 1
connection-timeout: 10000
idle-timeout: 30000
max-lifetime: 950000
maximum-pool-size: 30
minimum-idle: 10
pool-name: HikariCP
validation-timeout: 1000
上述配置应用程序一直报如下错误:
HikariCP - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@103e033e (The last packet successfully received from the server was 393,701 milliseconds ago. The last packet sent successfully to the server was 393,701 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.). Possibly consider using a shorter maxLifetime value
解决方案
max-lifetime
参数的值设置必须要小于min(数据库的wait_timeout,HA代理的超时时间,其余代理的超时时间);也就是说max-lifetime
不只要像HikariCP官方说的那样小于数据库的wait_timeout,还要小于包括HA代理在内的全部介于数据库和业务应用之间其余代理的超时时间。
排查过程
- 数据库的wait_timeout时间,默认500s
按照HikariCP官方和日志中的建议来看,应该是max-lifetime
设置的过长了,数据库的wait_timeout设置的是500秒,因此将max-lifetime
设置小于500000。 - 数据库超时时间,当前设置 600s。
- HA代理超时时间,当前设置 450s
- springboot2的max-lifetime值配置最小值 300s,并且最大值应该为上述三项时间的最小值,因此:max-lifetime应该配置为 300s到 450s之间。
修改后的配置为:
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mn-open-platform?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
hikari:
auto-commit: true
connection-test-query: SELECT 1
connection-timeout: 10000
idle-timeout: 30000
max-lifetime: 400000 # 设置为400s
maximum-pool-size: 30
minimum-idle: 10
pool-name: HikariCP
validation-timeout: 1000