Spring Boot JPA - configuring auto reconnect

Question

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

User · Accepted Answer

I assume that boot is configuring the DataSource for you  In this case  and since you are using MySQL  you can add the following to your application properties up to 1 3  spring datasource testOnBorrow true spring datasource validationQuery SELECT 1   As djxak noted in the comment  1 4  defines specific namespaces for the four connections pools Spring Boot supports  tomcat  hikari  dbcp  dbcp2  dbcp is deprecated as of 1 5   You need to check which connection pool you are using and check if that feature is supported  The example above was for tomcat so you d have to write it as follows in 1 4    spring datasource tomcat testOnBorrow true  spring datasource tomcat validationQuery SELECT 1   Note that the use of autoReconnect is not recommended      The use of this feature is not recommended  because it has side effects related to session state and data consistency when applications don t handle SQLExceptions properly  and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly

User · Answer

I have similar problem   Spring 4 and Tomcat 8  I solve the problem with Spring configuration   lt bean id  dataSource  class  org apache tomcat jdbc pool DataSource  destroy-method  close  gt       lt property name  initialSize  value  10    gt       lt property name  maxActive  value  25    gt       lt property name  maxIdle  value  20    gt       lt property name  minIdle  value  10    gt                lt property name  testOnBorrow  value  true    gt       lt property name  validationQuery  value  SELECT 1    gt    lt  bean gt    I have tested  It works well    This two line does everything in order to reconnect to database    lt property name  testOnBorrow  value  true    gt   lt property name  validationQuery  value  SELECT 1    gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[spring] Spring Boot JPA - configuring auto reconnect

Examples related to spring

Examples related to spring-boot

Examples related to configuration