database Hibernate c3p0 连接池不超时空闲连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1375074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate c3p0 connection pool not timing out idle connections
提问by Dougnukem
We have a java server connecting to a MySQL 5 database usingHibernate as our persistence layer which is using c3p0 for DB connection pooling.
我们有一个 java 服务器连接到 MySQL 5 数据库，使用 Hibernate 作为我们的持久层，它使用 c3p0 进行数据库连接池。
I've tried following the c3p0 and hibernate documentation:
我试过遵循 c3p0 和 hibernate 文档：
We're getting an error on our production servers stating that:
我们在生产服务器上收到一个错误，指出：
... 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.
...引起：com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException：连接关闭后不允许操作。由于底层异常/错误，连接被隐式关闭：
开始嵌套异常
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: 最后一个从服务器成功接收的数据包是45000 秒前。最后一个成功发送到服务器的数据包是45000 秒前，这比服务器配置的'wait_timeout' 值要长。您应该考虑在应用程序中使用之前使连接有效性过期和/或测试连接有效性，增加客户端超时的服务器配置值，或使用连接器/J 连接属性“autoReconnect=true”来避免此问题。
堆栈跟踪：
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 从服务器成功接收的最后一个数据包是 45000 秒前。最后一个成功发送到服务器的数据包是 45000 秒前，这比服务器配置的 'wait_timeout' 值要长。您应该考虑在应用程序中使用之前使连接有效性过期和/或测试连接有效性，增加客户端超时的服务器配置值，或使用连接器/J 连接属性“autoReconnect=true”来避免此问题。
We have our c3p0 connection pool properties setup as follows:
我们的 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
The default MySQL wait_timetoutis set to 28800 seconds (8 hours), the reported error is saying that it's been over 45000 seconds (about 12.5 hours). Although the c3p0 configuration states that it will "timeout" idle connections that haven't been used after 5000 seconds and it will check every 300 seconds, thus an idle connection should never live longer than 5299 seconds right?
该wait_timetout MySQL默认默认值为28800秒（8小时），报告的错误是说，它已经超过45000秒（约12.5小时）。尽管 c3p0 配置声明它将“超时”在 5000 秒后未使用的空闲连接，并且它会每 300 秒检查一次，因此空闲连接的存活时间不应超过 5299 秒，对吗？
I've tested locally by setting my developer MySQL (my.ini on windows, my.cnf on Unix) wait_timeout=60 and lowering the c3p0 idle timeout values below 60 seconds, and it will properly timeout idle connections and create new ones. I also check to ensure that we're not leaking DB connections and holding onto a connection, and it doesn't appear we are.
我已经通过设置我的开发人员 MySQL（Windows 上的 my.ini，Unix 上的 my.cnf）wait_timeout=60 并将 c3p0 空闲超时值降低到 60 秒以下进行了本地测试，它将正确地超时空闲连接并创建新连接。我还检查以确保我们没有泄漏数据库连接并保持连接，而且看起来我们没有。
Here's the c3p0.properties file I'm using to test in my developer environment to ensure c3p0 is properly handling connections.
这是我用来在我的开发人员环境中测试以确保 c3p0 正确处理连接的 c3p0.properties 文件。
hibernate.properties (testing with MySQL wait_timeout=60)
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
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
回答by Schildmeijer
Make sure that c3p0 really is starting by examine the log. I, for some reason, had two versions of hibernate (hibernate-core3.3.1.jar and hibernate-3.2.6GA.jar) on my classpath. I also used hibernate annotatations version 3.4.0GA which is not compatible with 3.2.x. (dont know if that had something to do with the original problem). After removal of one of the hibernate jar's (cant remember which i deleted, probably hibernate-3.2.6GA.jar) c3p0 finally started and i got rid of the annoying com.mysql.jdbc.exceptions.jdbc4.CommunicationsException that happend efter 8h inactivity.
通过检查日志确保 c3p0 确实正在启动。出于某种原因，我的类路径上有两个版本的 hibernate（hibernate-core3.3.1.jar 和 hibernate-3.2.6GA.jar）。我还使用了与 3.2.x 不兼容的 hibernate 注释版本 3.4.0GA。（不知道这是否与原始问题有关）。删除一个休眠 jar（不记得我删除了哪个，可能是 hibernate-3.2.6GA.jar）后，c3p0 终于启动了，我摆脱了 8 小时不活动后发生的烦人的 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException .
