"First project" MySQL first time the next morning to connect the wrong, solution com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

MySQL first time the next morning connected to the wrong, solution com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure

Com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure Last packet sent to the server was 0 Ms ago



Recently encountered a problem with a MYSQL5 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

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


After some research, we found that many people have encountered similar problems, but the online satisfactory answer is not many. There are a lot of questions on the MySQL website, but there is no right 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 by default 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 |

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

1 row in Set (0.00 sec)


28800 seconds, i.e. 8 hours.

If the database connection (java.sql.Connection) is in a waiting state for wait_timeout seconds, MYSQL5 closes the connection. At this point, your Java app's connection pool still legitimately holds a reference to that 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 there is no way to solve the Tomcat data source configuration? Indeed, in the configuration of the JDBC connection URL, you can attach "autoreconnect=true", but this only works on previous versions of MYSQL5. Adding "validation query" does not seem to be of any avail.

I think the simplest way is the right remedy: Since the problem is caused by the mysql5 of the global variable wait_timeout default value is too small, we will change its size.

View the MYSQL5 's manual and find that the maximum value for Wait_timeout is 24 days/365 days (Windows/linux), respectively. Take windows as an example, suppose we want to set it to 21 days, we just need to modify the MySQL5 profile "My.ini" (mysql5 installation dir), add a line: wait_timeout=1814400

You need to restart MYSQL5.

Linux system configuration file:/etc/my.cnf

The test shows that the problem is resolved.

Workaround:
you can also set directly

MySQL Modify Wait_timeout

MySQL mysql> show global variables like ' wait_timeout ';

Its default value is 8 hours

MySQL will automatically disconnect a connection when it has more than 8 hours of idle time.

　　1. Modify the configuration

Vi/etc/my.cnf

[Mysqld] wait_timeout=10

#/etc/init.d/mysql Restart

　2. Modify directly with SQL command line ( simple method )

Besides Wait_timeout, there's a ' interactive_timeout '

You can also execute show GLOBAL VARIABLES like ' interactive_timeout ' to query

Execute set global interactive_timeout=604800;

"First project" MySQL first time the next morning to connect the wrong, solution com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: