Hibernate has its own connection pool, but everyone uses it with a lot of criticism, because its stability and performance are not very good. C3P0 connection pooling has proven performance and stability, so friends using hibernate generally use the C3P0 connection pool. So is not the c3p0 of the package in, and then hibernate.cfg.xml in the c3p0 of the various attributes added in the all right? Not necessarily, it is likely that your project will be on the line after you find that your connection pool is not only low performance and poor reliability, C3P0 does not show the characteristics it should have in the legend. When you curse the reliability and high performance of C3P0, have you ever thought that you are probably using hibernate as the default connection pool, because the configuration of your c3p0 doesn't work at all? Have you ever thought about how you can configure the hibernate c3p0 configuration to really work? How do you verify that your c3p0 configuration really works? This article combines the experience of a java.net.SocketException:Connection reset fault that the author has personally experienced to answer these questions.
The MySQL version used in this article is 5.0, and the hibernate version is the 3.3.2,C3P0 version is 0.9.1.1:
The author's project DAO Layer combination is hibernate+c3p0+mysql,hibernate.cfg.xml in C3P0 connection pool configuration as follows:
<property name= "hibernate.c3p0.max_size" >10</property><property name= "Hibernate.c3p0.min_size" >5</property><property name= "Hibernate.c3p0.checkoutTimeout" >15000</property><property Name= "hibernate.c3p0.max_statements" >200</property><property name= "Hibernate.c3p0.idle_test_period" >0</property><property name= "Hibernate.c3p0.acquire_increment" >1</property><property Name= "Hibernate.c3p0.validate" >true</property><property name= "Hibernate.c3p0.timeout" >0</ Property>
Once on-line, the following errors are found occasionally:
Org.hibernate.exception.JDBCConnectionException:Cannot Open Connection
At Org.hibernate.exception.SQLStateConverter.convert (sqlstateconverter.java:97)
At Org.hibernate.exception.JDBCExceptionHelper.convert (jdbcexceptionhelper.java:66)
At Org.hibernate.exception.JDBCExceptionHelper.convert (jdbcexceptionhelper.java:52)
At Org.hibernate.jdbc.ConnectionManager.openConnection (connectionmanager.java:449)
At Org.hibernate.jdbc.ConnectionManager.getConnection (connectionmanager.java:167)
At Org.hibernate.jdbc.JDBCContext.connection (jdbccontext.java:142)
At Org.hibernate.transaction.JDBCTransaction.begin (jdbctransaction.java:85)
At Org.hibernate.impl.SessionImpl.beginTransaction (sessionimpl.java:1354)
At Sun.reflect.GeneratedMethodAccessor263.invoke (Unknown Source)
At Sun.reflect.DelegatingMethodAccessorImpl.invoke (delegatingmethodaccessorimpl.java:43)
At Java.lang.reflect.Method.invoke (method.java:606)
At Org.hibernate.context.threadlocalsessioncontext$transactionprotectionwrapper.invoke ( threadlocalsessioncontext.java:342)
At Com.sun.proxy. $Proxy 23.beginTransaction (Unknown Source)
At Com.defonds.dao.PayAntharDao.updateBalance (payanthardao.java:595)
At Com.defonds.interimpl.batchlogadmin$batchupdatethread.run (batchlogadmin.java:49)
At Java.lang.Thread.run (thread.java:745)
caused by:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure
The last packet successfully received from the server was 5,226,622 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
At Sun.reflect.NativeConstructorAccessorImpl.newInstance0 (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:1116)
At Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:3589)
At Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:3478)
At Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:4019)
At Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:2490)
At Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2651)
At Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2677)
At Com.mysql.jdbc.ConnectionImpl.setTransactionIsolation (connectionimpl.java:5315)
At Org.hibernate.connection.DriverManagerConnectionProvider.getConnection (Drivermanagerconnectionprovider.java : 126)
At Org.hibernate.jdbc.ConnectionManager.openConnection (connectionmanager.java:446)
... More
caused by:java.net.SocketException:Connection Reset
At Java.net.SocketInputStream.read (socketinputstream.java:196)
At Java.net.SocketInputStream.read (socketinputstream.java:122)
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:3036)
At Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:3489)
... More
At first it was just a thread of timed queries that exposed the error because it didn't affect the business and so didn't care.
Later project changes, in order to enhance the user experience, some update operations by synchronous waiting, in exchange for asynchronous operations, that is, the main thread immediately return to the user results, and another thread to update.
Response speed is raised, but this update occasionally failed, the reason is that the above Connection reset fault, the update failure frequency is too high, the basic daily (mostly the first time in the morning to appear the highest frequency), causing customer dissatisfaction.
Although there are many tasks at hand, customer first. Priority is pulled higher.
Exception information inside said very clearly, the cause of this failure is the database server timeout mechanism, that is, if you do not have any query statement (here, delete and change also belongs to the scope of the query), more than the database configuration time ("Wait_timeout" default is eight hours), the current connection is disconnected. But the connection pool does not know, also think the current connection is valid, when the new SQL comes over, enable the current database has been obsolete connection, resulting in the above results. What do we do?
Increasing the database time-out symptom does not cure;
The connection string plus &autoreconnect=true&failoverreadonly=false&maxreconnects=10 is useless;
Plus the following idle validations do not work:
<property name= "Hibernate.c3p0.idle_test_period" >120</property><property name= " Hibernate.c3p0.validate ">true</property><property name=" Hibernate.c3p0.preferredTestQuery "> Select 1</property>
The stability of the c3p0 should not be so bad, right? Wouldn't it be useless to c3p0 the connection pool? We started to wonder if our configuration of c3p0 was right. So we use SHOW processlist; The command verifies our connection pool and finds that it does not use the C3P0 configuration (we have a minimum of 5 connections):
Where is the reason? The following classes are available in the Hibernate core package:
It can be seen that hibernate's original ecology supports C3P0. So how do you get hibernate to recognize these configurations?
To view the source code of the Org.hibernate.connection.ConnectionProviderFactory class in the Hibernate core package, you can see the following comments:
instantiates a connection provider given either System properties or a java.util.Properties instance. The Connectionproviderfactory first attempts to find a name of a ConnectionProvider subclass in the property Hibernate.con Nection.provider_class. If missing, heuristics is used to choose either Drivermanagerconnectionprovider, Datasourceconnectionprovider, C3p0connectionprovider or Dbcpconnectionprovider.
It is clear that things are here: if we do not configure the ConnectionProvider class, hibernate defaults to find Drivermanagerconnectionprovider, One of the Datasourceconnectionprovider, C3p0connectionprovider, or dbcpconnectionprovider is an implementation of the connection pool. Through the above log can be seen, unfortunately, hibernate did not go to find c3p0connectionprovider, it is looking for drivermanagerconnectionprovider.
Now that we have found the problem, the next logical point, we just add C3p0connectionprovider to hibernate configuration, so we hibernate connection pool configuration into the following look:
<property name= "Hibernate.connection.provider_class" >org.hibernate.connection.c3p0connectionprovider</ Property><property name= "hibernate.c3p0.max_size" >10</property><property name= " Hibernate.c3p0.min_size ">5</property><property name=" Hibernate.c3p0.checkoutTimeout ">15000</ Property><property name= "hibernate.c3p0.max_statements" >200</property><property name= " Hibernate.c3p0.idle_test_period ">120</property><property name=" Hibernate.c3p0.timeout ">300</ Property><property name= "hibernate.c3p0.acquire_increment" >1</property><property name= " Hibernate.c3p0.validate ">true</property><property name=" Hibernate.c3p0.preferredTestQuery "> Select 1</property>
Deploy it up. The world is quiet, the customer hurried phone voice lost: Last month's 18th deployment on this configuration, until the author sent this blog, never met Connection reset failure.
PostScript
In fact, there is a way to verify that your c3p0 has been successfully configured, the above exception message has this sentence:
At Org.hibernate.connection.DriverManagerConnectionProvider.getConnection ( drivermanagerconnectionprovider.java:126)
This also shows that the configuration was actually hibernate's own connection pool, not c3p0.
One Hibernate+c3p0+mysql connection pool java.net.SocketException:Connection Reset fault Resolution note
