One Hibernate+c3p0+mysql connection pool java.net.SocketException:Connection Reset fault Resolution note

Source: Internet
Author: User
Tags connection pooling mysql connection pool mysql version connection reset

Hibernate has its own connection pool. But everyone used it with a lot of criticism, because of its stability and performance is 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, very likely after your project is online. Discovering that your connection pool is not only low performance and poor reliability, C3P0 does not show the features it should have in its legends. When you curse the reliability and performance of C3P0, have you ever wondered if you are using the default connection pool for Hibernate, because the configuration of your c3p0 doesn't work at all? Have you ever thought about it? How do you configure hibernate in C3P0 configuration to really play a role? 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 number used in this article is 5.0. The hibernate version number is 3.3.2. The C3P0 version number is 0.9.1.1:

The author's project DAO Layer combination is hibernate+c3p0+mysql,hibernate.cfg.xml in C3P0 connection pool configuration such as the following:

<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>

On the go, we find that there are occasional errors:
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 only some of the threads of timed queries exposed this error because there was no impact on the business. So I didn't care too much.


Later the project changes, in order to enhance the user experience, some update operations are synchronized waiting, in exchange for asynchronous operations. That is, the main thread returns to the user results immediately, and the other threads are updated.


The response speed was raised, but the update occasionally failed. The reason is that the Connection reset fault, this update failure frequency is too high, basically once a day (mostly the first time in the morning to appear the highest frequency). Cause customer dissatisfaction.


Despite the huge number of tasks at hand, the customer is supreme. Priority is pulled higher.

The exception information is very clear inside. The cause of this failure is the time-out mechanism of the database server, which means that you do not have any query statements (here. Additions and deletions also belong to the scope of the query, more than the time of the database configuration ("Wait_timeout" default feeling eight hours) after. Disconnect the current connection. But the connection pool does not know, also feel that the current connection is valid, when there is new SQL come over. Enables the connection that is already obsolete for the current database, 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;
Add the following spare validation also does 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 suspect that our configuration of c3p0 was incorrect. So we use SHOW processlist; The command verifies our connection pool and discovers that there is no practical c3p0 configuration (we have a minimum of 5 connections):

Where is the reason? The following classes are available in the Hibernate core pack:

It can be seen that hibernate's original ecology supports C3P0. So how can you let hibernate recognize these configurations?
View the source code for the Org.hibernate.connection.ConnectionProviderFactory class in the Hibernate core package. Can see the following gaze:
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.
Here's the thing. Already very clear: suppose we do not configure ConnectionProvider implementation class, hibernate default to find Drivermanagerconnectionprovider, An implementation of Datasourceconnectionprovider, C3p0connectionprovider, or Dbcpconnectionprovider as a connection pool. It can be seen through the logs above. Unfortunately, hibernate did not go to C3p0connectionprovider, and it was looking for drivermanagerconnectionprovider.


Now that we have found the problem, the next thing is logical, we just need to 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 was quiet and the customer was in a hurry: This configuration was deployed on the 18th of last month. Until the author sends this blog post. Never met Connection reset fault.


Postscript
In fact, another way to verify that your C3P0 has been configured successfully. There is this sentence in the above exception message:
At Org.hibernate.connection.DriverManagerConnectionProvider.getConnection (Drivermanagerconnectionprovider.java : 126)
This also shows the configuration according to the time. Hibernate actually uses Hibernate's own connection pool. It's not c3p0.

One Hibernate+c3p0+mysql connection pool java.net.SocketException:Connection Reset fault Resolution note

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.