连接超时后，Hibernate 无法重新连接到 MySQL。

Question

我有一个Java应用程序，我想创建连接池。我正在使用Hibernate 4.0_final和c3p0，在Jboss AS 7.0.2上使用MySQL。

hibernate.cfg.xml：

<hibernate-configuration>
 <session-factory >
  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.connection.password"></property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
  <property name="hibernate.show_sql">true</property>

  <property name="hibernate.c3p0.acquire_increment">1</property>
  <property name="hibernate.c3p0.idle_test_period">100</property>
  <property name="hibernate.c3p0.max_size">100</property>
  <property name="hibernate.c3p0.max_statements">0</property>
  <property name="hibernate.c3p0.min_size">5</property>
  <property name="hibernate.c3p0.timeout">30</property>
 <!--  <property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property> -->

  <mapping class="com.test.Test"/>

c3p0.properties:

c3p0.testConnectionOnCheckout=true
c3p0.acquireRetryDelay=1000
c3p0.acquireRetryAttempts=1

我使用的类用于获取会话，以便与数据库进行交互。

public class HibernateSessionFactory {

    private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";
    private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
    private  static org.hibernate.cfg.Configuration configuration = new org.hibernate.cfg.AnnotationConfiguration();    
    private static org.hibernate.SessionFactory sessionFactory;
    private static String configFile = CONFIG_FILE_LOCATION;

    static {
        rebuildSessionFactory();
    }

    public static Session getSession() throws HibernateException {
        Session session = (Session) threadLocal.get();
        if (session == null || !session.isOpen()) {
            if (sessionFactory == null) {
                rebuildSessionFactory();
            }
            session = (sessionFactory != null) ? sessionFactory.openSession(): null;
            threadLocal.set(session);
        }
        return session;
    }

    public static void rebuildSessionFactory() {
        try {
            configuration.configure(configFile);
            sessionFactory = configuration.buildSessionFactory();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

创建了SessionFactory之后，我在MySQL_WorkBench中看到了5个连接到我的数据库。之后，我发送请求并等待50秒（在MySql_Server中设置的wait_timeout，用于测试）。在Workbench中，我看到已经打开了4个连接。我发送请求并收到错误，但在workbench中，我已经看到了5个连接。

错误：

org.hibernate.exception.JDBCConnectionException: No operations allowed after connection closed.
    org.hibernate.exception.internal.SQLStateConverter.convert(SQLStateConverter.java:107)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:146)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy18.prepareStatement(Unknown Source)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1739)
    org.hibernate.loader.Loader.doQuery(Loader.java:828)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)


root cause 
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    com.mysql.jdbc.Util.getInstance(Util.java:386)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1205)
    com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1197)
    com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4186)
    com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4152)
    com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy18.prepareStatement(Unknown Source)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1739)
    org.hibernate.loader.Loader.doQuery(Loader.java:828)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)


root cause 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 96 189 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy19.executeQuery(Unknown Source)
    org.hibernate.loader.Loader.getResultSet(Loader.java:1978)
    org.hibernate.loader.Loader.doQuery(Loader.java:829)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)


root cause 
java.net.SocketException: Software caused connection abort: recv failed
    java.net.SocketInputStream.socketRead0(Native Method)
    java.net.SocketInputStream.read(SocketInputStream.java:150)
    java.net.SocketInputStream.read(SocketInputStream.java:121)
    com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
    com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
    com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
    com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2549)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy19.executeQuery(Unknown Source)
    org.hibernate.loader.Loader.getResultSet(Loader.java:1978)
    org.hibernate.loader.Loader.doQuery(Loader.java:829)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)

如何解决这个问题？

- Novarg

Answer 1

我曾经遇到过这样的问题，后来从服务器日志中找到了解决方案。

    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was56936 milliseconds ago.The last packet sent successfully to the server was 56936 milliseconds ago, 
which  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.

尝试将连接URL设置为：

<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test?autoReconnect=true</property>

但是当使用任何事务性存储引擎时，他们不建议这样做。

- tusar

Answer 2

尝试通过将以下内容添加到my.cnf中来增加超时时间：

[mysqld]
interactive_timeout=864000
wait_timeout=864000

- Selcuk