休眠/ mysql连接问题

Question

我们正在研究一个带有Hibernate 3 ORM的java struts2框架web应用程序。我们使用mysql作为我们的数据库。

我们在日志中发现了一些DB相关的异常。在配置的时间之前超时连接到数据库。这里是我们发现的共同特例。

104343235 [pool-6-thread-19] ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 100,838,460 milliseconds ago. The last packet sent successfully to the server was 100,838,461 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 
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99) 
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) 
    at org.hibernate.loader.Loader.doList(Loader.java:2536) 
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) 
    at org.hibernate.loader.Loader.list(Loader.java:2271) 
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452) 
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363) 
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) 
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) 
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) 
    at com.myproject.model.dao.entities.EntitiesDAO.getByIds(EntitiesDAO.java:148) 

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 100,838,460 milliseconds ago. The last packet sent successfully to the server was 100,838,461 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. 
    at sun.reflect.GeneratedConstructorAccessor345.newInstance(Unknown Source)104343242 [pool-6-thread-16] ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 100,838,544 milliseconds ago. The last packet sent successfully to the server was 100,838,544 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. 
104343242 [pool-6-thread-16] ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 100,838,544 milliseconds ago. The last packet sent successfully to the server was 100,838,544 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. 

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525) 
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) 
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117) 
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3829) 
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2449) 
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629) 
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719) 
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) 
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318) 
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) 
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) 
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953) 
    at org.hibernate.loader.Loader.doQuery(Loader.java:802) 
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) 
    at org.hibernate.loader.Loader.doList(Loader.java:2533) 
    ... 21 more 

我们每天都重新启动我们的应用服务器，作为该问题的临时解决方案。

任何帮助将不胜感激。

Answer 1

发生这种情况是因为MySQL服务器在超时后终止没有活动的连接。 你需要几行添加到DBCP配置 尝试添加下列参数：

validationQuery="SELECT 1" 
testOnBorrow="true 

工作原理：连接池试图返回连接之前运行validationQuery。如果validationQuesry失败，dbcp将放弃连接，创建一个新连接并将其返回。

下面是一个例子：

<Resource name="jdbc/cooldatabase" 
      description="Strandls.com license database" 
      auth="Container" 
      type="javax.sql.DataSource" 
      factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" 
      driverClassName="com.mysql.jdbc.Driver" 
      url="jdbc:mysql://localhost:3306/cooldatabase?autoReconnect=true" 
      username="cooluser" 
      password="coolpassword" 
      initialSize="0" 
      maxActive="20" 
      maxIdle="10" 
      minIdle="0" 
      maxWait="-1" 
      validationQuery="SELECT 1" 
      testOnBorrow="true" 
      poolPreparedStatements="true" 
      removeAbandoned="true" 
      removeAbandonedTimeout="60" 
      logAbandoned="true"/> 

您可以在这里找到完整的详细信息：http://amitcodes.wordpress.com/2008/07/26/16/

Answer 2

使用与Hibernate连接池。例如c3p0https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool。