MySQLNonTransientConnectionException/jdbc4.CommunicationsException 错误在使用 C3P0 时继续

Question

我正在运行一个使用 Hibernate 和 C3P0 作为连接池、MySQL 5.6 数据库的 Java Web 应用程序。我继续在已部署的应用程序中遇到连接超时和生成错误的问题:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 426,126,080 milliseconds ago. The last packet sent successfully to the server was 426,126,081 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.

特别是，任何一段时间的不活动都会导致错误开始发生。长时间导致的错误如下:

(DefaultConnectionTester.java:148) [http-nio-8080-exec-9]: SQL State '08007' of Exception tested by statusOnException() implies that the database is invalid, and the pool should refill itself with fresh Connections. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown.

和

(C3P0PooledConnectionPool.java:981) [http-nio-8080-exec-9]: A ConnectionTest has failed, reporting that all previously acquired Connections are likely invalid. The pool will be reset.

Hibernate/C3P0 连接参数是:

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.initial_pool_size">5</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">50</property>
<property name="hibernate.c3p0.acquire_increment">5</property>
<property name="hibernate.c3p0.max_statements">500</property>
<property name="hibernate.c3p0.timeout">1800</property> <!-- in secs -->
<property name="hibernate.c3p0.idle_test_period">480</property> <!-- in secs -->
<property name="hibernate.c3p0.testConnectionOnCheckout">false</property>
<property name="hibernate.c3p0.testConnectionOnCheckin">true</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1</property>
<property name="hibernate.c3p0.maxConnectionAge">3600</property> <!-- in secs -->
<property name="hibernate.c3p0.numHelperThreads">8</property>
<property name="hibernate.c3p0.maxIdleTimeExcessConnections">600</property>

我已根据有关此主题和 C3P0 文档的其他评论反复尝试调整 C3P0 参数，但无济于事。 Web 应用程序经历了不活动和大量使用的时期，当然必须有一些关于如何在这个用例中配置连接池的指导，但我还没有找到任何可以解决这个问题的方法。

Answer 1

我认为您需要将 testConnectionOnCheckout 的值更改为:

<property name="hibernate.c3p0.testConnectionOnCheckout">true</property>

想象一下这个场景(使用您当前的配置):

• 池中有一个连接刚刚过期
• 每 480 秒检查一次空闲连接的线程尚未启动
• 用户访问该网站...从池中获取连接，您第一次尝试使用它时会遇到异常，因为它已过期且 C3P0 未在结帐时测试连接。

您必须做的另一件事是检查 mysql 中配置的超时。如果超时配置为 60 秒，而你每 480 秒检查一次连接......那么你所有的连接肯定会关闭。根据经验，空闲测试时间应为数据库中配置的超时时间的 1/2 到 3/4 左右，以确保连接保持打开状态。

您还需要考虑中间的防火墙，因为如果它们看不到任何流量，它们可能会更快地关闭连接。