Example of how JDBC implements automatic reconnection mechanism in Mysql

I recently discovered a problem at work and finally solved it by searching for relevant information. The following article mainly introduces you to the relevant information about JDBC's implementation of the Mysql automatic reconnection mechanism. The article gives a variety of solutions. , friends in need can refer to it, let’s take a look below.

Preface

This article mainly introduces to you the relevant content about JDBC’s implementation of Mysql automatic reconnection mechanism, and shares it for your reference and study , let’s take a look at the detailed introduction:

Log: using the Connector/J connection property 'autoReconnect=true' to avoid this problem

com.mysql.jdbc.CommunicationsException: The last packet successfully received from the server was58129 seconds ago.The last packet sent successfully to the server was 58129 seconds 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 property 'autoReconnect=true' to avoid this problem.

After checking, it turned out to be a problem with MySQL timeout setting

If the connection is idle for 8 hours (no database operations are performed within 8 hours), mysql will automatically disconnect To connect, you need to restart tomcat.

Solution:

First method: If hibernate is not used, add parameters to the connection url: autoReconnect=true

 jdbc.url=jdbc:mysql://ipaddress:3306/database?autoReconnect=true&autoReconnectForPools=true

Second: If using hibernate, add the following attributes:

 <property name="connection.autoReconnect">true</property>
 <property name="connection.autoReconnectForPools">true</property>
 <property name="connection.is-connection-validation-required">true</property>

Third : If you still use the c3p0 connection pool:

 <property name="hibernate.c3p0.acquire_increment">1</property> 
 <property name="hibernate.c3p0.idle_test_period">0</property> 
 <property name="hibernate.c3p0.timeout">0</property>
 <property name="hibernate.c3p0.validate">true</property>

Fourth: The worst solution

Use Connector/J to connect to the MySQL database, and the program runs After a long time, the following error will be reported:

Communications link failure, The last packet successfully received from the server was *** millisecond ago.The last packet successfully sent to the server was *** millisecond ago.

The error will also prompt you to modify wait_timeout or use the autoReconnect attribute of Connector/J to avoid this error.

After checking some information, I found that there are quite a few people who have encountered this problem. Most of them have this problem when using the connection pool method. It should be difficult to have this problem with short connections.

The reason for this problem:

The default "wait_timeout" of the MySQL server is 28800 seconds or 8 hours, which means that if the idle time of a connection exceeds 8 hours, MySQL will automatically disconnect the connection, but the connection pool thinks that the connection is still valid (because the validity of the connection has not been verified). When the application applies to use the connection, it will cause the above error.

⑴. According to the error prompts, you can use the autoReconnect attribute in the JDBC URL. In the actual test, autoReconnect=true& failOverReadOnly=false was used, but it did not work. 5.1 was used. Version, it may be true that it is only valid for versions before 4 as stated on the Internet.

⑵. There is no other way but to modify the parameters of MySQL. The maximum wait_timeout is 31536000 or 1 year. Add to my.cnf:

[mysqld]

wait_timeout=31536000

interactive_timeout=31536000

For restarting to take effect, these two parameters need to be modified at the same time.

Summarize

The above is the detailed content of Example of how JDBC implements automatic reconnection mechanism in Mysql. For more information, please follow other related articles on the PHP Chinese website!