Connection MySQL Timeout disconnect error problem

  Using the MySQL database, the most annoying problem is the occasional connection error abnormal exception, similar exception as follows (Hibernate for example):
?

1 2 3 4 5 6 7 8 9 ten

Org.hibernate.util.jdbcexceptionreporter-sql error:0, sqlstate:08s01 org.hibernate.util.JDBCExceptionReporter- The last packet successfully received from the server was43200 milliseconds ago. The last packet sent successfully to the server being 43200 milliseconds ago, which is longer than the server configured Val UE of ' wait_timeout '. Your should consider either expiring and/or testing connection validity use in before your, application the increasing Er 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 Org.hibernate.exception.JDBCConnectionException:Could not execute JDBC batch update Com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Connection.close () has ALReady been called. Invalid operation in the state. Org.hibernate.util.jdbcexceptionreporter-sql error:0, sqlstate:08003 org.hibernate.util.jdbcexceptionreporter-no Operations allowed after connection closed. Connection is implicitly closed due to underlying exception/error:   * * BEGIN NESTED Exception * * COM.MYSQL.JDBC.EXC Eptions.jdbc4.CommunicationsException

Most people encounter this problem will be very difficult to understand, I also encountered this problem, after careful study to discover the essential reasons.

I. Causes of the problem

In MySQL's configuration, there is a parameter called "Wait_timeout", which roughly means this: When a client connects to the MySQL database, if the client does not disconnect itself and does nothing, the MySQL database will keep the connection "Wait_ Timeout "So long (unit is S, default is 28800s, that is, 8 hours), after this time, the MySQL database in order to save resources, will disconnect the connection at the database end; Of course, in this" wait_timeout "process, If the client has any action on this connection, the MySQL database will start to compute the time again.

So, the reason for the connection exception exception is because our program and MySQL database connection exceeds the "Wait_timeout" time, the MySQL server to disconnect it, but our program again use this connection without making any judgments, so hang up.

So how do you solve the problem?

Ii. Solutions

1. Extend the value of the Wait_timeout parameter in the MySQL configuration.

I see some people directly extended to a year, also some people say that the value of the maximum is 21 days, even if the value of the larger, MySQL will only identify 21 days (this I did not specifically to the MySQL document to check). But this is a symptom not the root cause of the method, even if can be a year, there will be broken time, the server is to be 7x24 hours online.

2. Before the database operation, a "check" inspection mechanism (that is, check the validity of the connection)

There are many schemes here, hibernate itself has a configuration method, each connection pool (C3P0, etc.) also has the configuration method, here we take c3p0 hibernate configuration as an example.

?

1 2 3 4 5 6 7 8 9 a

<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 in the hibernate version we use, without downloading , direct use of-->     <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, there is an explanation behind-->     <property name= " Hibernate.c3p0.testConnectionOnCheckout ">true</property>     <!--The following are all mapping, Omit--> </session-factory>

The most important of the above configuration is to hibernate.c3p0.testConnectionOnCheckout this property, which ensures that we check that the connection is closed each time we remove the connection. However, this property will have some loss of performance, and can be used in other ways.

In fact, there are many ways to implement the "check" mechanism, we are interested to know more about the relevant knowledge. C3P0 can also use a method such as Select 1 without testconnectiononcheckout.