MySQL automatically disconnects the connection solution after more than 8 hours of idle time

Add the Settings node Connectionlifetime (in seconds) to the connection string. Exceeding the set connection session will be killed!

Connection Lifetime, connectionlifetime

0

When a connection was returned to the pool, its creation time was compared with the current time, and the connection was dest royed If, time span (in seconds) exceeds the value specified by Connection Lifetime. This was useful in clustered configurations to force load balancing between a running server and a server just brought Onli Ne. A value of zero (0) causes pooled connections to the maximum connection timeout.

     Public BOOL connectionlifetimeexpired ()    {      = DateTime.Now.Subtract (creationtime);       if 0 &&        > settings.connectionlifetime)        return  True;       return false ;    }

MySQL's default setting, when a connection is idle for more than 8 hours, MySQL disconnects the connection, and the C3P0 connection pool thinks the disconnected connection is still valid. In this case, if the client code requests a connection to the C3P0 connection pool, the connection pool will return the failed connection to the client, and the client throws an exception when using the failed connection

There are three ways to solve this problem:

1. Increase the value of the MySQL Wait_timeout property.

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

# Set A connection to wait for 8hours in idle status.
Wait_timeout =86400
Related parameters, red part
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 is in effect, related to the connection parameter specified when the user connects, by default, Wait_timeout is used. I recommend that you modify both parameters to avoid causing unnecessary trouble.

The default value for these two parameters is 8 hours (60*60*8=28800). I tested to change these two parameters to 0, the result is unexpectedly, the system automatically set this value to. In other words, the value cannot be set to permanent.
Set these 2 parameters to 24 hours (60*60*24=604800).
Set interactive_timeout=604800;
Set wait_timeout=604800;

2. Reduce the lifetime of connections within the connection pool to less than the Wait_timeout value set in the previous item.
To modify the C3P0 configuration file, set:

# How long-keep unused connections around (in seconds)
# Note:mysql times out idle connections after 8hours (28,800seconds)
# So ensure this value is below MySQL idle timeout
cpool.maxidletime=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. Use the connections in the connection pool on a regular basis so that they do not get disconnected by MySQL due to idle timeouts.
To modify the C3P0 configuration file, set:

# Prevent MySQL Raise exception after a long idle timecpool.preferredtestquery= ' SELECT 1 ' cpool.idleconnectiontestperiod= 18000cpool.testconnectiononcheckout=true
To 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= "Testconnectiononcheckout" value= "${cpool.testconnectiononcheckout}"/>
<!--other properties--></bean>

MySQL automatically disconnects the connection solution after more than 8 hours of idle time