我们正在使用 Hibernate 3 ORM 开发一个 java struts2 框架网络应用程序。我们使用 mysql 作为我们的数据库。

我们在日志中发现了一些与数据库相关的异常。与数据库的连接在配置的时间之前超时。以下是我们发现的常见异常。

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

我们每天都会重新启动我们的应用服务器，作为对问题的临时修复。

我们将不胜感激任何帮助。

最佳答案

发生这种情况是因为 MySQL 服务器在超时后终止了没有 Activity 的连接。 您需要在 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/

关于java - Hibernate/mysql 连接问题，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/13115648/