MySQL connection time-out issue

Using hibernate + MySQL database development, link timeout issues:

Com.mysql.jdbc.CommunicationsException:The last packet successfully received from the server was58129 seconds ago. The last packet sent successfully to the server were 58129 seconds ago, which is longer than the server configured value of ' Wait_timeout '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er configured values for client timeouts, or using the Connector/j Connection property ' Autoreconnect=true ' to avoid this Problem.

Checked, it turned out to be a MySQL timeout setting problem
If the connection is idle for 8 hours (no database operation within 8 hours), MySQL will automatically disconnect and restart Tomcat.

Workaround:

A. If you do not use Hibernate, add the parameter in the connection URL: autoreconnect=true

Jdbc.url=jdbc:mysql://ipaddress:3306/database?autoreconnect=true&autoreconnectforpools=true
Two kinds. With Hibernate, add the following attributes:
<property name= "Connection.autoreconnect" >true</property>
<property name= "Connection.autoreconnectforpools" >true</property>
<property name= "connection.is-connection-validation-required" >true</property>
Three. If you also use the C3P0 connection pool:
<property name= "Hibernate.c3p0.acquire_increment" >1</property>
<property name= "Hibernate.c3p0.idle_test_period" >0</property>
<property name= "Hibernate.c3p0.timeout" >0</property>
<property name= "Hibernate.c3p0.validate" >true</property>

Four. The most difficult solution

Using connector/j to connect to the MySQL database, the program will report the following error when it is running for a long time:

Communications Link Failure,the Last packet successfully received by the server was * * * millisecond ago. The last packet successfully sent to the server was * * * * millisecond ago.

The error will also prompt you to modify Wait_timeout or use Connector/j's AutoReConnect property to avoid the error.

Later looked up some information, only to find that the problem encountered a lot of people, most of them are using the connection pool mode when this problem occurs, short connection should be difficult to appear this problem. The cause of this problem:

MySQL server default "Wait_timeout" is 28,800 seconds, or 8 hours, means that if a connection is idle for more than 8 hours, MySQL will automatically disconnect the connection, and the connection pool that the connection is still valid (because the validity of the connection is not verified), When the application uses the connection, it causes the error above.

1. Follow the error prompts, you can use the AutoReConnect property in the JDBC URL, the actual test using the autoreconnect=true& Failoverreadonly=false, but does not work, Using the 5.1 version, it may really be as valid as what is said on the Internet only for versions prior to 4.

2. No way, can only modify the parameters of MySQL, wait_timeout maximum of 31536000 is 1 years, add in MY.CNF:

[Mysqld]

wait_timeout=31536000

interactive_timeout=31536000

Restart takes effect, both parameters need to be modified at the same time.

* * BEGIN NESTED EXCEPTION * *

Com.mysql.jdbc.CommunicationsException
Message:communications link failure due to underlying exception:

* * BEGIN NESTED EXCEPTION * *

Java.io.EOFException

STACKTRACE:

Java.io.EOFException
At Com.mysql.jdbc.MysqlIO.readFully (mysqlio.java:1913)
At Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:2304)
At Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:2803)
At Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:1573)
At Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:1665)
At Com.mysql.jdbc.Connection.execSQL (connection.java:3176)
At Com.mysql.jdbc.PreparedStatement.executeInternal (preparedstatement.java:1153)

MySQL execution timeout setting MySQL execution timeout
Mysql> Show variables like '%timeout ';
mysql> set wait_timeout = 28800000;
mysql> set interactive_timeout = 28800000; The modifications are as follows: Open/etc/my.cnf, and add the following parameters under attribute group mysqld:
[Mysqld]
interactive_timeout=28800000
wait_timeout=28800000
Added under Windows in the My.ini article:

interactive_timeout=28800000
wait_timeout=28800000

Resolves a MySQL 5 database connection timeout problem that recently encountered 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) for 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=18144006
You need to restart MYSQL5.
Linux system configuration file:/etc/my.cnf
The test shows that the problem is resolved.

-------------------------------------------

[Mysqld]

wait_timeout=2147483
interactive_timeout=2147483

MySQL connection time-out issue