Solve the Problem of MySQL5 database connection timeout

Check the MySQL5 manual and find that the maximum value of wait_timeout is 24 days 365 days (windowsLinux ). Take windows as an example. Suppose we want to set it to 21 days.

Check the MySQL5 manual and find that the maximum value of wait_timeout is 24 days/365 days (windows/Linux ). Take windows as an example. Suppose we want to set it to 21 days.

After logging on to JR today, I found my title was "veteran expert ". Flattered. It only indicates that I have been here for a while. JR has done a good job and provided a platform for communication.

Let's get down to the truth. Recently I encountered a problem with the MySQL5 database. Is a standard servlet/tomcat network application that uses MySQL databases in the background. The problem is that after one night of standby, the first login on the next morning always fails. Check the log and find 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, we found that many people have encountered similar problems, but there are not many satisfactory answers on the Internet. There are also many questions on the MySQL website, but there is no correct answer; Baidu knows that there is an approximate correct answer. The solution is summarized as follows:

The above problem is caused by the configuration of the MySQL5 database. Mysql5 sets the connection wait time (wait_timeout) to 8 hours by default. You can view the value in the client program as follows:

Mysql>

Mysql> show global variables like 'wait _ timeout ';

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

| Variable_name | Value |

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

| Wait_timeout | 28800 |

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

1 row in set (0.00 sec)

28800 seconds, that is, 8 hours.

If the database Connection (java. SQL. Connection) remains in the waiting state during the wait_timeout seconds, mysql5 closes the Connection. At this time, the connection pool of your Java application still legally holds the reference of the connection. When you use this connection for database operations, you will encounter the above error. This explains why my program cannot log on the next day.

You may think of any solution in tomcat data source configuration? Indeed, in the configuration of jdbc connection url, you can attach "autoReconnect = true", but this only applies to versions earlier than MySQL 5. Adding a "validation query" does not seem to help.

I think the simplest way is to remedy the problem: since the problem is caused by the small default value of the global variable wait_timeout of MySQL5, we can just increase it.

Check the MySQL5 manual and find that the maximum value of wait_timeout is 24 days/365 days (windows/Linux ). Take windows as an example. If we want to set it to 21 days, we only need to modify the mysql5 configuration file "my. ini" (mysql5 installation dir) and add a line: wait_timeout = 1814400

You need to restart MySQL 5.

Linux configuration file:/etc/my. cnf

The test shows that the problem is solved.