Spring Boot JPA – configuring auto reconnect

configurationspringspring-boot

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?

Best 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.

Related Solutions

Mysql – Reproduce com.thesql.jdbc.exceptions.jdbc4.CommunicationsException with a setup of Spring, hibernate and C3P0

I had similar problems with MySQL and a connection pool. The problem is you tell the connection pool that an idle timeout is 30 minutes, but the database cuts the connection after 10 seconds. Since your idle connection check period is 120 sec, it leaves a little under 110 secs for the pool to use a broken connection!

I'd use the following settings for production:

MySQL:
wait_timeout=75
C3P0:
maxIdleTime=60
idleConnectionTestPeriod=55

Java – Connection with MySql is being aborted automatically. How to configure Connector/J properly

The text describes three solutions to prevent connection aborts:

Configure the connection string with autoReconnect=true. This is a property of the URL connection string, which works at the driver level. You need to change the connection string in the data source configuration.
```
url="jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
```
Increase the timeout. This is normally a property of the database. You can increase this value to see if you get less connection abort.
Configure the connection pool to test the connection validatiy. This is done at the pool, not a the driver level. This will depend on the data source implementation that you use. But it should be configurable in the property of the data source, if you use a pooled one, e.g. c3p0.

Additionnal comments:

The datasource/pool can also have a timeout, which corresponds to the time an idle connection remains in the pool. To not confused with the db timeout.
There are several way to test the validity of a connection. One common way is to have dummy test table. The pool will issue a select on the dummy test table to see if the connection is still OK.

Best Answer

Related Solutions

Mysql – Reproduce com.thesql.jdbc.exceptions.jdbc4.CommunicationsException with a setup of Spring, hibernate and C3P0

Java – Connection with MySql is being aborted automatically. How to configure Connector/J properly

Related Topic