java - Connection to Db dies after >4<24 in spring-boot jpa hibernate

Question

I have an app that uses spring-boot,jpa-hiberanate with mysql.I am getting this error log

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 56,006,037 milliseconds ago.  The last packet sent successfully to the server was 56,006,037 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Here is my application.properties

# DataSource settings: set here configurations for the database connection
spring.datasource.url = jdbc:mysql://localhost:3306/test
spring.datasource.username = test
spring.datasource.password = test
spring.datasource.driverClassName = com.mysql.jdbc.Driver

# Specify the DBMS
spring.jpa.database = MYSQL

# Show or not log for each sql query
spring.jpa.show-sql = true

# Hibernate settings are prefixed with spring.jpa.hibernate.*
spring.jpa.hibernate.ddl-auto = update
spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.ImprovedNamingStrategy

To solve this issue I can use

spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

But I checked that it's not recommended .So can anyone suggest me what should I do to overcome this error

Answer 1

The easiest way is to specify the autoReconnect property in the JDBC url, although this isn't the recommended approach.

spring.datasource.url = jdbc:mysql://localhost:3306/test?autoReconnect=true

This can give issues when you have an active connection and during a transaction something happens and a reconnect is going to happen. It will not give issues when the connection is validated at the start of the transaction and a new connection is acquired at the start.

However it is probably better to enable validation of your connections during the lifetime of your application. For this you can specify several properties.

First start by specifying maximum number of connections you allow for the pool. (For a read on determining the max poolsize read this).

spring.datasource.max-active=10

You also might want to specify the number of initial connections

spring.datasource.initial-size=5

Next you want to specify the min and max number of idle connections.

spring.datasource.max-idle=5
spring.datasource.min-idle=1

To validate connection you need to specify a validation-query and when to validate. As you want to validate periodically, instead of when a connection is retrieved from the pool (this to prevent broken connections in your pool).

spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1

NOTE: The usage of a validation-query is actually discouraged with as JDBC4 has a better/different way of doing connection validation. HikariCP will automatically call the JDBC validation method when available.

Now that you are also validating while a connection is idle you need to specify how often you want to run this query for the connections and when a connection is considered idle.

spring.datasource.time-between-eviction-runs-millis=5000 (this is the default)
spring.datasource.min-evictable-idle-time-millis=60000 (this is also default)

This all should trigger validation of your (idle) connections and when an exception occurs or the idle period has passed your connections will be removed from the pool.

Assuming you are using Tomcat JDBC as the connection pool this is a nice read of what and how to configure.

UPDATE: Spring Boot 2.x switched the default connection pool to HikariCP instead of Tomcat JDBC.