使用Spring，Hibernate和C3P0的设置来重现com.mysql.jdbc.exceptions.jdbc4.CommunicationsException [英] Reproduce com.mysql.jdbc.exceptions.jdbc4.CommunicationsException with a setup of Spring, hibernate and C3P0

问题描述

我从产品代码中得到了这个错误：

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException：
最后一个数据包从服务器成功接收
的时间为36940秒
ago。最后一个数据包成功发送
到服务器的时间为36940秒前，
比服务器的时间长
配置了'wait_timeout'的值。
在应用程序中使用之前，您应考虑到期
和/或测试连接有效性
，
增加服务器配置的客户端超时
值，或者使用
连接器/ J连接属性
'autoReconnect = true'来避免这个
问题。

现在我试图在本地重现问题并修复它。

 < bean id =dataSourceclass =com.mchange.v2.c3p0 .ComboPooledDataSource
< bean id =dataSourceclass =com.mchange.v2.c3p0.ComboPooledDataSource
 destroy-method =closep：driverClass =com.mysql.jdbc。驱动程序
p：jdbcUrl =jdbc：mysql：// localhost：3306 / test？userUnicode = yes& amp; ampE amp; amp; amp; amp; amp; amp; amp; amp; amp; 1p：maxIdleTime =1800
p：maxPoolSize =1p：minPoolSize =1p：checkoutTimeout =1000
 
 /> 
 
< bean id =sessionFactoryclass =org.springframework.orm.hibernate3.LocalSessionFactoryBean> 
< property name =dataSourceref =dataSource/> 
< property name =hibernateProperties> 
<值> 
 hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider 
 hibernate.dialect = org.hibernate.dialect.MySQL5Dialect 
 hibernate.default_schema = platform_server_original 
 hibernate.show_sql = false 
< /值> 
< / property> 
< property name =mappingResources> 
< list> 
<值> sometables.hbm.xml< /值> 
< / list> 
< / property> 
< / bean> 
  

然后我将我的mysql wait_timeout设置为10秒，然后运行我的测试，基本上打开一个连接，做一个查询，关闭它，所以它返回到池中，然后睡眠该线程15秒，然后再次打开一个连接，并再次执行查询，因此它会中断。但是，我仅收到类似的错误：

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException：
通信链路故障

发送到服务器的最后一个数据包是16
ms前。

所以我想知道这两个错误是一样的，还是不同？我做了一些研究，看起来这两个错误都归结为相同的解决方案：使用属性testConnectionOnCheckout = true。但是，根据c3p0 doc，这是一个非常昂贵的检查。它建议使用idleConnectionTestPeriod，但我已经将其设置为120秒。我应该使用什么值，以便它可以正确验证闲置连接？

所以我基本上问两件事：
1.如何重现生产代码中有错误？
2.我该如何修复它？

谢谢！

解决方案

我有类似的MySQL和连接池问题。问题是您告诉连接池空闲超时时间为30分钟，但数据库在10秒后切断连接。由于您的空闲连接检查周期为120秒，因此游泳池使用连接断开的时间稍微低于110秒！

我会使用以下设置进行生产：

  MySQL：
 wait_timeout = 75 
 C3P0：
 maxIdleTime = 60 
 idleConnectionTestPeriod = 55 
  

I got this error from the production code:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was36940 seconds ago.The last packet sent successfully to the server was 36940 seconds 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.

And now I'm trying to reproduce the problem locally and fix it. I setup the spring context as following:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close" p:driverClass="com.mysql.jdbc.Driver"
    p:jdbcUrl="jdbc:mysql://localhost:3306/test?userUnicode=yes&amp;characterEncoding=UTF-8&amp"
    p:idleConnectionTestPeriod="120" p:initialPoolSize="1" p:maxIdleTime="1800"
    p:maxPoolSize="1" p:minPoolSize="1" p:checkoutTimeout="1000"

/>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="hibernateProperties">
        <value>
            hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider
            hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
            hibernate.default_schema=platform_server_original
            hibernate.show_sql=false
        </value>
    </property>
    <property name="mappingResources">
        <list>
            <value>sometables.hbm.xml</value>
        </list>
    </property>
</bean>

Then I set my mysql wait_timeout to 10 seconds, then run my test, which is basically open a connection, do a query, close it, so it returns to the pool, then sleep the thread for 15 seconds, and then open a connection again, and do a query again, so it will break. However, I got a similar error only:

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

Last packet sent to the server was 16 ms ago.

So I wonder are these two errors the same, or they are different? I did some researches, and it seems both errors came down to the same solution: using a property "testConnectionOnCheckout=true". However, according to c3p0 doc, this is a very expensive check. It advises the use of "idleConnectionTestPeriod", but I'm already setting that to 120 seconds. What value should I use it so it can properly verify the idle connection?

So I'm basically ask two things: 1. how do I reproduce the error I got in production code? 2. how do I fix it?

Thanks!

解决方案

I had similar problems with MySQL and a connection pool. The problem is you tell the connection pool that an idle timeout is 30 minutes, but the database cuts the connection after 10 seconds. Since your idle connection check period is 120 sec, it leaves a little under 110 secs for the pool to use a broken connection!

I'd use the following settings for production:

MySQL:
wait_timeout=75
C3P0:
maxIdleTime=60
idleConnectionTestPeriod=55

这篇关于使用Spring，Hibernate和C3P0的设置来重现com.mysql.jdbc.exceptions.jdbc4.CommunicationsException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！