[spring] Spring Boot JPA - configuring auto reconnect

I have a nice little Spring Boot JPA web application. It is deployed on Amazon Beanstalk and uses an Amazon RDS for persisting data. It is however not used that often and therefore fails after a while with this kind of exception:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 79,870,633 milliseconds ago.
The last packet sent successfully to the server was 79,870,634 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.

I am not sure how to configure this setting and can not find information on it on http://spring.io (a very good site though). What are some ideas or pointers to information?

This question is related to spring spring-boot configuration

The answer is


I just moved to Spring Boot 1.4 and found these properties were renamed:

spring.datasource.dbcp.test-while-idle=true
spring.datasource.dbcp.time-between-eviction-runs-millis=3600000
spring.datasource.dbcp.validation-query=SELECT 1

whoami's answer is the correct one. Using the properties as suggested I was unable to get this to work (using Spring Boot 1.5.3.RELEASE)

I'm adding my answer since it's a complete configuration class so it might help someone using Spring Boot:

@Configuration
@Log4j
public class SwatDataBaseConfig {

    @Value("${swat.decrypt.location}")
    private String fileLocation;

    @Value("${swat.datasource.url}")
    private String dbURL;

    @Value("${swat.datasource.driver-class-name}")
    private String driverName;

    @Value("${swat.datasource.username}")
    private String userName;

    @Value("${swat.datasource.password}")
    private String hashedPassword;

    @Bean
    public DataSource primaryDataSource() {
        PoolProperties poolProperties = new PoolProperties();
        poolProperties.setUrl(dbURL);
        poolProperties.setUsername(userName);
        poolProperties.setPassword(password);
        poolProperties.setDriverClassName(driverName);
        poolProperties.setTestOnBorrow(true);
        poolProperties.setValidationQuery("SELECT 1");
        poolProperties.setValidationInterval(0);
        DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource(poolProperties);
        return ds;
    }
}

For those who want to do it from YAML with multiple data sources, there is a great blog post about it: https://springframework.guru/how-to-configure-multiple-data-sources-in-a-spring-boot-application/

It basically says you both need to configure data source properties and datasource like this:

@Bean
@Primary
@ConfigurationProperties("app.datasource.member")
public DataSourceProperties memberDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("app.datasource.member.hikari")
public DataSource memberDataSource() {
    return memberDataSourceProperties().initializeDataSourceBuilder()
            .type(HikariDataSource.class).build();
}

Do not forget to remove @Primary from other datasources.


In case anyone is using custom DataSource

@Bean(name = "managementDataSource")
@ConfigurationProperties(prefix = "management.datasource")
public DataSource dataSource() {
    return DataSourceBuilder.create().build();
}

Properties should look like the following. Notice the @ConfigurationProperties with prefix. The prefix is everything before the actual property name

management.datasource.test-on-borrow=true
management.datasource.validation-query=SELECT 1

A reference for Spring Version 1.4.4.RELEASE


Setting spring.datasource.tomcat.testOnBorrow=true in application.properties didn't work.

Programmatically setting like below worked without any issues.

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;    

@Bean
public DataSource dataSource() {
    PoolProperties poolProperties = new PoolProperties();
    poolProperties.setUrl(this.properties.getDatabase().getUrl());         
    poolProperties.setUsername(this.properties.getDatabase().getUsername());            
    poolProperties.setPassword(this.properties.getDatabase().getPassword());

    //here it is
    poolProperties.setTestOnBorrow(true);
    poolProperties.setValidationQuery("SELECT 1");

    return new DataSource(poolProperties);
}

The above suggestions did not work for me. What really worked was the inclusion of the following lines in the application.properties

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

You can find the explanation out here


I have similar problem. Spring 4 and Tomcat 8. I solve the problem with Spring configuration

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="initialSize" value="10" />
    <property name="maxActive" value="25" />
    <property name="maxIdle" value="20" />
    <property name="minIdle" value="10" />
     ...
    <property name="testOnBorrow" value="true" />
    <property name="validationQuery" value="SELECT 1" />
 </bean>

I have tested. It works well! This two line does everything in order to reconnect to database:

<property name="testOnBorrow" value="true" />
<property name="validationQuery" value="SELECT 1" />

As some people already pointed out, spring-boot 1.4+, has specific namespaces for the four connections pools. By default, hikaricp is used in spring-boot 2+. So you will have to specify the SQL here. The default is SELECT 1. Here's what you would need for DB2 for example: spring.datasource.hikari.connection-test-query=SELECT current date FROM sysibm.sysdummy1

Caveat: If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" drivers that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none


Examples related to spring

Are all Spring Framework Java Configuration injection examples buggy? Two Page Login with Spring Security 3.2.x Access blocked by CORS policy: Response to preflight request doesn't pass access control check Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured ApplicationContextException: Unable to start ServletWebServerApplicationContext due to missing ServletWebServerFactory bean Failed to auto-configure a DataSource: 'spring.datasource.url' is not specified Spring Data JPA findOne() change to Optional how to use this? After Spring Boot 2.0 migration: jdbcUrl is required with driverClassName The type WebMvcConfigurerAdapter is deprecated No converter found capable of converting from type to type

Examples related to spring-boot

Access blocked by CORS policy: Response to preflight request doesn't pass access control check Why am I getting Unknown error in line 1 of pom.xml? Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured How to resolve Unable to load authentication plugin 'caching_sha2_password' issue ApplicationContextException: Unable to start ServletWebServerApplicationContext due to missing ServletWebServerFactory bean Failed to auto-configure a DataSource: 'spring.datasource.url' is not specified After Spring Boot 2.0 migration: jdbcUrl is required with driverClassName ERROR Source option 1.5 is no longer supported. Use 1.6 or later How to start up spring-boot application via command line? JSON parse error: Can not construct instance of java.time.LocalDate: no String-argument constructor/factory method to deserialize from String value

Examples related to configuration

My eclipse won't open, i download the bundle pack it keeps saying error log Getting value from appsettings.json in .net core pgadmin4 : postgresql application server could not be contacted. ASP.NET Core configuration for .NET Core console application Turning off eslint rule for a specific file PHP Warning: Module already loaded in Unknown on line 0 How to read AppSettings values from a .json file in ASP.NET Core How to store Configuration file and read it using React Hadoop cluster setup - java.net.ConnectException: Connection refused Maven Jacoco Configuration - Exclude classes/packages from report not working