MySQL database drops connection after 8 hours. How

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?

回答1:

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

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

回答2:

Solution 2 is the least work and probably the most common way to solve this problem.

Log in to your server using SSH.

Edit my.cnf (the MySQL configuration file).

sudo vi /etc/my.cnf

Locate the timeout configuration and adjust it to fit your server.

Set wait_timeout = 86400 = 24hours.