Spring Boot communications link failure with MySQL and Hibernate

When you put a Spring Boot web application in production can happen that it will be inactive for several hours (e.g. for a whole night) without making any communication with database.

Using MySQL this can lead to a “communications link failure” error like this:

2015-07-08 09:16:32.666  WARN 20582 --- [http-nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08S01
2015-07-08 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.

MySQL has a wait timeout limit set for default to 8 hours (28800 seconds). If the database connection is inactive for more than 8 hours it is automatically closed and the error above will happen.

How to avoid this

In Spring Boot, we can solve this problem adding these configurations in the application.properties file:

src/main/resources/application.properties

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

Explanation: each hour (3600000 millis) minute (60000 millis) will be performed a connection test executing the query “SELECT 1”. In this way we can keep alive the database connection, periodically performing a validation query, and avoid to reach the MySQL’s wait_timeout.

References

https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
http://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html
http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-connect-to-production-database
http://stackoverflow.com/questions/3888776/basicdatasource-connection-time-out-problem-using-mysql
http://stackoverflow.com/questions/30451470/connection-to-db-dies-after-424-in-spring-boot-jpa-hibernate

MySQL wait_timeout and interactive_timeout

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_wait_timeout http://www.rackspace.com/knowledge_center/article/how-to-change-the-mysql-timeout-on-a-server
http://serverfault.com/questions/355750/mysql-lowering-wait-timeout-value-to-lower-number-of-open-connections