"Go" MySQL connection time-out disconnection problem

 

This article turns around and makes a note, and sometimes look at it.

Ext.: http://blog.csdn.net/nethibernate/article/details/6658855

Exception as follows:

[HTML]View Plaincopy

2. Org.hibernate.util.jdbcexceptionreporter-sql error:0, Sqlstate:08s01
4. org.hibernate.util.jdbcexceptionreporter - the last packet successfully  Received from the server was43200 milliseconds ago. the last packet sent successfully to the server was 43200  milliseconds ago, which is longer than the server configured  value of  ' Wait_timeout '.  you should 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  ' autoreconnect=true '  to avoid this problem.  
6. Org.hibernate.event.def.abstractflushingeventlistener-could not synchronize database state with session
8. Org.hibernate.exception.JDBCConnectionException:Could not execute JDBC batch update
10. Com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Connection.close () has already been called. Invalid operation in the this state.
12. Org.hibernate.util.jdbcexceptionreporter-sql error:0, sqlstate:08003
14. Org.hibernate.util.jdbcexceptionreporter-no operations allowed after connection closed. Connection is implicitly closed due to underlying exception/error:
18. * * BEGIN NESTED EXCEPTION * *
20. Com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

Let's talk about the general reasons for this exception:

In MySQL configuration, there is a parameter called "Wait_timeout", which roughly means: When a client connects to a MySQL database, if the client does not disconnect itself and does nothing, the MySQL database will keep the connection "Wait_ Timeout "So long (unit is S, the default is 28800s, that is, 8 hours), after this time, the MySQL database in order to save resources, the database will disconnect this connection, of course, in this process, if the client has any action on this connection, The MySQL database will start calculating this time again.

So, the reason for the above exception is because my server and MySQL database connection exceeded the "wait_timeout" time, the MySQL server to disconnect it, but my program again use this connection without making any judgment, so it hangs.

How can we solve this problem?

In the process of thinking about the solution, I found several questions that left me with no clue:

The first problem: Our server has been in the design process to consider this matter, so the main thread of the server has a timed check mechanism, every half hour will send a "Select 1" to the database to ensure that the connection is active, why this check mechanism does not work?

The second question: You can get a message from the exception above:

[Java]View Plaincopy

1. The last packet sent successfully to the server were 43200 milliseconds ago, which is longer than the server configured  Value of ' Wait_timeout '.

The message is clear, the last package that was successfully sent to the server was 43200 milliseconds ago. But 43200 milliseconds only 43.2 seconds, that is to say our server 43.2 seconds before and the MySQL server through the letter, how can happen more than "wait_timeout" problem? And the MySQL database configuration is really 28,800 seconds (8 hours), this is God horse situation?

Google on the internet for a long time, but there are a lot of discussion on this issue, but has not found that I feel effective methods, can only be combined with Google to the results to slowly pondering.

first, there is a single solution to the MySQL database, which is to extend the value of "Wait_timeout". I see some people directly extended to a year, also some people say this value is 21 days, even if the value of the larger, MySQL will only recognize 21 days (this I did not specifically to the MySQL document to check). But this is a palliative approach, even if it can be a year, there will be a break, the server is a 7x24 hour online ah.

Since there is no good way out of the MySQL database, the next step is to do it from the program side.

Let's talk about the approximate structure of the procedure here: two threads, one thread responsible for querying and the check mechanism mentioned above, the other thread is responsible for updating the database regularly, using Hibernate, the configuration is very simple, is the most basic, there is no connection pool and cache configuration, as follows:

[HTML]View Plaincopy

2. <session-factory>
4. <property name="Hibernate.dialect">org.hibernate.dialect.mysql5innodbdialect</ Property >
6. <property name="Hibernate.connection.driver_class">com.mysql.jdbc.driver</ Property>
8. < name="Hibernate.connection.useUnicode">true</ Property >
10. < name="hibernate.connection.characterEncoding">utf-8</Property >
12. < name="Hibernate.show_sql">true </Property>
14. <!--The following is all mapping, omitted--
16. </session-factory>

The process of updating in a program is roughly the same: [Java]View Plaincopy

1. Session = Org.hibernate.SessionFactory.openSession ();
2. Transaction = Session.begintransaction ();
3. Session.update (something);
4. Transaction.commit ();
5. Session.close ();

Here, all the connections and shutdowns of database connection are in Hibernate, so it is impossible to not dig into Hibernate's source code.

Before digging into Hibernate source code, you must have a clear goal: what to dig?

In fact, my goal is very clear, since the disconnect is the MySQL database, then the problem with our program side is that we do not call Connection.close after using the connection (), will retain a long connection there. So, when does hibernate start this connection, and when does it call Connection.close ()?

The next step is hibernate source mining ...

The boring process does not say, say the digging out of something:

Hibernate (forgot to say, We use the Hibernate version is 3.3.2) in the above configuration, there will be a default connection pool, the name is: Drivermanagerconnectionprovider; This is an extremely simple connection pool, which, by default, retains 20 connections in the pool, which are not the first Hiber When Nate initializes it, it creates it when you need to use it, and then joins it to the pool after it's done. Here is a method called CloseConnection (Connection conn), this method is NB, it directly to the incoming connection does not do any processing, put in the pool. And this kind of internal connection pool is actually a ArrayList, each time you get rid of the first connection ArrayList, use the Add method to add to the end of the ArrayList.

When our program is updated, hibernate will get a connection connection through Drivermanagerconnectionprovider, and when it is finished, call Session.close () Hibernate calls Drivermanagerconnectionprovider's CloseConnection method (which is the NB method mentioned above), this time, The connection is placed directly into the Drivermanagerconnectionprovider ArrayList, and there is no place to call the Close method of connection from beginning to end.

When it comes to this, the problem is obvious.

First, the check mechanism of our "SELECT 1" and the updated logic in our server program are two threads, and when the check mechanism is working, it gets a connection to Drivermanagerconnectionprovider, and when the logic is updated, It will get another connection to the Drivermanagerconnectionprovider, and after the two logic is done, it will be put back into the Drivermanagerconnectionprovider pool and placed at the end of the pool. In this way, the check mechanism again want to check the two connections need luck, because the update logic after the connection back to the pool, and update logic is timed, check mechanism is also timed, two timing mechanism if you can always stagger, Then the check mechanism check is always one of the two connections, the other one is in trouble. This is why the check mechanism is not so.

Second, the 43200-millisecond question about the exception message is clear, the check mechanism check is always a connection, and another expired connection is removed by the update thread, and after the check mechanism, there is not a long time for the update to occur. 43200 milliseconds is probably the interval between them.

Here's a clear analysis of the problem, how to solve it?

The easiest solution to think about, and the most online solution, is to extend the MySQL end of "wait_timeout" time. I said, the symptoms do not cure, I feel uncomfortable, no.

The second to see the most is the "AutoReConnect = True" This program, depressed is the MySQL 5 after the database to go to the function, said there will be side effects (also did not specifically say what side effects, I do not bother to check), we use the hibernate 3.3.2 This version also does not have autoreconnect this function.

The third said the most is the use of C3P0 pool, and Hibernate official document also mentions that the default connection pool is very excrement, only for testing use, recommended to use C3P0 (let me depressed is I even c3p0 of the official website have not found, only on SourceForge have a project homepage). Well, I decided to use C3P0 to fix the problem.

Solve this exception problem with C3P0: (click here to enter my reference blog, to Fq Oh, pro!) ）

First of all, it is clear that if it is a pool it must have this problem, unless the connection is closed before it is put into the pool, the pool is also a fart. So I refer to the blog that the best way is to check the connection, to see if the connection is still valid, that is, whether the connection has been turned off by the MySQL database, if it is closed to reconnect one. So, following this idea, I fixed the hibernate configuration file, and the problem was resolved:

[HTML]View Plaincopy

2. <session-factory>
4. <property name="Hibernate.dialect">org.hibernate.dialect.mysql5innodbdialect< /Property>
6. <property name="Hibernate.connection.driver_class">com.mysql.jdbc.driver</ Property>
8. < name="Hibernate.connection.useUnicode">true</ Property >
10. < name="hibernate.connection.characterEncoding">utf-8</Property >
12. < name="Hibernate.show_sql">true </Property>
14. <!--c3p0 in the hibernate version we use, without downloading, directly using --
16. <property name="Hibernate.connection.provider_class"> Org.hibernate.connection.C3P0ConnectionProvider</Property>
18. < name="hibernate.c3p0.min_size">5 </Property>
20. < name="hibernate.c3p0.max_size">20 </Property>
22. < name="hibernate.c3p0.timeout">1800 </Property>
24. < name="hibernate.c3p0.max_statements">50 </Property>
26. <property name= "Hibernate.c3p0.maxIdelTime" >1800</property>
28. <!--The following sentence is very important, there is an explanation--
30. <property name="hibernate.c3p0.testConnectionOnCheckout">true</ Property>
32. <!--The following is all mapping, omitted--
34. </session-factory>

The most important thing in the above configuration is the Hibernate.c3p0.testConnectionOnCheckout property, which ensures that each time we remove the connection, we check that the connection is closed. However, this property has some loss of performance, referring to the blog I refer to the words: The program can be used first, then it is the performance (not intolerable).

Of course, c3p0 comes with a check mechanism similar to select 1, but as I said, the problem is not solved unless you have a good grasp of the check mechanism interval.

Well, at this point, the problem that bothers me is solved. I hope the above arrangement can leave a thought for me to encounter similar problems later, and can provide a glimmer of help to those who are being troubled by this problem.

2. Using DBCP data source

The default maximum idle time for MySQL is 8 hours, so just set the minevictableidletimemillis to less than this value. For example, configure every 10 minutes to check for connections over idle one hours

<property name= "Minevictableidletimemillis" ><value>3600000</value></property>< Property Name= "Timebetweenevictionrunsmillis" ><value>600000</value></property>

3. Proxool Data source

Database connection pool Proxool, which has two properties: one is Test-before-use, and the other is Test-after-use, both before and after use to check the connection, if the connection is invalid, throw away and create a new connection

4.

Testconnectiononcheckout: Please use it only when you need it because of high performance consumption. If set to true then the validity of each connection submission is officer. It is recommended to use Idleconnectiontestperiod or automatictesttable
and other methods to improve the performance of the connection test. The default is false;

(1) Idleconnectiontestperiod

When the database restarts or, for some reason, the process is killed, C3P0 does not automatically reinitialize the database connection pool, and when the new request needs to access the database, it will report an error (because the connection is invalidated), flush the database connection pool, discard the failed connection, and return to normal when the second request arrives.

C3P0 currently does not provide a parameter that gets the number of retries after a failed connection has been established, only the parameter that gets the number of retries after the new connection failed (acquireretryattempts "defaults to 30" ).

To resolve this issue, you can set the idleconnectiontestperiod "default to 0, which means no check " parameter tradeoff, which is set to automatically check the connection pool connection is a normal frequency parameter, the time unit is seconds.

(2)acquireincrement

When the connection in the connection pool runs out, c3p0 the number of connections that are fetched at the same time, that is, if the number of connections used has reached maxpoolsize,c3p0, a new connection is established immediately.

(3)MaxIdleTime

In addition, C3P0 by default will not close the unused connection pool, but will be recycled to the available connection pool, which will result in increasing the number of connections, so you need to set MaxIdleTime "default 0, means never expire", the unit is seconds, MaxIdleTime represents the maximum time that an idle connection can survive.

"Go" MySQL connection time-out disconnection problem