Using C3P0 with DBCP connection pool, resulting in MySQL 8-hour problem solving method

This article provides an C3P0 connection to the DBCP connection pool when you connect to the MySQL database. No requests for self-disconnecting solutions within 8 hours. Let's start by describing the issues I encountered in the project (C3P0 connection pool), followed by a workaround for using the DBCP connection pool.

Basic Problem Solving Project environment:

Java Web project Framework for spring MVC+JPA, using C3P0 connection pool, advertise the environment as Tomcat 7

Description of the error:

After a project has been executed for a period of time (about a few hours), the first interview error occurs, and the normal behavior is re-interviewed. And this issue occurs more than once.

Error log:

Org.springframework.transaction.CannotCreateTransactionException:Could not open JPA Entitymanager for transaction; Nested exception is Javax.persistence.PersistenceException:org.hibernate.TransactionException:JDBC begin Transaction Failed:at Org.springframework.orm.jpa.JpaTransactionManager.doBegin (jpatransactionmanager.java:428) At Org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction ( abstractplatformtransactionmanager.java:372) at Org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary ( transactionaspectsupport.java:417) at Org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction ( transactionaspectsupport.java:255) at Org.springframework.transaction.interceptor.TransactionInterceptor.invoke ( transactioninterceptor.java:94) at Org.springframework.aop.framework.ReflectiveMethodInvocation.proceed ( reflectivemethodinvocation.java:172) at Org.springframework.aop.framework.CglibAopPrOxy$dynamicadvisedinterceptor.intercept (cglibaopproxy.java:631) at com.appcarcare.cube.service.userservice$$ enhancerbycglib$ $a 4429cba.getuserdao (<generated>) at com.appcarcare.cube.servlet.datacenterservlet$ Sqltimer.connectsql (datacenterservlet.java:76) at Com.appcarcare.cube.servlet.datacenterservlet$sqltimer.run ( DATACENTERSERVLET.JAVA:70) at Java.util.TimerThread.mainLoop (timer.java:555) at Java.util.TimerThread.run ( timer.java:505) caused By:javax.persistence.PersistenceException:org.hibernate.TransactionException:JDBC begin Transaction Failed:at Org.hibernate.ejb.AbstractEntityManagerImpl.convert (abstractentitymanagerimpl.java:1387) at Org.hibernate.ejb.AbstractEntityManagerImpl.convert (abstractentitymanagerimpl.java:1310) at Org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException (abstractentitymanagerimpl.java:1397) at Org.hibernate.ejb.TransactionImpl.begin (transactionimpl.java:62) at Org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction (Defaultjpadialect.java:71) at Org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction (hibernatejpadialect.java:60) at Org.springframework.orm.jpa.JpaTransactionManager.doBegin (jpatransactionmanager.java:378) ... morecaused By:org.hibernate.TransactionException:JDBC BEGIN TRANSACTION Failed:at Org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin (jdbctransaction.java:76) at Org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin (abstracttransactionimpl.java:160) at Org.hibernate.internal.SessionImpl.beginTransaction (sessionimpl.java:1426) at Org.hibernate.ejb.TransactionImpl.begin (transactionimpl.java:59) ... morecaused by:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failurethe last Packet  Successfully received from the server was 1,836,166 milliseconds ago. The last packet sent successfully to the server was 29,134 milliseconds ago.at SUN.REFLECT.NATIVECONSTRUCTORACCESSORIMPL.N EwInstance0 (Native Method) at Sun.reflect.NativeConStructoraccessorimpl.newinstance (nativeconstructoraccessorimpl.java:57) at Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (delegatingconstructoraccessorimpl.java:45) at Java.lang.reflect.Constructor.newInstance (constructor.java:526) at Com.mysql.jdbc.Util.handleNewInstance ( util.java:411) at Com.mysql.jdbc.SQLError.createCommunicationsException (sqlerror.java:1117) at Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:3567) at Com.mysql.jdbc.MysqlIO.reuseAndReadPacket ( mysqlio.java:3456) at Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:3997) at Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:2468) at Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2629 ) at Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2713) at Com.mysql.jdbc.ConnectionImpl.setAutoCommit (connectionimpl.java:5060) at Com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit (newproxyconnection.java:881) at Org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin (jdbctransaction.java:72):. morecaused By:java.net.SocketException:Software caused connection abort:recv Failedat Java.net.SocketInputStream.socketRead0 (Native Method) at Java.net.SocketInputStream.read (Socketinputstream.java : @ java.net.SocketInputStream.read (socketinputstream.java:121) at Com.mysql.jdbc.util.ReadAheadInputStream.fill (readaheadinputstream.java:114) at Com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary (readaheadinputstream.java:161) at Com.mysql.jdbc.util.ReadAheadInputStream.read (readaheadinputstream.java:189) at com.mysql.jdbc.MysqlIO.readFully (mysqlio.java:3014) at Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:3467) ... More

Cause Analysis:

MySQLServer the default "Wait_timeout" is 28,800 seconds, or 8 hours, which means that the spare of a connection is assumed to be longer than 8 hours. MySQL will voluntarily disconnect the connection, but the connection pool feels that the connection is still valid (because the validity of the connection is not verified), and when the application requests to use the connection, it causes the error above.

Solution (There are three ways to solve the problem, recommend another)：

1. Add the value of the MySQL Wait_timeout property to change the configuration file My.ini file under the MySQL installation folder (assuming there is no this file,Copy the "My-default.ini" file and generate the "duplicate My-default.ini" file. Rename "Copy My-default.ini" file to "My.ini"), set in the file:

wait_timeout=31536000interactive_timeout=31536000

The default value for these two parameters is 8 hours (60*60*8=28800). Attention: The maximum value of 1.wait_timeout is only 2147483 (24 days or so)2. Changes to the configuration file are available in most articles on the web and can be changed using the MySQL command for both properties

2. Reduce the life cycle of connections in the connection poolreduce the lifetime of connections within the connection pool, make it less thanset in the previous item the value of the Wait_timeout.  Change the C3P0 configuration file.in the Spring configuration file, set:

<bean id= "DataSource"  class= "Com.mchange.v2.c3p0.ComboPooledDataSource" >  <property name= " MaxIdleTime "value=" 1800 "/>  <!--other properties--  </bean>

3. Regular use of connections within the connection poolConnections within the connection pool are used regularly so that they do not get disconnected by MySQL due to idle timeouts.
Change the configuration file for C3p0,in the Spring configuration file, set:

        <bean id= "DataSource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" >  <property name= " Preferredtestquery "value=" Select 1 "/>  <property name=" Idleconnectiontestperiod "value=" 18000 "/>  <property name= "Testconnectiononcheckout" value= "true"/>  </bean>

Knowledge expansionc3p0

C3p0 is an open source JDBC Connection pool that is advertised with hibernate in the Lib folder and contains statement objects for the connection and datasources pools that implement JDBC3 and JDBC2 extension specification descriptions. c3p0 configuration file

<default-config> <!--c3p0 The number of connections fetched at the same time when the connection in the connection pool is exhausted. Default:3--<property name= "Acquireincrement" >3</property> <!--defines the number of times to try repeatedly after a new connection has failed to get from the database. Default:30---<property name= "acquireretryattempts" >30</property> <!--the interval between two connections, in milliseconds.
default:1000---<property name= "Acquireretrydelay" >1000</property> < rollback of all uncommitted operations by default when the connection is closed. Default:false--<property name= "Autocommitonclose" >false</property> <!--c3p0 will build a blank table named Test, and use its own query statement to test. Assuming that the parameter is defined, then the attribute preferredtestquery will be ignored. You can't do anything on this test sheet. It will be used only for C3P0 testing.
default:null--> <property name= "automatictesttable" >Test</property> <!-- Getting a connection failure will cause the thread to wait for the connection pool to get the connection to throw an exception.
The data source is still valid and continues to try to get the connection the next time you call Getconnection (). If set to True, the data source will declare broken and permanently shut down after attempting to acquire a connection failure. default:false--> <property name= "Breakafteracquirefailure" >false</property> <!-- When the connection pool is exhausted, the client calls getconnection () to wait for the new connection, and after the timeout, the SQLException is thrown and, if set to 0, waits indefinitely. Unit milliseconds.
default:0--<property name= "Checkouttimeout" >100</property> <!-- Test the connection by implementing a class of Connectiontester or Queryconnectiontester.
The class name needs to be set to the full path.
default:com.mchange.v2.c3p0.impl.defaultconnectiontester--> <property name= "ConnectionTesterClassName" > </property> <!--Specify the path to the C3P0 libraries. Assuming (usually this is the case) locally you can get so no need to set, the default null can be default:null--> <property name= "Factoryclasslocation" >null</ Property> <!--strongly disrecommended. 　　Setting this to true may leads to subtle and bizarre bugs. Author strongly recommends not to use one of the properties--<property name= "Forceignoreunresolvedtransactions" >false</property> <!- -Check the spare connections in all connection pools every 60 seconds.
default:0---<property name= "Idleconnectiontestperiod" >60</property> <!--get three connections when initializing. The value should be between Minpoolsize and Maxpoolsize. Default:3--<property name= "Initialpoolsize" >3</property> <!--maximum spare time, unused in 60 seconds the connection is discarded.
If 0, it will never be discarded. default:0---<property name= "MaxIdleTime" >60</property> <!--the maximum number of connections left in the connection pool.
Default:15---<property name= "Maxpoolsize" >15</property> <!--JDBC Standard number of references, Used to control the number of preparedstatements loaded in the data source.
But because the pre-cached statements belong to a single connection instead of the entire connection pool.
So there are a number of factors that need to be taken into account in setting this parameter. Assuming that both maxstatements and maxstatementsperconnection are 0, the cache is closed.
default:0--> <property name= "maxstatements" >100</property> <!-- Maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. default:0--<property name= "Maxstatementsperconnection" ></property> <!--c3p0 are asynchronous operations. The slow JDBC operation is completed by the help process.
Extending these operations effectively improves performance by multithreading to implement multiple operations at the same time being run. default:3--> <property name= "Numhelperthreads" >3</property> <!--when user calls getconnection () The root user becomes the user who is going to get the connection. Used primarily when connection pooling is connected to a non-c3p0 data source. default:null--> <property name= "Overridedefaultuser" >root</property> <!-- A parameter that is used in relation to the Overridedefaultuser parameters. default:null--> <property name= "Overridedefaultpassword" >password</property> <!--password. default:null--> <property name= "password" ></property> <!--define a test statement that all connections are running.
This one significantly increases the test speed in the case of a connection test. Note: The test table must exist at the initial data source.
default:null--> <property name= "preferredtestquery" >select ID from test where id=1</property> <!-- The user changes the system configuration parameters to wait up to 300 seconds before running.
default:300--<property name= "propertycycle" >300</property> <!--because of the high performance consumption please use it only when you need it.
The 
 hypothesis is set to true so that every connection commits officer its validity. It is recommended to use methods such as Idleconnectiontestperiod or automatictesttable to improve the performance of the connection test. Default:false--<property name= "Testconnectiononcheckout" >false</property> <!-- If set to true then officer the validity of the connection at the same time that the connection was made. Default:false--<property name= "Testconnectiononcheckin" >true</property> <!--username. 
default:null--> <property name= "user" >root</property> configuration in Hibernate (Spring Administration): <bean id= "Datasou Rce "class=" Com.mchange.v2.c3p0.ComboPooledDataSource "destroy-method=" Close "> <property name=" Driverclass " ><value>oracle.jdbc.driver.OracleDriver</value></property> <property name= "Jdbcurl" > <value>jdbc:oracle:thin: @localhost:1521:test</value></property> <property name= "User" > <value>Kay</value></property> <property name= "password" ><value>root</value> </property> <!--The minimum number of connections that are kept in the connection pool. --<property name= "minpoolsize" value= "/> <!--the maximum number of connections left in the connection pool.
Default:15--<property name= "maxpoolsize" value= "1800"/> <!--maximum spare time, no connection is discarded if not used.
If 0, it will never be discarded.
 Default:0--<property name= "MaxIdleTime" value= "1800"/> <!--when the connection in the connection pool is exhausted c3p0 the number of connections obtained at the same time. Default:3--<property name= "Acquireincrement" value= "3"/> <property name= "maxstatements" value= "1000" /> <property name= "initialpoolsize" value= "ten"/> <!--Check the spare connections in all connection pools every 60 seconds. default:0--<property name= "Idleconnectiontestperiod" value= "$"/> <!--defines the number of times to try repeatedly after a new connection has failed from the database. 
Default:30--<property name= "acquireretryattempts" value= ""/> <property name= "BreakAfterAcquireFailu Re "value=" true "/> <property name=" Testconnectiononcheckout "value=" false "/> </bean> ############## ############# # # C3P0 Connection pool### ########################### #hibernate. C3p0.max_size 2 #hibernate. c3p0.m In_size 2 #hibernate. c3p0.timeout #hibernate c3p0.max_statements #hibernate. C3p0.idle_test_period #h Ibernate.c3p0.acquire_increment 2 #hibernate. C3p0.validate False in the Hibernate.cfg.xml file, add configuration such as the following: <!--maximum number of connections--&G 　　T <property name= "Hibernate.c3p0.max_size" >20</property> <!--minimum number of connections and <property name= "hibernate . c3p0.min_size ">5</property> <!--the time-out for the connection, assuming that it exceeds this time, an exception is thrown. Units in milliseconds--<property name= "Hibernate.c3p0.timeout" >120</property> <!--the maximum number of preparedstatement-- > <property name= "hibernate.c3p0.max_statements" >100</propertY> <!--Check the spare connection in the connection pool every 120 seconds. Units are seconds--<property name= "Hibernate.c3p0.idle_test_period" >120</property> <!--when the connection pool is exhausted, C3P0 get a new number of connections--<property name= "Hibernate.c3p0.acquire_increment" >2</property> <!-- Verify that the connection is available every time-<property name= "Hibernate.c3p0.validate" >true</property>

Workaround for MySQL 8-hour disconnect when using DBCP connection pooling

To change the L configuration file:
Changes such as the following:

<data-sources> <data-source key= "Org.apache.struts.action.DATA_SOURCE" type= "O Rg.apache.commons.dbcp.BasicDataSource "> <set-property property=" driverclassname "value=" Com.mysql.jdbc.Driver "/> <set-property property=" description "value=" WJJG "/> <set-property Pro perty= "url" value= "JDBC:MYSQL://LOCALHOST/WJJG?
useunicode=true&characterencoding=gb2312 "/> <set-property property=" password "value=" 12345678 "/>        <set-property property= "username" value= "wjjg"/> <set-property property= "maxactive" value= "/>" <set-property property= "Maxidle" value= "60000"/> <set-property property= "maxwait" value= "60000"/&        Gt <set-property property= "Defaultautocommit" value= "true"/> <set-property property= "DefaultReadOnly" value= "False"/> <set-property property= "Testonborrow" value= "true"/> <set-property property= "Valida Tionquery "value=" SELECT 1 "/></data-source>

OfTestonborrowAndValidationqueryVery important.

Testonborrow means that when a connection is made from a database connection pool, its validity is checked.

Validationquery is the SQL statement that is used to check, "Select 1" runs faster. is a good test statement.

Recall

When the testers reflect on the problem. Locked the MySQL eight-hour problem very quickly. But the solution has cost me a little kung fu. The first consideration is that it is not reasonable to change the MySQL configuration file. Abandon it.

And then think about it in a servlet wrote a timer, let it check the database every two hours, the execution of a few days to find that the problem still exists, the timer interval changed to 30 minutes, 3 minutes still useless, abnormal as usual, abandoned.

On the Internet to search the solution is also quite a lot. Put

<set-property property= "Testonborrow" value= "true"/>        <set-property property= "Validationquery" value= " Select 1 "/>

Write to the configuration file and execute an error. Say that the bean does not have these two attributes, to look at the original I used the C3P0 connection pool. This workaround is for DBCP connection pooling.

Finally in the configuration file. Joined the

<property name= "MaxIdleTime" value= "1800"/>

Succeeded in overcoming the problem.

Sentiment, the problem is very easy. is to change a configuration file. But the ability to solve this problem with a high speed problem requires a serious attitude.

Using C3P0 with DBCP connection pool, resulting in MySQL 8-hour problem solving method