首页 > 代码库 > Hibernate+C3P0下连接超时总结

Hibernate+C3P0下连接超时总结

2024-10-22 06:12:01   212人阅读

最近后台总是会遇到当几个游戏区并发通知后台写入统计数据时，发生连接已超时的问题，抛出如下异常，导致一些统计数据未有写进去.

Mysql服务器默认的“wait_timeout”是8小时【也就是默认的值默认是28800秒】，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection，通俗的讲就是一个连接在8小时内没有活动，就会自动断开该连接。
wait timeout的值可以设定，但最多只能是2147483，不能再大了。也就是约24.85天

这个参数大致的意思是这样：当一个客户端连接到MySQL数据库后，如果客户端不自己断开，也不做任何操作，MySQL数据库会将这个连接保留"wait_timeout"这么长时间（单位是s，默认是28800s，也就是8小时），超过这个时间之后，MySQL数据库为了节省资源，就会在数据库端断开这个连接；当然，在此过程中，如果客户端在这个连接上有任意的操作，MySQL数据库都会重新开始计算这个时间。

这么看来，发生上面Exception的原因就是因为我的服务器和MySQL数据库的连接超过了”wait_timeout"时间，MySQL服务器端将其断开了，但是我的程序再次使用这个连接时没有做任何判断，所以就挂了。

异常日志如下:

Caused by: org.hibernate.TransactionException: JDBC begin transaction failed:
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:76) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1392) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:473) ~[spring-orm-3.2.6.RELEASE.jar:3.2.6.RELEASE]
        ... 15 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 3,599,995 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 22,312,103 milliseconds ago.  The last packet sent successfully to the server was 22,312,105 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.GeneratedConstructorAccessor137.newInstance(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_65]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_65]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3941) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2551) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5339) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912) ~[c3p0-0.9.2.1.jar:0.9.2.1]
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1392) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:473) ~[spring-orm-3.2.6.RELEASE.jar:3.2.6.RELEASE]
        ... 21 more
Caused by: java.net.SocketException: 断开的管道
        at java.net.SocketOutputStream.socketWrite0(Native Method) ~[?:1.7.0_65]
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113) ~[?:1.7.0_65]
        at java.net.SocketOutputStream.write(SocketOutputStream.java:159) ~[?:1.7.0_65]
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[?:1.7.0_65]
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[?:1.7.0_65]
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3922) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2551) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5339) ~[mysql-connector-java-5.1.26-bin.jar:?]
        at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912) ~[c3p0-0.9.2.1.jar:0.9.2.1]
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1392) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]
        at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:473) ~[spring-orm-3.2.6.RELEASE.jar:3.2.6.RELEASE]
        ... 21 more


以下是我做的C3P0配置，问题解决:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="jdbcUrl" value=http://www.mamicode.com/"jdbc:mysql://192.168.1.21:3306/game_admin?autoReconnect=true&useUnicode=true&characterEncoding=utf-8"/>>

由于我配置的是C3P0的配置，和hibernate下进行C3P0配置不同，这个要注意
c3p0.maxIdleTime=hibernate.c3p0.timeout
配置是否生效请参考：com.mchange.v2.c3p0.ComboPooledDataSource 这个类
参考:
使用Hibernate连接MySQL数据库，MySQL连接超时断开的问题
c3p0 - JDBC3 Connection and Statement Pooling

Hibernate+C3P0下连接超时总结


联系
我们