Summary of HIBERNATE+C3P0 Connection timeout

Recently the background always encountered when several game areas concurrent notification background write statistics, the connection has timed out the problem, throw the following exception, causing some statistics are not written in.

MySQL server default "Wait_timeout" is 8 hours "is the default value default is 28,800 seconds", that is, a connection idle for more than 8 hours, MySQL will automatically disconnect the connection, In layman's words, a connection is automatically disconnected if there is no activity within 8 hours.
The value of wait timeout can be set, but can be at most 2147483, no longer large. That's about 24.85 days.

The general meaning of this parameter is this: When a client connects to the MySQL database, if the client does not disconnect itself and does nothing, the MySQL database will keep the connection "wait_timeout" for such a long time (in units of S, the default is 28800s, that is, 8 hours), After this time, the MySQL database will be disconnected from the database in order to conserve resources, and of course, in this process, if the client has any action on the connection, the MySQL database will start calculating the time again.

So, the reason for the above exception is because my server and MySQL database connection exceeded the "wait_timeout" time, the MySQL server to disconnect it, but my program again use this connection without making any judgment, so it hangs.

The Exception log is as follows:

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]        ... morecaused by:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failurethe 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 '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er 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.jdb        C.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] ... morecaused by:java.net.SocketException: Disconnected pipe 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.So Cketoutputstream.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] ... More

Here are the C3P0 configurations I made, problem solving:

<bean id= "DataSource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method= "Close" ><property Name= "Jdbcurl" value= "jdbc:mysql://192.168.1.21:3306/game_admin?autoreconnect=true&useunicode=true& Characterencoding=utf-8 "/><property name=" Driverclass "value=" Com.mysql.jdbc.Driver "/><property name= "User" value= "Linyu"/><property name= "password" value= "com.123"/><!--Initialize the number of connections in the connection pool, The value should be between Minpoolsize and Maxpoolsize, the default is 3--> <property name= "Initialpoolsize" value= "2"/><!--the minimum number of connections kept in the connection pool, The default is:3--> <property name= "Minpoolsize" value= "2"/><!--the maximum number of connections that are kept in the connection pool. Default:----<property name= "maxpoolsize" value= "/><!--c3p0 the number of connections that are fetched at the same time when the connection in the connection pool is exhausted. Default value: 3--<property name= "Acquireincrement" value= "2"/><!--defines the number of repeated attempts to obtain a new connection from the database after a failure. Default value: 30, less than or equal to 0 means unlimited--<property name= "acquireretryattempts" value= "0"/> <!--retry interval, default is: 1000 ms---<property name= "Acquireretrydelay"Value= ""/> <!--maximum idle time, unused in 3,600 seconds, the connection is discarded. If 0, it will never be discarded. Default value: 0--<property name= "MaxIdleTime" value= "3600"/> <!--c3p0 the size of the global preparedstatements cache. If both maxstatements and maxstatementsperconnection are 0, the cache does not take effect, and if one is not 0, the statement's cache will take effect. If the default value: 0--> <property name= "maxstatements" value= "0"/> <!-- Maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. Default value: 0--<property name= "maxstatementsperconnection" value= "0"/><!--define test statements that are executed by all connection tests. This one significantly improves the test speed in the case of connection testing. The test table must exist at the time of the initial data source. Default:null--><property name= "Preferredtestquery" value= "Select 1"/> <!--every 1800 seconds to check for idle connections in all connection pools. default:0--><property name= "idleconnectiontestperiod" value= "1800"/><!--to test validity when connecting, verify that the connection is available every time- <property name= "Testconnectiononcheckout" value= "false"/></bean></span>

Since I configured the C3P0 configuration, and hibernate under the C3P0 configuration is different, this should pay attention to

C3p0.maxidletime=hibernate.c3p0.timeout

Configuration is valid please refer to: Com.mchange.v2.c3p0.ComboPooledDataSource this class

Reference:

Connect MySQL database with hibernate, MySQL connection timed out disconnect problem

C3P0-JDBC3 Connection and Statement Pooling

Summary of HIBERNATE+C3P0 Connection timeout