hikari连接池mysql连接空闲超8小时后自动断开问题

MySQL 默认的 wait_timeout 和 interactive_timeout 是 8 小时（28800 秒），当连接空闲超过这个时间，MySQL 会自动断开连接，而 HikariCP 可能仍认为这些连接有效，导致获取到无效连接时抛出异常。你的问题是即使配置了 connectionTestQuery，连接仍然超时，说明配置可能未生效或未正确解决 MySQL 空闲超时问题。以下是分析和解决方案：

问题原因

MySQL 空闲超时：MySQL 的 wait_timeout（非交互式连接）或 interactive_timeout（交互式连接）默认为 28800 秒（8 小时）。超过此时间，MySQL 会关闭空闲连接。
HikariCP 未检测到断开：HikariCP 默认通过 connectionTestQuery 或 JDBC4 的 Connection.isValid() 方法验证连接有效性，但如果配置不当或验证频率不足，可能无法及时检测到 MySQL 已关闭的连接。
配置问题：即使配置了 connectionTestQuery，可能存在以下问题：
- connectionTestQuery 未正确设置或未被驱动支持。
- idleTimeout 或 keepaliveTime 配置不当，导致连接未被及时回收或验证。
- MySQL 驱动版本或 HikariCP 配置与数据库设置不匹配。
常见异常：如 The last packet successfully received from the server was X milliseconds ago 或 No operations allowed after connection closed，表明 HikariCP 尝试使用已断开的连接。

解决方案

以下是调整 HikariCP 配置和 MySQL 设置的建议，逐步解决问题：

1. 优化 HikariCP 配置

HikariCP 提供了几个关键参数来管理连接的生命周期和有效性验证。以下是需要调整的参数：

idleTimeout：
- 控制连接在池中空闲的最大时间（单位：毫秒）。建议设置为比 MySQL 的 wait_timeout 略短，例如 7 小时（25200000 毫秒），以确保 HikariCP 在 MySQL 关闭连接前主动回收空闲连接。
- 默认值：600000 毫秒（10 分钟）。
- 示例：spring.datasource.hikari.idleTimeout=25200000
- 注意：仅当 minimumIdle < maximumPoolSize 时生效。如果 minimumIdle 等于 maximumPoolSize，空闲连接不会被回收。
maxLifetime：
- 控制连接在池中的最大存活时间（单位：毫秒）。建议设置为比 MySQL 的 wait_timeout 短，例如 7 小时（25200000 毫秒），以避免使用过老的连接。
- 默认值：1800000 毫秒（30 分钟）。
- 示例：spring.datasource.hikari.maxLifetime=25200000
- 确保 maxLifetime 小于 MySQL 的 wait_timeout（28800 秒）。
connectionTestQuery：
- 用于验证连接是否有效的 SQL 查询（如 SELECT 1）。但 HikariCP 官方不推荐使用 connectionTestQuery，因为现代 JDBC 驱动（JDBC4 标准）支持 Connection.isValid()，效率更高。
- 如果你的 MySQL JDBC 驱动版本较老（如早于 5.1.23），需要设置 connectionTestQuery。否则，建议移除此配置，让 HikariCP 使用 Connection.isValid()。
- 示例：spring.datasource.hikari.connectionTestQuery=SELECT 1
- 如果已设置但仍超时，检查：
  - 是否正确配置（如大小写错误或 SQL 不合法）。
  - MySQL 驱动版本是否支持（推荐使用最新 MySQL Connector/J，如 8.0.x）。
keepaliveTime：
- 控制 HikariCP 定期检查空闲连接的频率（单位：毫秒）。建议设置为 5-10 分钟（300000-600000 毫秒），以定期验证连接是否有效，防止 MySQL 超时断开。
- 默认值：0（禁用）。
- 示例：spring.datasource.hikari.keepaliveTime=300000
- 注意：keepaliveTime 必须小于 maxLifetime 且大于 30000 毫秒。
connectionTimeout：
- 控制从池中获取连接的最大等待时间（单位：毫秒）。默认 30000 毫秒（30 秒）。如果池中连接耗尽，可能导致超时异常，建议根据应用负载调整。
- 示例：spring.datasource.hikari.connectionTimeout=30000
minimumIdle：
- 控制池中最小空闲连接数。建议设置为小于 maximumPoolSize，以允许 idleTimeout 生效。如果设置为与 maximumPoolSize 相同，空闲连接不会被回收，可能导致超时问题。
- 示例：spring.datasource.hikari.minimumIdle=5
maximumPoolSize：
- 控制池中最大连接数（包括空闲和使用中的连接）。根据应用负载和数据库的 max_connections 设置合理值。
- 示例：spring.datasource.hikari.maximumPoolSize=10
leakDetectionThreshold：
- 用于检测连接泄漏，设置连接借用后未归还的超时时间（单位：毫秒）。建议启用以排查连接泄漏问题。
- 示例：spring.datasource.hikari.leakDetectionThreshold=60000（60 秒）

示例 Spring Boot 配置（application.properties）：

spring.datasource.hikari.jdbcUrl=jdbc:mysql://localhost:3306/mydb?autoReconnect=true&useSSL=false
spring.datasource.hikari.username=yourusername
spring.datasource.hikari.password=yourpassword
spring.datasource.hikari.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.maximumPoolSize=10
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.idleTimeout=25200000
spring.datasource.hikari.maxLifetime=25200000
spring.datasource.hikari.keepaliveTime=300000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.leakDetectionThreshold=60000
# 如果驱动版本较老，启用以下配置
spring.datasource.hikari.connectionTestQuery=SELECT 1

2. 调整 MySQL 配置

如果不希望频繁回收连接，可以增加 MySQL 的 wait_timeout 和 interactive_timeout：

检查当前值：
```
SHOW VARIABLES LIKE '%timeout%';
```
关注 wait_timeout 和 interactive_timeout。
修改 MySQL 配置：
编辑 MySQL 配置文件（如 my.cnf 或 my.ini），增加超时时间：
```
[mysqld]
wait_timeout=86400
interactive_timeout=86400
```
设置为 24 小时（86400 秒），然后重启 MySQL：
```
sudo systemctl restart mysql
```

临时修改（重启后失效）：

SET GLOBAL wait_timeout=86400;
SET GLOBAL interactive_timeout=86400;

注意：增加超时时间可能增加 MySQL 资源占用，需根据服务器资源权衡。

3. 使用 MySQL 驱动的 `autoReconnect`（不推荐）

MySQL Connector/J 支持 autoReconnect=true 参数，尝试自动重连断开的连接：

spring.datasource.hikari.jdbcUrl=jdbc:mysql://localhost:3306/mydb?autoReconnect=true&useSSL=false

缺点：
- autoReconnect 可能导致不可预期的行为（如事务状态丢失）。
- 官方不推荐在生产环境中使用，优先通过 HikariCP 参数解决。

4. 验证连接有效性

确保 JDBC 驱动版本：使用最新 MySQL Connector/J（如 8.0.x），支持 JDBC4 的 Connection.isValid()，避免依赖 connectionTestQuery。
移除 connectionTestQuery：如果驱动支持 JDBC4，HikariCP 默认使用 Connection.isValid()，无需手动设置 connectionTestQuery。若仍需使用，确保查询轻量（如 SELECT 1）。
启用 keepaliveTime：通过定期验证空闲连接，防止 MySQL 断开。

5. 排查连接泄漏

连接泄漏可能导致池中有效连接耗尽，间接导致超时问题：

启用 leakDetectionThreshold：如上所述，设置 60 秒，检查日志是否有泄漏警告。
检查代码：确保每次使用连接后正确关闭（connection.close()），尤其在使用 Spring JDBC 或 JPA 时，事务管理要正确。
监控池状态：通过 JMX 或 Spring Actuator 监控 HikariCP 指标（如 hikaricp.connections.active、hikaricp.connections.idle）：
```
management.endpoint.metrics.enabled=true
management.endpoints.web.exposure.include=metrics
```
访问：http://localhost:8080/actuator/metrics/hikaricp.connections.active

6. 测试配置

模拟空闲超时：
- 设置 MySQL 的 wait_timeout 为较短时间（如 60 秒）：
```
SET GLOBAL wait_timeout=60;
```
- 观察应用是否仍抛出超时异常。
负载测试：使用工具（如 JMeter）模拟高并发请求，验证连接池是否稳定。
日志分析：
- 启用 HikariCP 日志（com.zaxxer.hikari 设置为 DEBUG）：
```
<logger name="com.zaxxer.hikari" level="debug" additivity="false">
    <appender-ref ref="STDOUT"/>
</logger>
```
- 检查是否出现 Failed to validate connection 或 Connection is not available 错误。

为什么 `connectionTestQuery` 未生效？

驱动支持问题：
- 如果 MySQL 驱动版本较老（如早于 5.1.23），可能不支持 Connection.isValid()，但 connectionTestQuery 配置错误（如 SQL 语法错误或查询过重）。
- 解决：升级到最新 MySQL Connector/J（如 8.0.x），移除 connectionTestQuery。
验证频率不足：
- connectionTestQuery 仅在连接从池中借出时执行。如果连接长时间空闲（超过 8 小时），未被借出，MySQL 已关闭连接但 HikariCP 未检测。
- 解决：启用 keepaliveTime 定期验证空闲连接。
配置未加载：
- 检查 application.properties 或 HikariConfig 是否正确加载 connectionTestQuery。日志中应看到：
```
DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery............."SELECT 1"
```
- 如果未出现，检查配置路径或语法。

其他建议

升级依赖：

确保 HikariCP 版本为最新（如 5.0.1 或更高）。
确保 MySQL Connector/J 版本为最新（如 8.0.x）。

Maven 示例：

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

避免 connectionTestQuery：
- 现代驱动支持 Connection.isValid()，效率更高。仅在老驱动下使用 connectionTestQuery。
定期维护：
- 定期检查 MySQL 的 max_connections 和 HikariCP 的 maximumPoolSize 是否匹配。
- 使用监控工具（如 Spring Actuator 或 JMX）观察连接池状态。
参考资料：
- HikariCP 官方文档：https://github.com/brettwooldridge/HikariCP
- MySQL 官方文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

总结

通过设置 idleTimeout 和 maxLifetime 小于 MySQL 的 wait_timeout（8 小时），启用 keepaliveTime 定期验证连接，并确保使用最新 MySQL 驱动，可以有效避免空闲超 8 小时的断开问题。如果仍使用 connectionTestQuery，确认其正确性并考虑移除以使用 Connection.isValid()。若问题持续，启用 HikariCP 日志并检查是否有连接泄漏或配置错误。

目录CONTENT

hikari连接池mysql连接空闲超8小时后自动断开问题

问题原因

解决方案

1. 优化 HikariCP 配置

2. 调整 MySQL 配置

3. 使用 MySQL 驱动的 `autoReconnect`（不推荐）

4. 验证连接有效性

5. 排查连接泄漏

6. 测试配置

为什么 `connectionTestQuery` 未生效？

推荐配置总结

其他建议

总结

评论区

hikari连接池mysql连接空闲超8小时后自动断开问题

问题原因

解决方案

1. 优化 HikariCP 配置

2. 调整 MySQL 配置

3. 使用 MySQL 驱动的 autoReconnect（不推荐）

4. 验证连接有效性

5. 排查连接泄漏

6. 测试配置

为什么 connectionTestQuery 未生效？

推荐配置总结

其他建议

总结

评论区

3. 使用 MySQL 驱动的 `autoReconnect`（不推荐）

为什么 `connectionTestQuery` 未生效？