Summary of connection timeout in Hibernate + C3P0,

Summary of connection timeout in Hibernate + C3P0,

Recently, the background always encounters a connection timeout problem when several game zones concurrently notify the background to write statistics. The following exception is thrown, causing some statistics not to be written.

The default "wait_timeout" of the Mysql server is 8 hours (that is, the default value is 28800 seconds). That is to say, if a connection is idle for more than 8 hours, Mysql will automatically disconnect the connection, in general, a connection is automatically disconnected if it is not active within 8 hours.
The value of wait timeout can be set, but it can only be 2147483 at most and cannot be larger. That is, about 24.85 days

This parameter roughly indicates that after a client connects to the MySQL database, if the client is not disconnected by itself, no operation is performed, the MySQL database keeps the connection "wait_timeout" for such a long time (unit: s, default value: 28800 s, that is, 8 hours). After this time, the MySQL database saves resources, the connection will be closed on the Database End. Of course, if the client has any operation on the connection during this process, the MySQL database will start to calculate the time again.

In this case, the cause of the above Exception is that the connection between my server and MySQL database exceeds the "wait_timeout" time, And the MySQL server disconnects it, however, my program did not make any judgment when using this connection again, so it went down.

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]        ... 15 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 encoded ed 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 shoshould 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. generatedconstructextends cessor137.newinstance (Unknown Source )~ [? :?] At sun. reflect. delegatingconstruct%cessorimpl. newInstance (delegatingconstruct%cessorimpl. 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.exe cSQL (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 moreCaused by: java.net. SocketException: broken 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. 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.exe cSQL (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

Here is my C3P0 configuration and the problem is solved:

<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 value is 3 --> <property name = "initialPoolSize" value = "2"/> <! -- The minimum number of connections retained in the connection pool. The default value is 3 --> <property name = "minPoolSize" value = "2"/> <! -- The maximum number of connections retained in the connection pool. Default value: 15 --> <property name = "maxPoolSize" value = "15"/> <! -- The number of connections that c3p0 obtains at the same time when connections in the connection pool are exhausted. Default value: 3 --> <property name = "acquireIncrement" value = "2"/> <! -- Defines the number of repeated attempts after a new connection fails to be obtained from the database. Default value: 30; if the value is less than or equal to 0, it indicates an unlimited number of times --> <property name = "acquireRetryAttempts" value = "0"/> <! -- Retry Interval. Default value: 1000 ms --> <property name = "acquireRetryDelay" value = "1000"/> <! -- Maximum idle time. connections are discarded if they are not used within 3600 seconds. If it is 0, it will never be discarded. Default value: 0 --> <property name = "maxIdleTime" value = "3600"/> <! -- Global PreparedStatements cache size of c3p0. If maxStatements and maxStatementsPerConnection are both 0, the cache does not take effect. If one is not 0, the statement cache takes effect. If the default value is 0 --> <property name = "maxStatements" value = "0"/> <! -- MaxStatementsPerConnection defines the maximum number of statements cached for a single connection in the connection pool. Default value: 0 --> <property name = "maxStatementsPerConnection" value = "0"/> <! -- Define the test statement executed for all connection tests. This significantly increases the testing speed when connection tests are used. The test table must exist at the initial data source. Default: null --> <property name = "preferredTestQuery" value = "select 1"/> <! -- Check all idle connections in the connection pool every 1800 seconds. Default: 0 --> <property name = "idleConnectionTestPeriod" value = "1800"/> <! -- Test the connection validity when obtaining the connection, and verify whether the connection is available each time --> <property name = "testconnectioncheckout" value = "false"/> </bean> </span>

The configuration of C3P0 is different from that of C3P0 in hibernate.

C3p0. maxIdleTime = hibernate. c3p0. timeout

For more information about whether the configuration takes effect, see com. mchange. v2.c3p0. ComboPooledDataSource.

Refer:

Use Hibernate to connect to the MySQL database. MySQL connection times out and is disconnected.

C3p0-JDBC3 Connection and Statement Pooling