java 帮助我避免使用 JPA、Hibernate 和 MySQL 的连接超时

help me avoid connection timeout with JPA, Hibernate & MySQL

提问by George Armhold

I'm using JPA (Hibernate as provider), Glassfish and MySQL. Everything works great in development, but when I deploy the app to a test server and let it run (largely idle) overnight, I'm usually greeted with this in the morning:

我正在使用 JPA（Hibernate 作为提供者）、Glassfish 和 MySQL。在开发中一切都很好，但是当我将应用程序部署到测试服务器并让它在一夜之间运行（主要是空闲的）时，我通常会在早上看到这个：

[#|2011-03-09T15:06:00.229+0000|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=23;_ThreadName=Thread-1;|ERROR [htt\
p-thread-pool-8080-(1)] (JDBCTransaction.java:91) - JDBC begin failed
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 41,936,868 milliseconds ago.  The last packet \
sent successfully to the server was 41,936,868 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expirin\
g and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connec\
tion property 'autoReconnect=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3321)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4956)
        at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:87)
        at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
        at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:60)

I tried using the following in my persistence.xml, but it didn't help:

我尝试在我的 中使用以下内容persistence.xml，但没有帮助：

        <property name="hibernate.c3p0.min_size" value="5"/>
        <property name="hibernate.c3p0.max_size" value="20"/>
        <property name="hibernate.c3p0.idleTestPeriod" value="30"/>
        <property name="hibernate.c3p0.timeout" value="0"/>
        <property name="hibernate.c3p0.max_statements" value="0"/>

So that's the C3p0 configuration; it's entirely possible I'm missing the part that actually tells hibernate "hey, use c3p0".

这就是 C3p0 配置；我完全有可能错过了实际告诉 hibernate“嘿，使用 c3p0”的部分。

I'm about to try the suggestion that's right there in the error message: add autoReconnect=trueto my JDBC URL, but this is really starting to feel like cargo-cult development at this point. I would appreciate some guidance on the proper way to address this issue. It's hard to debug, because the test cycle is effectively "run it overnight, see what happens in the morning".

我正要尝试错误消息中的建议：添加autoReconnect=true到我的 JDBC URL，但这真的开始感觉像货物崇拜开发在这一点上。我将不胜感激有关解决此问题的正确方法的一些指导。很难调试，因为测试周期实际上是“通宵运行，看看早上会发生什么”。

I should probably mention how I'm actually using connections in my app. I have a custom Servlet Filterthat intercepts all requests. It creates an EntityManager, stores it in a ThreadLocal, and is closed by the filter in a catch/finally block. All my entities obtain a reference to the EntityManagerfrom the ThreadLocal.

我可能应该提到我如何在我的应用程序中实际使用连接。我有一个拦截所有请求的自定义Servlet 过滤器。它创建一个 EntityManager，将其存储在 ThreadLocal 中，并由 catch/finally 块中的过滤器关闭。我的所有实体都EntityManager从ThreadLocal.

It's entirely possible that my filter is at fault, but as it only seems to happen after idle periods, I suspect something else is wrong. I do intend to move to Seam/Weld when I have a chance to catch my breath, but for now I'm relying on this filter.

完全有可能是我的过滤器有问题，但由于它似乎只发生在空闲期后，我怀疑还有其他问题。当我有机会喘口气时，我确实打算转向接缝/焊接，但现在我依赖于这个过滤器。

Edit: here's the TL;DR solution:

编辑：这是 TL;DR 解决方案：

• use your container's connection pool, if you can (thanks, @partenon)
• make sure your connection pool uses connection validation(thanks, @matt b)

• 如果可以，请使用容器的连接池（谢谢，@partenon）
• 确保您的连接池使用连接验证（谢谢，@matt b）

In my case, I had to go into the Glassfish console under Resources/JDBC/Connection Pools, Advanced Tab, and then enable Connection Validation:

就我而言，我必须进入资源/JDBC/连接池下的 Glassfish 控制台，高级选项卡，然后启用连接验证：

This was really the crucial step. You also probably want to set Validate At Most Onceto something reasonable, say 100 seconds. If you're using C3P0 or similar, make sure you configure idle_test_periodand preferredTestQuery.

这确实是关键的一步。您可能还想设置Validate At Most Once一些合理的值，比如 100 秒。如果您使用的是 C3P0 或类似版本，请确保配置idle_test_period和preferredTestQuery.

Whatever you end up doing, it's important to test out your changes to see if they have the desired effect. To make the timeout happen faster in MySQL, you can temporarily set the wait_timeoutto something low like 30 seconds by editing my.cnf. This was a tremendous help in debugging this problem, as it allowed me to test changes in seconds, rather than hours.

无论您最终做什么，重要的是测试您的更改以查看它们是否具有预期效果。为了使 MySQL 中的超时发生得更快，您可以wait_timeout通过编辑my.cnf. 这对调试这个问题非常有帮助，因为它让我可以在几秒钟内而不是几小时内测试更改。

采纳答案by matt b

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.

您应该考虑在应用程序中使用之前使连接有效性过期和/或测试连接有效性，增加客户端超时的服务器配置值，或使用连接器/J 连接属性“autoReconnect=true”来避免此问题。

Just a shot in the dark, but have you taken a look at setting the autoReconnect=trueproperty in your JDBC driver? Or consider disabling the server-side setting for client connection timeouts.

只是在黑暗中拍摄，但您是否看过在autoReconnect=trueJDBC 驱动程序中设置属性？或者考虑禁用客户端连接超时的服务器端设置。

回答by jpkrohling

I think the real question is: why are you using an external connection pooling mechanism instead of using Glassfish' own pooling? Your app server is better suited to provide this kind of service to your application. "External" connection pooling mechanisms are better suited for standalone applications, not in-container applications.

我认为真正的问题是：为什么要使用外部连接池机制而不是使用 Glassfish 自己的池？您的应用服务器更适合为您的应用程序提供此类服务。“外部”连接池机制更适合独立应用程序，而不是容器内应用程序。

回答by Binil Thomas

I think the property to set the connection test period is idle_test_period, not idleTestPeriodas per C3P0 documentation here. So you should be using:

我认为设置连接测试周期的属性idle_test_period不是idleTestPeriod按照 C3P0 文档这里的。所以你应该使用：

<property name="hibernate.c3p0.idle_test_period" value="30"/>

instead.

反而。

回答by chaitanya89

I was getting the same problem and it took time to figure out the solution.

我遇到了同样的问题，需要时间来找出解决方案。

I use Hibernate 4.0.1 and mysql 5.1(no spring framework) and I was facing the issue. First make sure that you configured the c3p0 jars properly which are essential.

我使用 Hibernate 4.0.1 和 mysql 5.1（没有 spring 框架），我遇到了这个问题。首先确保您正确配置了 c3p0 jar，这是必不可少的。

I used these properties in hibernate.cfg.xml

我在 hibernate.cfg.xml 中使用了这些属性

<property name="hibernate.c3p0.validate">true</property>
<property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.preferredTestQuery">SELECT 1;</property>
<property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
<property name="hibernate.c3p0.idle_test_period">10</property>
<property name="hibernate.c3p0.acquireRetryAttempts">5</property>
<property name="hibernate.c3p0.acquireRetryDelay">200</property>
<property name="hibernate.c3p0.timeout">40</property>

But it's of no use 'cause C3p0 was still taking the default properties not the properties which I set in hibernate.cfg.xml, You can check it in logs. So, I searched many websites for right solution and finally I came up with this. remove the C3p0 properties in cfg.xml and create c3p0-config.xml in the root path(along with cfg.xml) and set properties as follows.

但它没有用，因为 C3p0 仍然采用默认属性，而不是我在 hibernate.cfg.xml 中设置的属性，您可以在日志中检查它。所以，我在很多网站上搜索了正确的解决方案，最后我想出了这个。删除 cfg.xml 中的 C3p0 属性并在根路径中创建 c3p0-config.xml（连同 cfg.xml）并按如下设置属性。

<c3p0-config>
<default-config> 
<property name="automaticTestTable">con_test</property>
<property name="checkoutTimeout">40</property> 
<property name="idleConnectionTestPeriod">10</property> 
<property name="initialPoolSize">10</property>
<property name="maxPoolSize">20</property> 
<property name="minPoolSize">5</property> 
<property name="maxStatements">50</property>
<property name="preferredTestQuery">SELECT 1;</property>
<property name="acquireRetryAttempts">5</property>
<property name="acquireRetryDelay">200</property>
<property name="maxIdleTime">30</property>
</default-config>
</c3p0-config>

but if you run, ORM takes the jdbc connection but not C3p0 connection pool 'cause we should add these properties in hibernate.cfg.xml

但是如果你运行，ORM 会使用 jdbc 连接而不是 C3p0 连接池，因为我们应该在 hibernate.cfg.xml 中添加这些属性

<property name="hibernate.c3p0.validate">true</property>

<property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>

now everything works fine(At least it worked fine for me) and the issue is solved.

现在一切正常（至少对我来说很好）并且问题解决了。

check the following for references.

检查以下内容以获取参考。

http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool

https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool

I hope this solves your problem.

我希望这能解决你的问题。