Spring Boot: Communications link failure after some hours of inactivity with Hibernate, JDBC and MySQL [duplicate]

Question

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

Answer 1

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