帮助我避免与JPA，Hibernate& MySQL的 [英] help me avoid connection timeout with JPA, Hibernate & MySQL

问题描述

我使用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开始失败
 com .mysql.jdbc.exceptions.jdbc4.CommunicationsException：从服务器成功接收的最后一个数据包是41,936,868毫秒前。成功发送到服务器的最后一个数据包\ 
的时间为41,936,868毫秒。比服务器配置的'wait_timeout'值长。在应用程序中使用之前，应考虑expirin \ 
g和/或测试连接有效性，增加服务器配置的客户端超时值或使用Connector / J连接
 tion属性autoReconnect =真来避免这个问题。 
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0（Native Method）
 at sun.reflect.NativeConstructorAccessorImpl.newInstance（NativeConstructorAccessorImpl.java:57）
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance（DelegatingConstructorAccessorImpl。 
 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.j ava：87）
 at org.hibernate.impl.SessionImpl.beginTransaction（SessionImpl.java:1473）
 at org.hibernate.ejb.TransactionImpl.begin（TransactionImpl.java:60）
  

我在 persistence.xml 中使用了以下内容，但它没有帮助：

 < property name =hibernate.c3p0.min_sizevalue =5/> 
< property name =hibernate.c3p0.max_sizevalue =20/> 
< property name =hibernate.c3p0.idleTestPeriodvalue =30/> 
< property name =hibernate.c3p0.timeoutvalue =0/> 
< property name =hibernate.c3p0.max_statementsvalue =0/> 
  

这就是C3p0配置;这完全有可能我错过了实际上告诉休眠的部分使用c3p0。

我将尝试在错误消息中提供的建议：为我添加 autoReconnect = true JDBC URL，但是现在真的开始感觉像货运邪教的发展。我希望就解决这个问题的正确方式提供一些指导。这很难调试，因为测试周期实际上是在一夜之间运行，看看早上发生了什么。

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

我的过滤器完全有可能出现故障，但由于它似乎只发生在空闲时间之后，我怀疑还有其他问题。当我有机会喘口气时，我打算转移到Seam / Weld，但现在我依靠这个过滤器。

编辑：这里是TL; DR解决方案：如果可以的话，感谢@partenon） li>

确保您的连接池使用连接验证（谢谢，@matt b）

在我的情况下，我必须在Resources / JDBC / Connection Pools，Advanced Tab下进入Glassfish控制台，然后启用连接验证：

这是真正关键的一步。你也可能想把最多一次验证设置为合理的值，比如说100秒。如果您使用的是C3P0或类似软件，请确保配置 idle_test_period 和 preferredTestQuery 。无论您最终做什么，测试您的更改以查看它们是否具有所需的效果都很重要。为了让MySQL在超时发生得更快，你可以通过编辑 my.cnf wait_timeout 设置为低至30秒C>。这对调试这个问题有很大的帮助，因为它允许我以秒为单位测试更改，而不是几小时。

b $ b

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

在黑暗中拍摄一张照片，但是您看过了设置 autoReconnect = true code>属性在你的JDBC驱动程序中？或者考虑禁用客户端连接超时的服务器端设置。

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:

[#|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:

        <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".

I'm about to try the suggestion that's right there in the error message: add autoReconnect=true to 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".

I should probably mention how I'm actually using connections in my app. I have a custom Servlet Filter that 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 EntityManager from the 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:

• use your container's connection pool, if you can (thanks, @partenon)
• make sure your connection pool uses connection validation (thanks, @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:

This was really the crucial step. You also probably want to set Validate At Most Once to something reasonable, say 100 seconds. If you're using C3P0 or similar, make sure you configure idle_test_period and 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_timeout to 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.

解决方案

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.

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

这篇关于帮助我避免与JPA，Hibernate& MySQL的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！