MySQL Database wait_timeout wait time

recently encountered a MYSQL5 The problem with the database. is a standard servlet/tomcat Network application that uses mysql database in the background . The problem is that after a night of standby, the first log on the next morning always fails. The view log found the following error:

" com.mysql.jdbc.exceptions.jdbc4.communicationsexception: Communications link failure lastpacket sent to the server was 0 ms ago. "

After some research, we find that many people have encountered similar problems, But there are not many satisfactory answers online. mysql There are a lot of questions on the website, but there is no correct answer; Baidu knows there is an approximate right answer. Now I will summarize the solution:

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

Mysql﹥ Mysql﹥show Global variables like ' wait_timeout '; +---------------+---------+ | variable_name | Value | +---------------+---------+ |wait_timeout | 28800 | +---------------+---------+ 1row in Set (0.00 sec)

28800seconds 8 hours.

wait_timeout Span style= "" > < Span style= "font-size:14px" > (java.sql.Connection) has been in the waiting state, mysql5 Java The connection pool applied is still legally holding a reference to the connection. This error is encountered when the connection is used for database operations. This explains why my program cannot log on the next day.

You might think that in Tomcat is there a way to resolve the data source configuration? Indeed, in the jdbc connection URL in the configuration, you can attach "autoreconnect=true" , but this is only for mysql5 the previous version worked. Add "validationquery" seemed to be of no avail.

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

View mysql5 The Handbook, found on wait_timeout The maximum value for each Tian /365 days (windows/linux) . With windows As an example, suppose we want to set it to God, we just need to change mysql5 . configuration File "my.ini" (mysql5installation dir) , add one line:wait_timeout=1814400

need to restart mysql5

Linux System configuration file:/etc/my.cnf

MySQL Database wait_timeout wait time