Spring JPA Download - Configure Auto Reconnect

Question

I have a cute little Spring Boot JPA web application. It is deployed on Amazon Beanstalk and uses Amazon RDS to save data. However, it is not used so often and therefore does not pass after a while with the following exception:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 79,870,633 milliseconds ago.
The last packet successfully sent to the server was 79,870,634 milliseconds ago. greater than the wait_timeout value configured by the server. To avoid this problem, you should consider expiring and / or checking the validity of the connection before using it in the application, increasing the values ​​configured by the server for client timeouts, or using the Connector / J connection property 'autoReconnect = true'.

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

Answer 1

I assume the download configures the DataSource for you. In this case, and since you are using MySQL, you can add the following to application.properties to 1.3

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

As djxak notes in a comment, 1.4+ defines specific namespaces for the four Spring connection pools. Boot support: tomcat , hikari , dbcp , dbcp2 ( dbcp deprecated from 1.5). You need to check which connection pool you are using and check if this feature is supported. The example above was for tomcat, so you need to write it to 1.4 +:

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

Please note that using autoReconnect not recommended :

Using this function is not recommended because it has side effects related to session state and data consistency when applications do not handle SQLExceptions correctly and are for use only when you cannot configure the application to handle SQLExceptions caused by dead and stale connections.

Answer 2

The above suggestions did not help me. What really worked was to include the following lines in application.properties

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

You can find an explanation here.

Answer 3

I just switched to Spring Boot 1.4 and found that 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 

Answer 4

Setting spring.datasource.tomcat.testOnBorrow=true in application.properties does not work.

The software setup, as shown below, worked without problems.

 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); } 

Answer 5

I have a similar problem. Spring 4 and Tomcat 8. I am solving a Spring configuration problem

 <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 tested. It works well! These two lines do everything to reconnect to the database:

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

Answer 6

Whoami answer is correct. Using the proposed properties, I could not get this to work (using Spring Boot 1.5.3.RELEASE)

I am adding my answer as it is a complete configuration class, so it can 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; } } 

Answer 7

In case someone uses their own data source

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

Properties should look like this. Check out @ConfigurationProperties with a prefix. The prefix is ​​everything before the actual property name

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

Link to Spring 1.4.4.RELEASE

Answer 8

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

Warning : If your driver supports JDBC4, we strongly recommend that you do not set this property. This is for legacy drivers that do not support the JDBC4 Connection.isValid () API. This is a request that will be executed just before you are given a connection from the pool to verify that the database connection is still alive. Again, try to start the pool without this property, HikariCP will log an error if your driver is not JDBC4 compatible to inform you. Default: none