MySQL数据库会在8小时后断开连接.怎么预防呢? [英] MySQL database drops connection after 8 hours. How to prevent it?

问题描述

我们有一个使用Spring和Hibernate用Java 8编写的应用程序.

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

我们在产品和开发环境中都使用MySQL数据库.该数据库每天使用一次，过程从晚上11点开始.有时甚至没人使用prod数据库，因此在闲置8小时后，MySQL数据库断开了连接.当过程开始时，我们会收到错误消息:

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.

似乎第一次连接(自动)尝试以某种方式触发了MySQL数据库的建立，而第二次(手动)尝试没有问题，因为MySQL数据库已启动并正在运行...

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...

我们有3个想法:

1. 在我们应用程序的代码中，使用一个触发器，该触发器将在过程开始前约10-15分钟尝试连接到数据库(但它仍会引发此异常，这不是很好)
2. 将MySQL上的8小时空闲计时器设置为24小时或更长时间
3. 以spring-database.xml的方式配置JDBC驱动程序，以某种方式每隔4个小时对数据库进行一次"ping"操作，以使其保持活动状态，但是我们仍然不知道如何正确执行它...

1. 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)
2. Set the 8-hour idle timer on MySQL to 24 hours or more
3. 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...

(在这3个中)哪个想法最好?我们个人认为第三个解决方案是最好的...但是如何配置JDBC驱动程序以某种方式每隔4小时左右"ping"数据库?也许您的某个人会有另一个更好的主意?

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?

非常感谢

哪个主意最好?

推荐答案

您可以在jdbc url中使用autoReconnect参数来解决此问题.例如:

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

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

这篇关于MySQL数据库会在8小时后断开连接.怎么预防呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！