Mysql automatically disconnects the connection solution after the idle time of MySQL connection exceeds 8 hours

There are three solutions to this problem:

1. Increase the value of the wait_timeout attribute of MySQL.

Modify the/etc/mysql/my. cnf file and set it in [mysqld:

# Set a connection to wait 8 hours in idle status.
Wait_timeout = 86400
Related Parameters, in red
Mysql> show variables like '% timeout % ';
+ -------------------------- + ------- +
| Variable_name | Value |
+ -------------------------- + ------- +
| Connect_timeout | 5 |
| Delayed_insert_timeout | 300 |
| Innodb_lock_wait_timeout | 50 |
| Interactive_timeout | 28800 |
| Net_read_timeout | 30 |
| Net_write_timeout | 60 |
| Slave_net_timeout | 3600 |
| Wait_timeout | 28800 |
+ -------------------------- + ------- +
At the same time, only one of these two parameters works. Which parameter takes effect depends on the connection parameter specified during user connection. wait_timeout is used by default. I suggest modifying both parameters to avoid unnecessary troubles.

The default values of these two parameters are 8 hours (60*60*8 = 28800 ). I tested changing these two parameters to 0, and the result is unexpected. The system automatically sets this value. In other words, you cannot set this value to permanent.
Set these two parameters to 24 hours (60*60*24 = 604800.
Set interactive_timeout = 604800;
Set wait_timeout = 604800;

2. Reduce the connection life cycle in the connection pool so that it is smaller than the value of wait_timeout set in the previous item.
Modify the configuration file of c3p0 and set:

# How long to keep unused connections around (in seconds)
# Note: MySQL times out idle connections after 8 hours (28,800 seconds)
# So ensure this value is below MySQL idle timeout
Cpool. Max idletime = 25200
In the Spring configuration file:
Copy codeThe Code is as follows:
<Bean id = "dataSource"
Class = "com. mchange. v2.c3p0. ComboPooledDataSource">
<Property name = "maxIdleTime" value = "$ {cpool. maxIdleTime}"/>
<! -- Other properties -->
</Bean>


3. regularly use connections in the connection pool so that they will not be disconnected by MySQL due to idle timeout.
Modify the configuration file of c3p0 and set:

# Prevent MySQL raise exception after a long idle timecpool. preferredTestQuery = 'select 1 'cpool. idleConnectionTestPeriod = 18000cpool. testconnectioncheckout = true
Modify the Spring configuration file:
Copy codeThe Code is as follows:
<Bean id = "dataSource" class = "com. mchange. v2.c3p0. ComboPooledDataSource">
<Property name = "preferredTestQuery" value = "$ {cpool. preferredTestQuery}"/>
<Property name = "idleConnectionTestPeriod" value = "$ {cpool. idleConnectionTestPeriod}"/>
<Property name = "testconnectioncheckout" value = "$ {cpool. testConnectionOnCheckout}"/>
<! -- Other properties --> </bean>