Failed to validate connection (This connection has been closed.). Possibly consider using a shorter maxLifetime value

Question

I see the following error message:

HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@f162126 (This connection has been
closed.). Possibly consider using a shorter maxLifetime value.
frequently refreshing the same page gives the above warning after
exceeding maxLifetime

This was my original database configuration:

spring.datasource.hikari.auto-commit=false  
spring.datasource.hikari.idleTimeout=180000  
spring.datasource.hikari.minimumIdle=5  
spring.datasource.hikari.leakDetectionThreshold=240000    
spring.datasource.hikari.maximumPoolSize=10  
logging.level.com.zaxxer.hikari=TRACE  
spring.datasource.hikari.connectionTimeout=30000  
spring.datasource.hikari.maxLifetime=300000  
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG  

The application is working fine if I change the following properties:

spring.datasource.hikari.maximumPoolSize=100
spring.datasource.hikari.maxLifetime=60000

Can any one explain what is happening exactly?

Predigestion

Do you open a new connection on every refresh? Please consider adding a Minimal, Complete, and Verifiable Examples. – Udela

Answer 1

As the error message suggests, this is being caused by Hikari Connection Pool attempting to use a connection that has already been closed.

Your database connections are just TCP connections and when these are sat idle for too long they can be closed by the database or any firewall in-between.

Hikari CP is doing a check on a connection to see if it's still alive & can be used. If it has already been closed it's going to warn you because opening a new connection is going to add latency to your database access. You can see that error being thrown in the method isConnectionAlive here.

Client Side

As the error message suggests, you can decrease your maxLifetime configuration to fix this issue.

The maxLifetime property is the time before a connection will be closed by the client. As is suggested in the Hikari CP documentation, this should be at least a few seconds shorter of any database/architecture timeout.

The reason being that if Hikari CP is always closing the connection before the database, it will never attempt to use a connection that has already been closed.

As I don't know your database or architecture, I cannot suggest what value this should be. You need to find this out the idle timeout of you connections to accurately set your maxLifetime configuration.

You can read the documentation for this property on the Hikari Github readme.

Database Side

If it's your database (rather than a firewall etc) that is the bottleneck for the TCP timeout, there are also some connection settings for Postgres that can have an impact.

I wanted to mention these but changing these aren't really necessary as these normally reasonable defaults.

You can find the documentation for the properties in the Connection Settings Postgres documentation

These are the properties you are looking for:

tcp_keepalives_idle

This is the amount of time a TCP connection should be idle before the OS sends a keepalive message.

tcp_keepalives_interval

This is the amount of time after the OS has sent an unacknowledged keepalive message before it should retry.

tcp_keepalives_count

This is the number of unacknowledged keepalive messages that can be sent before the connection is considered dead.

Heteroplasty

any thoughts on how we should set max lifetime if we're accessing multiple databases? Should we set it to the minimum of all the databases? – Rox

@GeoffLangenderfer if you're accessing multiple databases, these will be accessed through different connection pools & you can the set different max lifetime based on each database: github.com/brettwooldridge/… – Heteroplasty

Answer 2

If it's happend during tests with testcontainer it may be solved by annotation @DirtiesContext SpringBootTest is reusing Spring context between tests so there is a common Hikari Pool between tests. But in the background testcontainers killed a container and created a new one before the next test. SpringBootTest is not aware of that change resulting in a new Postgres container so Hikari Pool is the same as in the previous test.

Dharna

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Meridith