Connection to MySql timeout disconnection error _ MySQL

When MySql database is used for connection timeout and disconnection, the most troublesome problem is that a connection error Exception occurs occasionally. Similar exceptions are as follows (Hibernate is used as an example ):

org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01org.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.org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with sessionorg.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch updatecom.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error:** BEGIN NESTED EXCEPTION **com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

Most people will be confused about this problem. I have also encountered this problem. after careful research, I found the underlying cause.

I. cause

In the Mysql configuration, there is a parameter named "wait_timeout", which roughly means that after a client connects to the MySQL database, if the client does not disconnect 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 during this "wait_timeout" process, mySQL databases start to calculate the time again.

In this case, the connection Exception occurs because the connection between our program and MySQL database exceeds the "wait_timeout" time, and the Mysql server disconnects it, however, our program did not make any judgment when using this connection again, so it went down.

So how can we solve this problem?

II. Solution

1. extend the value of wait_timeout in Mysql configuration.

I think some people extend it to a year, and some people say that this value is the maximum value of 21 days, even if the value is set to a larger value, mySQL only recognizes MySQL for 21 days (I did not go to the MySQL document to check it ). However, this is a temporary solution. even if it can be used for one year, it will still take 7x24 hours for the server to be online.

2. perform the "check" check mechanism (that is, check whether the connection is valid) before performing database operations)

There are actually a variety of solutions, Hibernate itself has a configuration method, each connection pool (c3p0, etc.) also has a configuration method, here we take the Hibernate configuration of c3p0 as an example.

     
  
   
Org. hibernate. dialect. MySQL5InnoDBDialect
      
  
   
Com. mysql. jdbc. Driver
      
  
   
True
      
  
   
UTF-8
      
  
   
True
      
      
  
   
Org. hibernate. connection. C3P0ConnectionProvider
      
  
   
5
      
  
   
20
      
  
   
1800
      
  
   
50
      
      
  
   
True
      
  
 

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, and other methods can be used.

There are actually many ways to implement the "check" mechanism. if you are interested, you can learn more about it. C3p0 can also use select 1 and other methods without testconnectioncheckout.