Connection to MySql timeout disconnection error _ MySQL

Source: Internet
Author: User
Tags sql error
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 - 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 - 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
Org. hibernate. connection. C3P0ConnectionProvider

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.