Auto reconnect error of mysql5

Http://www.linuxdiyf.com/viewarticle.php? Id = 89413

 

 

Recently, during the development of a J2EE project, we encountered the following problems:

After the web system is deployed on the server, access to the system is normal. When you add the server time (for example, 2008-03-31) to one or more days (for example, 2008-04-01, 2008-04-02...) and then access the web system, the following exception is reported:

Quote:

Com. MySQL. JDBC. communicationsexception: communications link failure due to underlying exception: ** Begin nested exception ** Java. Io. eofexception Message: can not read response from server. expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. Stacktrace: Java. Io. eofexception: can not read response from server. expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. At com. MySQL. JDBC. mysqlio. readfully (mysqlio. Java: 1997) At com. MySQL. JDBC. mysqlio. reuseandreadpacket (mysqlio. Java: 2411) At com. MySQL. JDBC. mysqlio. checkerrorpacket (mysqlio. Java: 2916) At com. MySQL. JDBC. mysqlio. sendcommand (mysqlio. Java: 1631) At com. MySQL. JDBC. mysqlio. sqlquerydirect (mysqlio. Java: 1723) At com.mysql.jdbc.connection.exe csql (connection. Java: 3250) At com. MySQL. JDBC. Connection. setautocommit (connection. Java: 5395) ...

We first determine that the connection pool is faulty, that is, when the system time changes, the database connection times out, so we need to reconnect or completely recreate the connection pool. We switched to the database. If the Oracle database is used, the same modification will not cause problems, the system runs normally, and the exception will not occur. This shows that this exception is related to MySQL. It may be related to MySQL running parameters (timeout) after reading the relevant information. We will analyze some MySQL running parameters following this idea.

Run show variables like '% timeout %' in MySQL command line client, as shown in:

In the figure, we can see two variables wait_timeout and interactive-Timeout. Their default values are 28800 seconds, that is, 8 hours. That is to say, by default, MySQL will automatically close the opened connection after eight hours (28800 seconds) are not used.

To solve this problem, for versions earlier than mysql5, such as mysql4.x, you only need to modify the URL in the connection pool configuration and add a parameter: autoreconnect = true. For versions earlier than mysql5, you need to modify my. CNF (or my. INI) file, add it after [mysqld:

Wait_timeout = N

Interactive-Timeout = N

N indicates the number of seconds before the server closes the interactive connection.

However, it is difficult to modify my. ini every time for deployment, and what is equal to N is suitable? So this solution is not good.

How can we better solve this problem? After careful analysis, the reason is roughly as follows: after the system date is modified, MySQL automatically closes the opened connection. You can also consider the database connection pool (DBCP) as active, if there is a request (to perform read/write operations on the database), the connection pool uses a connection to operate the database, and this connection does not exist in the MySQL connection pool, so the above exception occurs. This problem does not occur if a connection is checked when it is connected to MySQL.

At this time, our colleague remembered a database connection pool proxool that he had previously used. It has two attributes: Test-before-use and test-after-use, these two attributes are used to check the connection before and after use. If the connection is invalid, discard and create a new connection. Their explanations are as follows:

Test-before-use:

If you set this to true then each connection is tested (with whatever is defined in house-keeping-test-SQL) before being served. if a connection fails then it is discarded and another one is picked. if all connections fail a new one is built. if that one fails then you get an sqlexception saying so.

Test-after-use:

If you set this to true then each connection is tested (with whatever is defined in house-keeping-test-SQL) after it is closed (that is, returned to the connection pool ). if a connection fails then it is discarded.

So we put on the proxool connection pool in the project, run and access the system, change the system time, and then access it. Everything is OK, and the problem is solved!

In addition, the number of connections, the number of activities, and the specific situation of each connection in the proxool connection pool are clearly displayed, as shown in:

Through the test and analysis above, it is more appropriate to select the proxool database connection pool for MySQL 5 database.

The connection pool of c3p0 has the idleconnectiontestperiod attribute. After a period of time, the connection pool can be automatically tested and executed to keep the connection open, and the effect is the same.