Failed to validate connection Pooled connection wrapping physical connection

Question

I'm working on a Spring MVC project (Spring version 4.1.1), and I configured HikariCP as connection pool manager. When the application is not used for a while, it seems like Hikari starts losing connections with DB, and when I try to connect to the web app, Tomcat shows this:

com.zaxxer.hikari.pool.PoolBase - HikariPool-8 - Failed to validate connection Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@2bb0433a (Connection has been closed.). Possibily consider using a shorter maxLifetime value.

Here is my Hikari datasource configuration for Spring:

<!-- Initialize DataSource com.zaxxer.hikari.HikariDataSource -->
<bean id="hikariConfig" class="com.zaxxer.hikari.HikariDataSource">
    <property name="dataSourceClassName" value="org.postgresql.ds.PGPoolingDataSource" />
    
    <property name="dataSourceProperties">
        <props>
            <prop key="url">${jdbc.url}</prop>
            <prop key="user">${jdbc.username}</prop>
            <prop key="password">${jdbc.password}</prop>
        </props>
    </property>
    
    <property name="maxLifetime" value="120000" />
    <property name="connectionTestQuery" value="SELECT 1" />
    <property name="maximumPoolSize" value="10" />
</bean>

<!-- HikariCP configuration -->
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>

I already tried to use shorter maxLifetime values, as suggested by the stacktrace, but it did not make any difference.

Could anybody please help me resolving this issue? What am I doing wrong?

Other useful info:

HikariCP version: 3.3.1
Java version: 1.8
Tomcat version: 8.5
Spring MVC version: 4.1.1
JDBC PostgreSQL driver version: 42.2.16
DBMS version: PostgreSQL 13.0

You probably don't close connection to datasource so pool becomes full, can you show/check your code always close connections and if not why? or it can be wait timeout, see https://stackoverflow.com/questions/60310858/possibly-consider-using-a-shorter-maxlifetime-value-hikari-connection-pool-spr and https://dba.stackexchange.com/questions/164419/is-it-possible-to-limit-timeout-on-postgres-server — Ori Marko, Mar 15 '21 at 12:06
Thank you @user7294900 .I can ensure we always close connections. For every method accessing the db we have `try { conn = dataSource.getConnection(); ... } finally { conn.close(); }`. I also noticed that the `idle_in_transaction_session_timeout` is disabled in my DBMS. Maybe I need to set it with a value, such as 5 min? That is greater than the `maxLifetime` of the application (2min)? — Giovanni Melissari, Mar 15 '21 at 15:09
@user7294900 Unfortunately, nothing changed. I found that `idle_in_transaction_session_timeout` only affects connections whose state is `idle in transaction`, but in my case I have a lot of `idle` connections (I am using `pg_stat_activity` to spot them). I also found a few posts that suggest to use a cron job to programmatically kill idle connections :/ (https://stackoverflow.com/questions/13236160/is-there-a-timeout-for-idle-postgresql-connections, https://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically). Do you think it is worth trying this? — Giovanni Melissari, Mar 16 '21 at 09:36
I would investigate more if there's a leak, you can turn on hikari's leakDetectionThreshold or other tool — Ori Marko, Mar 16 '21 at 10:03
After 40 minutes without using the web-app, Hikari has shown `com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - Thread starvation or clock leap detected (housekeeper delta=39m45s881ms414µs700ns).` I am getting more confused :( — Giovanni Melissari, Mar 16 '21 at 11:43
@GiovanniMelissari Hi, were you able to figure out the cause? Would really appreciate it if you could share the details. — continuousLearner, Oct 03 '21 at 11:55

Failed to validate connection Pooled connection wrapping physical connection

0 Answers0