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

Question

• 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

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

For below configuration application working fine:

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

can any one explain what is happening exactly?

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.