Resolve MySQL 5 database connection timeout problem

Recently encountered a problem with a MYSQL5 database. is a standard SERVLET/TOMCAT network application, the backstage use MySQL database. The problem is standby after one night, the next morning the first login always fails. View Log found the following error:

“com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.”

After some research, found that many people have encountered similar problems, but the online satisfactory answer is not much. There are a lot of questions on the MySQL site, but there is no correct answer; Baidu knows that there is an approximate correct answer. I will now summarize my solution:

The above problem is caused by the configuration of the MYSQL5 database. MySQL5 the Wait Time (wait_timeout) for its connection defaults to 8 hours. In its client program, you can view its values in this way:

mysql﹥

mysql﹥ show global variables like 'wait_timeout';

+---------------+---------+

| Variable_name | Value |

+---------------+---------+

| wait_timeout | 28800 |

+---------------+---------+

1 row in set (0.00 sec)

28800 seconds, which is 8 hours.

If the database connection (java.sql.Connection) is in the waiting state during wait_timeout seconds, MYSQL5 closes the connection. At this point, your Java application's connection pool still legally holds a reference to the connection. This error is encountered when you use this connection for database operations. This explains why my program cannot log on the next day.

You might think that there is no way to solve the data source configuration of Tomcat? Indeed, you can attach "Autoreconnect=true" in the configuration of the JDBC connection URL, but this only works for previous versions of MYSQL5. Adding "validation query" does not seem to help.

I think the simplest way is to prescribe the right remedy: Since the problem is caused by the default value of the MYSQL5 global variable wait_timeout is too small, we will change it to large.

Check the MYSQL5 manual and find that the maximum value for Wait_timeout is 24 days/365 days (Windows/linux) respectively. Take Windows for example, assuming that we want to set it to 21 days, we simply modify the MYSQL5 profile "My.ini" (MySQL5 installation dir) and add one more line: wait_timeout=1814400

A reboot of the MYSQL5 is required.

Linux system configuration file:/etc/my.cnf

The test shows that the problem is resolved.