How to solve connection pool timeout disconnection in Mysql

How to solve connection pool timeout disconnection in Mysql
Introduction: I made an activation service last night and then tested it. I tested it again this morning and found that an exception was reported and the database could not be connected.
Let's talk about the general cause of this Exception:
In the MySQL configuration, there is a parameter called "wait_timeout", which roughly means that when a client connects to the MySQL database, if the client is not disconnected by itself, no operation is performed. The MySQL database will keep the connection "wait_timeout" for such a long time (unit: s, default value: 28800 s, that is, 8 hours). After this time, to save resources, the MySQL database will disconnect the connection from the database. Of course, if the client has any operations on the connection, mySQL Databases start to calculate the time again.


In this case, the cause of the above Exception is that the connection between my server and MySQL database exceeds the "wait_timeout" time, And the MySQL server disconnects it, however, my program did not make any judgment when using this connection again, so it went down.
How can this problem be solved?
I checked the information on the Internet. The details are as follows:


The first problem: Our server has considered this issue during the design process. Therefore, the main thread of the server has a regular check mechanism, every 30 minutes, a "select 1" is sent to the database to ensure that the connection is active. Why does this check mechanism not work?
The second problem: we can get the following information from the Exception:
[Java]View plaincopy
The last packet sent successfully to the server was 43200 milliseconds ago, which is longer than the server configured value of 'wait _ timeout '. this information clearly states that the last packet successfully sent to the Server was 43200 milliseconds ago. But 43200 milliseconds is only 43.2 seconds. That is to say, our server passed the message to the MySQL server before 43.2 seconds. How can this problem exceed "wait_timeout? In addition, the configuration of the MySQL database is indeed 28800 seconds (8 hours). What about this?[Html]View plaincopy <session-factory> <property name = "hibernate. dialect"> org. hibernate. dialect. MySQL5InnoDBDialect </property>
<Property name = "hibernate. connection. driver_class"> com. mysql. jdbc. Driver </property> <property name = "hibernate. connection. useUnicode"> true </property>
<Property name = "hibernate. connection. characterEncoding"> UTF-8 </property> <property name = "hibernate. show_ SQL"> true </property>
<! -- The following is all mapping, omitted --> </session-factory>
The update process in the program is roughly as follows:[Java]View plaincopy
Session = org. hibernate. SessionFactory. openSession (); transaction = session. beginTransaction ();
Session. update (something); transaction. commit ();
Session. close (); here, all the connections and closures about the database Connection are in Hibernate, so it is impossible not to mine the source code of Hibernate.[Html]View plaincopy <session-factory> <property name = "hibernate. dialect"> org. hibernate. dialect. MySQL5InnoDBDialect </property>
<Property name = "hibernate. connection. driver_class"> com. mysql. jdbc. Driver </property> <property name = "hibernate. connection. useUnicode"> true </property>
<Property name = "hibernate. connection. characterEncoding"> UTF-8 </property> <property name = "hibernate. show_ SQL"> true </property>
<! -- C3p0 is self-contained in the Hibernate version we use. You do not need to download it. --> <property name = "hibernate. connection. provider_class "> org. hibernate. connection. c3P0ConnectionProvider </property>
<Property name = "hibernate. c3p0. min_size"> 5 </property> <property name = "hibernate. c3p0. max_size"> 20 </property>
<Property name = "hibernate. c3p0. timeout"> 1800 </property> <property name = "hibernate. c3p0. max_statements"> 50 </property>
<! -- The following sentence is very important and will be explained later --> <property name = "hibernate. c3p0. testConnectionOnCheckout"> true </property>
<! -- The following is all mapping, omitted --> </session-factory>

 

The most important attribute in the above configuration is hibernate. c3p0. testconnectioncheckout, which ensures that the connection is closed every time we pull the connection. However, this attribute may cause some performance loss. I can refer to my reference blog to say that the program can be used first, and then its performance (not tolerable ).

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

Now, the problem that troubles me is solved. I hope that the above sorting will leave some ideas for me to encounter similar problems in the future, and provide some help to those who are suffering from this problem.

Finally, add something:

1. Summary of some attributes of c3p0:

Datasource. c3p0. acquireIncrement = 10 when connections in the connection pool are used up, the number of new connections created at one time by C3P0;

Datasource. c3p0. minPoolSize = 50 the minimum number of connections retained in the connection pool. The default value is 15.

Datasource. c3p0. maxPoolSize = 400 maximum number of connections retained in the connection pool. The default value is 15;

Datasource. c3p0. initialPoolSize = 50 the number of connections created during initialization. The value should be between minPoolSize and maxPoolSize. The default value is 3;

Datasource. c3p0. maxIdleTime = 1800 maximum idle time. connections that exceed the idle time will be discarded. 0 or negative. The default value is 0;

Datasource. c3p0. acquireRetryAttempts = 100 defines the number of times that a new connection fails to be retrieved from the database. The default value is 30;

Datasource. c3p0. acquireRetryDelay = 20 the interval between two connections, in milliseconds. The default value is 1000;

Datasource. c3p0. debugUnreturnedConnectionStackTraces = true

Datasource. c3p0. maxStatements = 0JDBC standard parameter, used to control the number of PreparedStatement loaded in the data source. However, the pre-Cache Statement belongs to a single Connection rather than the entire Connection pool. Therefore, you need to consider multiple factors when setting this parameter. If both maxStatements and maxStatementsPerConnection are 0, the cache is disabled. The default value is 0;

Datasource. c3p0. idleConnectionTestPeriod = 1800 check the idle connections in all connection pools every second. The default value is 0, indicating no check;

Datasource. c3p0. breakAfterAcquireFailure = true if the connection fails to be obtained, an exception will be thrown by all threads waiting for the connection to be obtained. However, the data source is still valid, and the next call to getConnection () will continue to try to obtain the connection. If it is set to true, the data source will be declared disconnected and permanently closed after the connection fails to be obtained. The default value is false;

Datasource. c3p0. testConnectionOnCheckout = false because of high performance consumption, please use it only when necessary. If it is set to true, the validity of each connection is verified when it is submitted. We recommend that you use idleConnectionTestPeriod or automaticTestTable

Datasource. c3p0. autoCommitOnClose = true when the connection is closed, all uncommitted operations are rolled back by default. The default value is false;

Datasource. c3p0. maxStatementsPerConnection = 100 maximum number of cached Statement owned by a single connection pool. The default value is 0;

2. In this issue, add the <property name = "hibernate. c3p0. maxIdleTime"> 1800 </property> line, because the above attribute contains.

3, remember to add two jar packages: the version I added was c3p0-0.9.2.1.jar and mchange-commons-java-0.2.3.4.jar

If you have any new questions, please feel free to add them.