Hibernate C3P0连接池不正时闲置连接[英] Hibernate c3p0 connection pool not timing out idle connections

本文是小编为大家收集整理的关于Hibernate C3P0连接池不正时闲置连接的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我们有一台Java服务器,使用hibernate连接到MySQL 5数据库作为我们的持久性层,该层将C3P0用于DB Connection池.

我已经尝试遵循C3P0和Hibernate文档:

我们在生产服务器上遇到错误,说明:

...由: com.mysql.jdbc.exceptions.jdbc4.mysqlnontransientConnectionException: 连接后不允许操作 关闭.连接是隐式的 由于基础而关闭 异常/错误:

开始嵌套异常

com.mysql.jdbc.exceptions.jdbc4.communicationsexception

消息:最后一个数据包成功 从服务器接收到45000 几秒钟前.最后一个数据包发送 成功进入服务器的是45000 几秒钟前,比 服务器配置的值 'Wait_timeout'.您应该考虑 到期和/或测试 使用之前的连接有效性 应用程序,增加服务器 为客户端配置值, 或使用连接器/J连接 属性'autoreconnect = true'避免 这个问题.

stacktrace:

com.mysql.jdbc.exceptions.jdbc4.communicationsexception: 最后一个数据包成功收到 从服务器WAS 45000秒 ago.最后一个数据包成功发送 到达服务器是45000秒前, 比服务器更长 配置的" wait_timeout"的值. 您应该考虑到期 和/或测试连接有效性 在您的应用程序中使用之前, 增加配置的服务器 客户超时或使用的值 连接器/J连接属性 'autoreconnect = true'避免这种情况 问题.

我们的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(在窗口上的my.ini,my.cnf上的my.cnf)进行了本地测试那些.我还检查以确保我们不会泄漏DB连接并保持连接,并且似乎并不是我们.

这是我用来在开发人员环境中测试的c3p0.properties文件,以确保C3P0正确处理连接.

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

推荐答案

确保C3P0真正从检查日志开始.由于某种原因,我在我的classPath上有两个版本的Hibernate(Hibernate-Core3.3.1.Jar和Hibernate-3.2.6Ga.jar).我还使用了与3.2.x不兼容的3.4.0GA版本3.4.0GA. (不知道这是否与原始问题有关). 在删除了一个冬眠罐子(不记得我删除的内容,可能是Hibernate-3.2.6Ga.jar)C3P0终于开始了,我摆脱了烦人的com.mysql.jdbc.exceptions.jdbc4.jdbc4.c4.commumnication seexception,使8h无活动的活性变得不活跃.

本文地址:https://www.itbaoku.cn/post/597759.html

问题描述

We have a java server connecting to a MySQL 5 database usingHibernate as our persistence layer which is using c3p0 for DB connection pooling.

I've tried following the c3p0 and hibernate documentation:

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.

We have our c3p0 connection pool properties setup as follows:

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_timetout is 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?

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.

Here's the c3p0.properties file I'm using to test in my developer environment to ensure c3p0 is properly handling connections.

hibernate.properties (testing with 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

推荐答案

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.