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 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 works, is related to the connection parameters specified when the user connects, and by default is the use of wait_timeout. I suggest that both of these parameters be modified so as not to cause unnecessary trouble.
The default values for these two parameters are 8 hours (60*60*8=28800). I tested the two parameters to 0, the result is unexpected, 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, so that they are less than the Wait_timeout values set in the previous entry.
To modify the C3P0 configuration file, set:
# How long to keep unused connections around (in seconds)
# note:mysql Idle connections after 8hours (28,800seconds)
# So ensure this value is below MySQL idle timeout
cpool.maxidletime=25200
In the configuration file for Spring:
Copy Code code as follows:
<bean id= "DataSource"
class= "Com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name= "MaxIdleTime" value= "${cpool.maxidletime}"/>
<!--other properties-->
</bean>
3. Use connections within the connection pool on a regular basis so that they are not disconnected by MySQL because of idle timeout.
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 configuration file for Spring:
Copy Code code 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>
