12

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:

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)?

Community
  • 1
  • 1
Andrea
  • 15,900
  • 18
  • 65
  • 84
  • You should read past the C3P0 comments. There are MySQL tweaks and tweaks to your connection URL (validation query and auto-reconnect are the most important). – M. Deinum May 20 '15 at 09:48
  • Thanks @M.Deinum for your suggestions. Reading here http://stackoverflow.com/questions/667289/why-does-autoreconnect-true-not-seem-to-work it seems that setting `autoReconnect`in the connection string is not reccomended.. So I'm going to try with some options for my datasource: `testWhileIdle`, `timeBetweenEvictionRunsMillis` and `validationQuery`. However I'm still waiting for some good and "definitive" answer :) – Andrea May 20 '15 at 12:57

1 Answers1

18

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
Andrei Epure
  • 1,746
  • 1
  • 21
  • 30
Andrea
  • 15,900
  • 18
  • 65
  • 84