Hibernate + MySQL database problems (hibernate database connection timeout settings autoreconnect = true)

Using hibernate + MySQL for database development, connection Timeout:

Com. mySQL. JDBC. communicationsexception: The last packet successfully received ed 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 shoshould 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.

 

I checked it. It turned out to be a problem with MySQL timeout settings.
If the connection is idle for 8 hours (no database operation is performed within 8 hours), MySQL will automatically disconnect and restart tomcat.

 

 

Solution:

 

 

One. If Hibernate is not needed, add the parameter in the connection URL:
Autoreconnect = true

JDBC. url = JDBC: mysql: // IPaddress: 3306/database? Autoreconnect = true & amp; autoreconnectforpools = true

 

Two. If Hibernate is used, 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>

 

3. 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>

 

 

4. The worst solution

 

When you use connector/J to connect to the MySQL database, the following error will be reported after the program runs for a long time:

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 ctor/J to avoid this error.

Later, I checked some information and found that there were quite a few people who encountered this problem. Most of them encountered this problem only when using the connection pool method. Short connections should be difficult to see this problem. Cause:

The default "wait_timeout" of the MySQL server is 28800 seconds, that is, 8 hours. This means that if the idle time of a connection exceeds 8 hours, MySQL will automatically disconnect the connection, the connection pool considers the connection to be valid (because the connection is not verified). When the application applies to use the connection, the above error is reported.

1. according to the error message, you can use the autoreconnect attribute in the jdbc url. In the actual test, autoreconnect = true & failoverreadonly = false is used, but it does not work. Version 5.1 is used, it may be effective only for versions earlier than 4 As mentioned on the Internet.

2. No way. You can only modify MySQL parameters. The maximum value of wait_timeout is 31536000, that is, 1 year. Add the following to my. CNF:

[Mysqld]

Wait_timeout = 31536000

Interactive_timeout = 31536000

The restart takes effect. You need to modify these two parameters at the same time.