One time hibernate + c3p0 + mysql Connection pool java.net. SocketException: notes on Connection reset troubleshooting, hibernate. c3p0
Hibernate has its own connection pool, but it is quite a word in use because of its stability and performance. The performance and stability of the c3p0 connection pool have been tested for a long time, so friends who use hibernate generally use the c3p0 connection pool. So is it all right to import the c3p0 package and add the attributes of c3p0 in hibernate. cfg. xml? Not necessarily. It is very likely that after your project goes online, you will find that your connection pool not only has low performance, but also has poor reliability. c3p0 does not show the features it should have in the legend. When cursing the reliability and high performance of c3p0, have you ever thought that what you are using is probably the default connection pool of hibernate, because those configurations of your c3p0 do not work? Have you ever considered how to make the c3p0 configuration in your hibernate configuration take effect? How does one verify that your c3p0 configuration really works? This article will answer these questions in conjunction with the author's experience of troubleshooting the java.net. SocketException: Connection reset fault.
The mysql version used in this article is 5.0, The hibernate version is 3.3.2, And the c3p0 version is 0.9.1.1:
The author's project dao layer combination is hibernate + c3p0 + mysql, And the c3p0 connection pool configuration in hibernate. cfg. xml is 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>
After going online, you may find the following errors 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. $ Proxy23.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. nativeconstruct%cessorimpl. newInstance (nativeconstruct%cessorimpl. java: 57)
At sun. reflect. delegatingconstruct%cessorimpl. newInstance (delegatingconstruct%cessorimpl. 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.exe cSQL (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)
... 12 more
Caused by: java.net. SocketException: Connection reset
At java.net. SocketInputStream. read (fig. java: 196)
At java.net. SocketInputStream. read (fig. 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)
... 20 more
At the beginning, this error was only reported by some scheduled query threads, because it did not affect the business, so I did not care too much.
Later, the project changed. In order to enhance the user experience, some update operations were synchronized and waited, and changed to asynchronous operations. That is, the main thread immediately returned the user results and updated the results.
The response speed was increased, but the update failed occasionally because of the Connection reset fault above. The update failure frequency is too high, it usually occurs once a day (most often the first time in the morning), resulting in customer dissatisfaction.
Although there are a huge number of tasks at hand, the customer is paramount. Higher priority.
The exception information clearly states that the cause of this fault is the timeout and disconnection mechanism of the database server. That is to say, if you do not have any query statements (here, adding, deleting, modifying, or modifying are also in the query scope ), when the database configuration time is exceeded ("wait_timeout" is eight hours by default), the current connection is disconnected. However, the connection pool does not know and considers the current connection to be valid. When a new SQL statement comes in, the discarded connection of the current database is enabled, resulting in the above results. What should we do?
Increase the database timeout and permanent cure;
Adding & autoReconnect = true & failOverReadOnly = false & maxReconnects = 10 to the connection string does not help;
The following idle verification does not work either:
<property name="hibernate.c3p0.idle_test_period">120</property><property name="hibernate.c3p0.validate">true</property><property name="hibernate.c3p0.preferredTestQuery">select 1</property>
Shouldn't c3p0 be so stable? Will the connection pool of c3p0 be useless at all? We began to suspect that the c3p0 configuration was correct. So we used the show processlist command to verify our connection pool. We found that the c3p0 configuration was not used (the minimum number of connections we configured is 5 ):
Why? The hibernate core package includes the following categories:
It can be seen that the original ecology of hibernate supports c3p0. So how can we let hibernate identify these configurations?
View the source code of the org. hibernate. connection. ConnectionProviderFactory class in the hibernate core package. You can see the following annotations:
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. connection. provider_class. if missing, heuristics are used to choose either DriverManagerConnectionProvider, DatasourceConnectionProvider, C3P0ConnectionProvider or DBCPConnectionProvider.
Here, it is clear: if the ConnectionProvider implementation class is not configured, hibernate will find DriverManagerConnectionProvider, DatasourceConnectionProvider, C3P0ConnectionProvider, or DBCPConnectionProvider by default as the implementation of the connection pool. The above log shows that, unfortunately, hibernate did not find C3P0ConnectionProvider, which is looking for DriverManagerConnectionProvider.
Now that we have found the cause of the problem, we can simply add the C3P0ConnectionProvider to the hibernate configuration, so the hibernate connection pool configuration will look like the following:
<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>
Deployment. The world is quiet, and the customer's hurried phone call is gone: This configuration was deployed on the 18 th day of last month until the author posted this blog, and there was no Connection reset fault.
Postscript
In fact, there is another way to verify whether your c3p0 configuration is successful. The above exception information contains this sentence:
At org. hibernate. connection. DriverManagerConnectionProvider. getConnection (DriverManagerConnectionProvider. java: 126)
This also shows that according to the current configuration, hibernate uses the actual hibernate connection pool, not c3p0.
