Hibernate c3p0 keep alive似乎不起作用，出现“中断管道”异常

Question

我在我的web应用程序中结合使用Hibernate和c3p0（没有Spring）。一夜之间，我的数据库连接似乎超时（或至少其中一个），第二天我会得到一个中断的管道异常。
下面是我在项目中使用的配置。

    <hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/**********?autoReconnect=true</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
        <property name="hibernate.connection.username">********</property>
        <property name="hibernate.connection.password">********</property>
        <property name="hibernate.show_sql">false</property>
        <property name="hibernate.globally_quoted_identifiers">true</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
        <property name="hibernate.connection.autocommit">false</property>
        <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">25</property>
        <property name="hibernate.c3p0.timeout">300</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">60</property>
        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.acquireRetryAttempts">5</property>
        <property name="hibernate.c3p0.acquireRetryDelay">250</property>
        <property name="hibernate.c3p0.preferredTestQuery">select 1;</property>
        <property name="hibernate.c3p0.validate">true</property>
        <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
        <property name="hibernate.c3p0.testConnectionOnCheckin">true</property>
    </session-factory>
</hibernate-configuration>

我得到的例外情况如下：

    The last packet successfully received from the server was 56,974,967 milliseconds ago.
    The last packet sent successfully to the server was 56,974,967 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: The last packet successfully received
    from the server was 56,974,967 milliseconds ago.  The last packet sent successfully to
    the server was 56,974,967 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.

这里的问题是，我正在使用属性autoreConnect=true，并且我正在测试连接的有效性。（参见c3p0配置。）我很确定c3p0配置已经加载，因为我的stdout日志中显示了以下内容：

    Apr 08, 2014 10:57:13 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
    INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@f79fb235 [ 
    connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@4e20d034 [ 
    acquireIncrement -> 1, acquireRetryAttempts -> 5, acquireRetryDelay -> 250, autoCommitOnClose -> 
    false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, 
    connectionCustomizerClassName -> null, connectionTesterClassName -> 
    com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, 
    factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 
    ph9ube91hyyxjw1mgbhpo|709689fb, idleConnectionTestPeriod -> 60, initialPoolSize -> 3, 
    maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 300, 
    maxIdleTimeExcessConnections -> 0, maxPoolSize -> 25, maxStatements -> 50, maxStatementsPerConnection
     -> 0, minPoolSize -> 5, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@a1ca3a3 [ 
    description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 
    ph9ube91hyyxjw1mgbhpo|24bbab7, jdbcUrl -> jdbc:mysql://localhost:3306/********?autoReconnect=true, 
    properties -> {user=******, password=******, autocommit=false} ], preferredTestQuery -> select 1;, 
    propertyCycle -> 0, testConnectionOnCheckin -> true, testConnectionOnCheckout -> true, 
    unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], 
    dataSourceName -> null, factoryClassLocation -> null, identityToken -> ph9ube91hyyxjw1mgbhpo|fedb05d,
     numHelperThreads -> 3 ]

    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 56,974,967 milliseconds ago.  The last packet sent successfully to the server was 56,974,967 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.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:525)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3851)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2471)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2310)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
        at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
        ... 23 more
    Caused by: java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3832)
        ... 33 more

• 将autoreconnect=true添加到连接URL（请注意，尝试使用后连接仍然中断，因此似乎不会重新连接）
• 将testConnectionOnCheckout和testConnectionOnCheckin添加到c3p0配置
• 将idle_test_period和preferredTestQuery添加到c3p0配置

似乎什么都不起作用。有人知道是什么导致了这个问题吗？

Answer 1

所以，一些零散的想法：

您的PreferredTestQuery中有一个错误。它应该是“select 1”，而不是“select 1；”。我认为如果这是一个问题，您会看到它的异常（并且签出将全部失败），但这是值得尝试解决的。

[您使用的是什么版本的c3p0？如果您希望生活得很危险，并且正在使用最新的MySQL驱动程序，请尝试使用最新的预发布版本，当前为C3P0-0.9.5-pre8，并且完全不要设置PreferredTestQuery。最新版本支持JDBC4 Connection.IsValid（）进行测试。]

另一种可能是，您的应用程序偶尔会将连接打开（签出）很长时间。这不会造成连接泄漏，因为应用程序最终会尝试针对连接执行一条语句。但如果您的应用程序这样做，您将看到您所看到的，因为c3p0不会在客户端使用时测试连接，因此它们可以空闲。（我不知道为什么autoReconnect没有帮助，除非您正在事务处理中，而autoReconnect对此不起作用？）如果要针对此场景进行测试，可以使用UnReturndConnectionTimeout（可选地与DebugunReturndConnectionStackTraces一起使用）强制关闭签出时间过长的异常并尝试诊断问题。

如果您只是想让问题消失（并且不太关心理解它），您也可以尝试设置C3P0的maxConnectionAge和/或MaxIdleTime。如果将这些设置设置为大值，则这些设置不会对性能产生有意义的影响。（可能是7200秒，也就是两个小时，不过超过1800秒就可以了。）这些设置（特别是maxConnectionAge）是非常简单的，除非麻烦的连接（如上面所述）保持打开并从池中签出很长时间。[UnreturndConnectionTimeout是从客户端下关闭（）签出连接的唯一c3p0设置。]

祝你好运！