database - Hibernate c3p0 连接池不会超时空闲连接

Question

我们有一个连接到 MySQL 5 数据库的 java 服务器，使用 Hibernate 作为我们的持久层，它使用 c3p0 进行数据库连接池。

我已经尝试遵循 c3p0 和 hibernate 文档:

• Hibernate - HowTo Configure c3p0 connection pool
• C3P0 Hibernate properties
  • C3P0.properties configuration

我们在生产服务器上收到一条错误消息:

... Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:

BEGIN NESTED EXCEPTION

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

MESSAGE: The last packet successfully received from the server was45000 seconds ago.The last packet sent successfully to the server was 45000 seconds ago, which 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.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was45000 seconds ago.The last packet sent successfully to the server was 45000 seconds ago, which 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.

我们的 c3p0 连接池属性设置如下:

hibernate.c3p0.max_size=10
hibernate.c3p0.min_size=1
hibernate.c3p0.timeout=5000
hibernate.c3p0.idle_test_period=300
hibernate.c3p0.max_statements=100
hibernate.c3p0.acquire_increment=2

default MySQL wait_timetout设置为 28800 秒(8 小时)，报告的错误是说它已经超过 45000 秒(大约 12.5 小时)。尽管 c3p0 配置声明它将“超时”5000 秒后未使用的空闲连接，并且它将每 300 秒检查一次，因此空闲连接的生命周期永远不应超过 5299 秒，对吗？

我通过设置我的开发人员 MySQL(在 Windows 上为 my.ini，在 Unix 上为 my.cnf)wait_timeout=60 并将 c3p0 空闲超时值降低到 60 秒以下进行了本地测试，它会正确地超时空闲连接并创建新的连接那些。我还检查以确保我们没有泄漏数据库连接并保持连接，但看起来我们没有。

这是我用来在我的开发环境中进行测试以确保 c3p0 正确处理连接的 c3p0.properties 文件。

hibernate.properties(使用 MySQL wait_timeout=60 进行测试)

hibernate.c3p0.max_size=10
hibernate.c3p0.min_size=1
hibernate.c3p0.timeout=20
hibernate.c3p0.max_statements=100
hibernate.c3p0.idle_test_period=5
hibernate.c3p0.acquire_increment=2

c3p0.properties

com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL=ALL
com.mchange.v2.log.MLog=com.mchange.v2.log.FallbackMLog
c3p0.debugUnreturnedConnectionStackTraces=true
c3p0.unreturnedConnectionTimeout=10

Answer 1

通过检查日志确保 c3p0 确实在启动。由于某种原因，我的类路径上有两个版本的 hibernate (hibernate-core3.3.1.jar 和 hibernate-3.2.6GA.jar)。我还使用了与 3.2.x 不兼容的 hibernate 注释版本 3.4.0GA。 (不知道这是否与原始问题有关)。删除其中一个 hibernate jar(不记得我删除了哪个，可能是 hibernate-3.2.6GA.jar)后，c3p0 终于启动了，我摆脱了 8 小时不活动后发生的烦人的 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException .