Connection to Db Dies After >4<24 in Spring-Boot JPA Hibernate

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

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.

Spring Boot JPA - configuring auto reconnect

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.

spring-boot:jdbc re-connection after db outage

actually i found it does reconnect after adding:

spring:
  datasource:
    testOnBorrow: true
    validationQuery: select 1

i just needed to wait the default validationInterval that is 30sec

Losing JPA connection after some idle time

Interesting problem

It can happen due to possibly multiple reasons. your app server is losing connections to your DB. It is hard to tell looking at the logs that only connections which are idle are the only one which are getting killed.
Your DB server might be not allocating connections. I hope you have checked your DB logs for errors. How you are handling transactions in your services ? Are you using spring transactions ?

It’s a good idea always to specify destroy-method if you are using external connection pool provider , so as to shutdown the data source immediately when the spring application shuts down

<bean id="basisDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">

Change the following in your c3p0 pool settings

Take two approaches
Remove maxConnectionAge & maxIdleTime

Test by changing below two parameters , they are expensive in terms of performance hit

testConnectionOnCheckout =true 
preferredTestQuery =SELECT 1

In case if you are able to resolve it using the above settings

Change the above settings to below as the below is less expensive in terms of performance hit

idleConnectionTestPeriod – 30 
preferredTestQuery =SELECT 1

Post your findings

Connection hangs after time of inactivity

I've had problems like this before when the database is on a seperate box and there's a firewall in between which is set to timeout idle connections.

In some circumstances the firewall cuts off the connection in such a way that the JDBC end doesn't detect, and attempting to use it results in an indefinite block.

In my case it was a custom connection pool which sent a test query down the connection before returning it from the pool. I configured this test query to have a timeout (using Statement.setQueryTimeout) so that it didn't block indefinitely.

---

Related Topics