帮助我避免与 JPA、Hibernate 和 JPA 的连接超时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 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)
我尝试在我的 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"/>
这就是 C3p0 配置；我完全有可能错过了实际告诉 hibernate“嘿，使用 c3p0”的部分。
我正准备尝试错误消息中的建议：将 autoReconnect=true 添加到我的 JDBC URL，但此时这确实开始让人感觉像是货物崇拜开发。我希望获得有关解决此问题的正确方法的指导。调试很困难，因为测试周期实际上是“运行过夜，看看早上会发生什么”。
我可能应该提到我实际上是如何在我的应用程序中使用连接的。我有一个自定义的 Servlet Filter 来拦截所有请求。它创建一个 EntityManager，将其存储在 ThreadLocal 中，并由 catch/finally 块中的过滤器关闭。我的所有实体都从 ThreadLocal 获取对 EntityManager 的引用。
我的过滤器完全有可能出现故障，但由于它似乎只在空闲期后发生，因此我怀疑还有其他问题。当我有机会喘口气时，我确实打算转向接缝/焊接，但现在我依靠这个过滤器。
编辑：这里是TL;DR解决方案：
- 使用容器的连接池，如果可以的话（感谢@partenon）
- 确保您的连接池使用连接验证（感谢@ matt b)
就我而言，我必须进入 Resources/JDBC/Connection Pools、Advanced 选项卡下的 Glassfish 控制台，然后启用连接验证：
这确实是关键的一步。您可能还希望将 Validate At Most Once 设置为合理的值，例如 100 秒。如果您使用的是 C3P0 或类似产品，请确保配置 idle_test_period 和 preferredTestQuery。
无论您最终做什么，测试您的更改以查看它们是否具有预期的效果都很重要。为了使 MySQL 中的超时发生得更快，您可以通过编辑 my.cnf 临时将 wait_timeout 设置为较低的值，例如 30 秒。这对调试这个问题有很大帮助，因为它允许我在几秒钟内而不是几个小时内测试更改。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我认为真正的问题是：为什么使用外部连接池机制而不是使用 Glassfish 自己的池？您的应用程序服务器更适合为您的应用程序提供此类服务。 “外部”连接池机制更适合独立应用程序，而不是容器内应用程序。
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.
只是猜测，但是您是否考虑过在 JDBC 驱动程序中设置
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.我认为设置连接测试周期的属性是
idle_test_period，而不是 C3P0 文档中的idleTestPeriod此处。所以你应该使用：代替。
I think the property to set the connection test period is
idle_test_period, notidleTestPeriodas per C3P0 documentation here. So you should be using:instead.
我遇到了同样的问题，花了一些时间才找到解决方案。
我使用 Hibernate 4.0.1 和 mysql 5.1（没有 spring 框架），我遇到了这个问题。首先确保您正确配置了 c3p0 jar，这是必不可少的。
我在 hibernate.cfg.xml 中使用了这些属性，
但它没有用，因为 C3p0 仍然采用默认属性而不是我在 hibernate.cfg.xml 中设置的属性，您可以在日志中检查它。因此，我在很多网站上搜索了正确的解决方案，最后我想出了这个。删除 cfg.xml 中的 C3p0 属性，并在根路径中创建 c3p0-config.xml（以及 cfg.xml），并按如下方式设置属性。
但如果你运行，ORM 采用 jdbc 连接而不是 C3p0 连接池，因为我们应该在 hibernate.cfg.xml 中添加这些属性，
现在一切正常（至少对我来说工作正常）并且问题得到解决。
检查以下内容以获取参考。
http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing
https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool
我希望这能解决你的问题。
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.
I used these properties in hibernate.cfg.xml
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.
but if you run, ORM takes the jdbc connection but not C3p0 connection pool 'cause we should add these properties in hibernate.cfg.xml
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
https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool
I hope this solves your problem.