使用Hibernate和Apache DBCP与MySQL连接池问题 [英] Connection Pool Issue with MySQL using Hibernate and Apache DBCP

问题描述

似乎是我的应用程序的问题。当应用程序长时间闲置（不确定确切的时间）后，我会在我的日志中看到以下错误消息。我使用Spring + Hibernate + MySQL和ApacheDBCP进行连接池

  ERROR [org.hibernate.util.JDBCExceptionReporter]最后一个数据包成功从服务器收到74,188,684毫秒前。 
成功发送到服务器的最后一个数据包是74,188,685毫秒前。比服务器配置的'wait_timeout'值长。在应用程序中使用之前，应考虑过期和/或测试连接有效性，增加服务器配置的客户端超时值，或者使用Connector / J连接属性'autoReconnect = true'来避免此问题。 
 org.hibernate.exception.JDBCConnectionException：无法执行查询
  

如果我重新启动URL一切正常。我认为这与我的连接池有关。这里是我的Apache DBCP设置，并且MYSQL中的wait_timeout被设置为默认值。 （28800秒或8小时）。

 < beans：bean id =MyIDclass =org.apache.commons。 dbcp.BasicDataSourcedestroy-method =close> 
< beans：property name =driverClassNamevalue =com.mysql.jdbc.Driver/> 
< beans：property name =urlvalue =jdbc：mysql：// localhost：17761 / myDB/> 
< beans：property name =usernamevalue =myname/> 
< beans：property name =passwordvalue =mypwd/> 
< beans：property name =maxIdlevalue =5/> 
< beans：property name =maxActivevalue =20/> 
< beans：property name =minIdlevalue =5/> 
< / beans：bean> 
  

当我搜索时，我发现这个 link 可能的解决方案。

1. 使用autoReconnect = true配置连接字符串

   
   
   

   解决这个问题？在上面提到的同一问题中，另一位作者说，这个解决方案并不可取。

增加MySQL服务器变量wait_timeout的值。


2. 
   
   

   我不确定这将如何帮助解决错误。如果有帮助，如何计算适当的wait_timeout值。我的意思是应该是什么标准呢？
   
   $ b

经过一番研究之后，我开始知道在执行实际查询之前，我可以使用validationQuery =SELECT 1来检查连通性，这有助于解决问题吗？不会影响性能吗？

问题仅仅是针对Apache DBCP，我也曾在C3P0中看到它。

请帮助。

解决方案

看起来我找到了解决方案。
我所做的只是在我的servlet-context.xml中添加下面的行。仍检查此行的性能影响

 < beans：property name =validationQueryvalue =SELECT 1/> gt ; 
  

问题仍然存在，为什么我实际上遇到错误？ ：）

Seems to be an issue with my application. When ever the application is idle for a very long time ( am not sure the exact timing ) upon launching I get following error message in my logs. I am using Spring+Hibernate+MySQL and ApacheDBCP for connection pooling

ERROR [org.hibernate.util.JDBCExceptionReporter]The last packet successfully received from the server was 74,188,684 milliseconds ago. 
The last packet sent successfully to the server was 74,188,685 milliseconds ago. 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.
org.hibernate.exception.JDBCConnectionException: could not execute query

If I relaunch the URL everything works fine. I assume that this is something to do with my connection pooling. Here is my Apache DBCP setting and wait_timeout in MYSQL is set to its default. (28800 Secs or 8Hrs ).

<beans:bean id="MyID" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <beans:property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <beans:property name="url" value="jdbc:mysql://localhost:17761/myDB"/>
        <beans:property name="username" value="myname"/>
        <beans:property name="password" value="mypwd"/>
        <beans:property name="maxIdle" value="5"/>
        <beans:property name="maxActive" value="20"/>
        <beans:property name="minIdle" value="5"/>
</beans:bean>

While, I was searching I found this link The possible options for solving the issue.

1. Configure the connection string with autoReconnect=true

   Will this help solving the issue ? In the same question mentioned above another author says, it is not advisable to go for this solution.

2. Increasing the value of MySQL server variable wait_timeout.

   I am not sure how this will help in solving the error. if it helps, how to calculate the appropriate wait_timeout value. I mean what should be the criteria for this ?

3. Configuring the connection pool to test the validity of the connection.

   After some study, I came to know that I can use validationQuery="SELECT 1" to check the connectivity before doing actual Query, will this help in resolving the issue ? Wont it affect the performance ?.

Issue is just not just specific to Apache DBCP, I have seen it in C3P0 also.

Please help.

解决方案

Looks like I found the solution for it. All I did was to add below line in my servlet-context.xml. Still checking the performance impact of this line

<beans:property name="validationQuery" value="SELECT 1"/>

Question remains, Why was I actually getting the error ? :)

这篇关于使用Hibernate和Apache DBCP与MySQL连接池问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！