MySQL Idle 8-hour problem

When you configure a data source in spring, you must set the destroy-method= "close" property so that the data source shuts down gracefully when the spring container is closed.

If the database is MySQL, a classic "8-hour problem" can occur if the data source is improperly configured. The reason is that MySQL automatically shuts down this connection on the database side if it finds a connection that has been idle for more than 8 hours by default. The data source does not know that the connection has been closed by the database, and when he gives the "idle" Connection to DAO, DAO will not be able to get the connection exception.

If you use the DBCP default configuration, the Testonborrow property defaults to True, and the data source detects if the connection is normal before the connection is given to DAO, and if there is a problem, it will take another connection, so there will be no 8-hour problem. If the validity of the connection is detected each time it is handed to the DAO, it can cause performance problems when the concurrency is high, because this results in more database access requests.

Another way is to set Testonborrow to False, set Testwhileidle to True, and then set the Timebetweenevictionrunsmillis value. In this way, DBCP will detect idle connections through a background thread, and will erase them when they find useless idle connections. As long as the timebetweenevictionrunsmillis is set to less than 8 hours, those idle connections that are closed by MySQL can be purged, thus avoiding the "8-hour problem".

You can also set the interactive-timeout configuration parameters for MySQL itself to change the idle connection expiration time. When setting up Timebetweenevictionrunsmillis, you need to know the maximum idle connection expiration time for MySQL

MySQL Idle 8-hour problem