I. Exception information and workarounds
Com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link Failure Org.springframework.transaction.CannotCreateTransactionException:Could not open JDBC Connection for transaction; Nested exception iscom.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failurethe last Packe T successfully received from the server was 6,388 milliseconds ago. The last packet sent successfully to the server was 1,504 milliseconds ago.at org.springframework.jdbc.datasource.DataSour Cetransactionmanager.dobegin (datasourcetransactionmanager.java:240)
Exception Analysis: The program and MySQL communication failed, that is, the connection failed.
After the program opened the database connection, wait until the database operation, found that the connection was MySQL shut down. On the MySQL level, the MySQL5 configuration defaults to a connection waiting time (wait_timeout) of 8 hours. Connecting for more than 8 hours will cause MySQL to think that the connection timeout is invalid and then shut down.
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.
workaround (first attempt of the third scenario):
(1) in the configuration of the JDBC connection URL, you can attach "autoreconnect=true", but this only works on previous versions of MYSQL5.
(2) Since the problem is caused by the fact that the default value of the MYSQL5 global variable wait_timeout is too small, let's change it to a larger size.
View 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
(3) We can open the Validatequery, Testonborrow (Testonreturn) of the database connection pool so that Each time it is removed from the connection pool and ready to be used (or used and before it is put into the connection pool) the current use is destory, and if not, the system will automatically be dropped. The
or Testwhileidle item is a setting that allows a background thread to periodically check the availability of connections in the connection pool.
MySQL exception "Database Disconnected": Communications link failure