mysql多久清理连接,MySQL数据库在8小时后丢弃连接。怎么预防呢？

We have an application written in Java 8 using Spring and Hibernate.

We are using the MySQL database on both prod and dev environments. The database is used once a day, a process is starting at 11pm. There are days when nobody is even using the prod database, so after 8 hours of inactivity MySQL database is dropping the connection. When the process starts, we get an error:

Exception occured: org.hibernate.exception.GenericJDBCException: Cannot release connection

Then, when someone starts the process manually after the initial failure - everything goes OK.

It seems that the first connection (automatic) attempt is somehow triggering the MySQL database to stand up, and the second (manual) attempt has no problems cause the MySQL database is up and running...

We have 3 ideas:

In the code of our app, use a trigger that will attempt to connect to the database around 10-15 minutes before the process starts (but it still throw this exception, which is not nice)

Set the 8-hour idle timer on MySQL to 24 hours or more

Configure the JDBC driver in spring-database.xml in a manner that it will somehow "ping" the database every 4 hours to keep it alive, but we still don't know how to do it properly...

Which idea (of those 3) seems to be best? We personally think that the 3rd solution is the best... but how to configure the JDBC driver to somehow "ping" the database every 4 hours or so? Or maybe someone of You will have another better idea?

Great thanks in advance

Which idea is the best?

解决方案

You can use autoReconnect parameter in jdbc url to fix this issue.For example:

MYSQL_URL=jdbc:mysql://10.*.*.0:3306/ABC?autoReconnect=true