Spring Boot：通过Hibernate，JDBC和MySQL几小时不活动后，通信链接失败 [英] Spring Boot: Communications link failure after some hours of inactivity with Hibernate, JDBC and MySQL

问题描述

  2015- 2015年春季开始的应用程序不活动几个小时（例如夜间） 05-19 09：16：32.666警告20582 --- [http-nio-8080-exec-6] ohengine.jdbc.spi.SqlExceptionHelper：SQL错误：0，SQLState：08S01 
 2015-05-19 09：16：32.668错误20582 --- [http-nio-8080-exec-6] ohengine.jdbc.spi.SqlExceptionHelper：通信链路故障
 
从服务器成功接收的最后一个数据包是29.792.613毫秒前。成功发送到服务器的最后一个数据包是6毫秒前。 
  

试着解决这个问题，我读到 MySQL 有一个名为 wait_timeout 默认设置为8小时（28800秒），在这段时间之后，所有不活动的连接都关闭，所以我的Spring Boot应用程序停止工作...

我的问题是：

• 我该如何避免这个问题？


• 我应该增加这个参数的值吗？


• 有没有增加这种价值的缺点？ （它可以永远是我的应用程序所达到的值，如果它不活跃，那么很长..：/）


• 没有其他解决方案使用 Spring Boot 投票或类似的东西）？

---

编辑

这里还有其他类似/有用的问题：

• Hibernate通信链接失败，基于Hibernate的Java Servlet应用程序由MySQL驱动


• Spring正在失去与数据库的连接，并且无法恢复或重新连接 WARN SqlExceptionHelper：143 - SQL错误：0，SQLState：08S01- SqlExceptionHelper：144 - 通信链接失败 SQL错误：0，SQLState：08S01通信链接失败


• https://serverfault.com/questions/89955/unable-to-connect- to-mysql-through-jdbc-connector-through-tomcat-or-external


• 解决通信链路故障使用JDBC和MySQL

从上面的参考文献可以得出结论：一种解决方案是添加 C3p0 作为附加库，并正确配置它以避免通信链接错误。

它是我唯一的解决方案吗？是不是有更多的与Spring / Spring Boot集成的解决方案（即不需要添加外部库）？

解决方案

解决了这里描述的问题：
http://blog.netgloo.com/2015/07/09/spring-boot-communications-link-failure-with-mysql-and-hibernate/ ，添加这些配置在 application.properties 文件中：

  spring.datasource.tomcat。 testWhileIdle = true 
 spring.datasource.tomcat.timeBetweenEvictionRunsMillis = 60000 
 spring.datasource.tomcat.validationQuery = SELECT 1 
  

I get this error if my Spring Boot application is inactive for several hours (e.g. during the night):

2015-05-19 09:16:32.666  WARN 20582 --- [http-nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08S01
2015-05-19 09:16:32.668 ERROR 20582 --- [http-nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : Communications link failure

The last packet successfully received from the server was 29.792.613 milliseconds ago.  The last packet sent successfully to the server was 6 milliseconds ago.

Trying to solving this problem I read that MySQL have a parameter named wait_timeout setted by default to 8 hours (28800 seconds) and after this time all my inactive connections are closed, so my Spring Boot application stop to works...

My questions are:

• How can I avoid this problem?
• Should I increase the value for such parameter?
• Are there any drawbacks increasing such value? (It could always be a value reached by my application if it is inactive so long.. :/ )
• Is not there another solution using Spring Boot (like a polling or something like that)?

---

EDIT

There are other similar/useful questions here:

• Hibernate Communications Link Failure in Hibernate Based Java Servlet application powered by MySQL
• Spring is losing connection to the DB and does not recover or reconnect
• WARN SqlExceptionHelper:143 - SQL Error: 0, SQLState: 08S01- SqlExceptionHelper:144 - Communications link failure
• SQL Error: 0, SQLState: 08S01 Communications link failure
• https://serverfault.com/questions/89955/unable-to-connect-to-mysql-through-jdbc-connector-through-tomcat-or-externally
• Solving a "communications link failure" with JDBC and MySQL

From references above I can conclude that one solution is to add C3p0 as additional library and properly configure it to avoid the communication link error..

Is it the only solution I have? Isn't there a solution more "integrated" with Spring/Spring Boot (i.e. without adding an external library)?

解决方案

I solved the problem as described here: http://blog.netgloo.com/2015/07/09/spring-boot-communications-link-failure-with-mysql-and-hibernate/ , adding these configurations in the application.properties file:

spring.datasource.tomcat.testWhileIdle = true
spring.datasource.tomcat.timeBetweenEvictionRunsMillis = 60000
spring.datasource.tomcat.validationQuery = SELECT 1

这篇关于Spring Boot：通过Hibernate，JDBC和MySQL几小时不活动后，通信链接失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！