Hikaricp: org.hibernate.exception.GenericJDBCException: could not prepare statement ~~ Caused by: java.sql.SQLException: Connection is closed

[varunvohra]

Environment

HikariCP version: org.hibernate hibernate-hikaricp 5.2.10.Final
com.zaxxer:HikariCP:jar: 2.5.1.jar (from the maven build)
JDK version     : 1.8.0_201
Database        : PostgreSQL - 10.7 (Ubuntu 10.7-1.pgdg16.04+1)
Driver version  : 42.2.5

After bringing the application up, it runs for around 7-10 days, then following exception starts occurring at each DB call

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
    at org.hibernate.query.Query.getResultList(Query.java:146)
    at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)
    at com.inmobi.programmatics.myservice.dao.impl.AbstractDAOImpl.getByIds(AbstractDAOImpl.java:117)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1940)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1909)
Caused by: java.sql.SQLException: Connection is closed
    at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection$1.invoke(ProxyConnection.java:469)
    at com.sun.proxy.$Proxy66.prepareStatement(Unknown Source)
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:310)
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)

Configuration -

hikari.minimumIdle=1
hikari.maximumPoolSize=8
hikari.maxLifetime=600000
hikari.connectionTimeout=30000
hikari.leakDetectionThreshold=60000
hikari.idleTimeout=60000
hikari.connectionInitSql=select 1

idle_in_transaction_session_timeout = 2800s (no issue from DB side)

I followed the logs and found the trend -

java.lang.Exception: Apparent connection leak detected - Usually this happens in some of the queries, once in few hours, but soon 'was returned to the pool' gets printed and connection is returned to the pool. This thing doesn't bother my prod app much.
But In between, 'Apparent connection leak detected' gets printed but that connection is never returned to the pool eventually.

After few days (approx a week) of the service being UP, when maxPoolSize no. of connections got exhausted(not returned to the pool), org.hibernate.exception.GenericJDBCException: could not prepare statement || Caused by: java.sql.SQLException: Connection is closed starts getting printed and application is apparently dead as no DB call can be made after that.

I tried to tweak the hikari configurations but no help.

At last, I am thinking of hack i.e. to kill the connection after some time (lets say 2 min) if its is out of the pool whether it is an in-use connection or whatever its condition is. Automatically, new connection will be added to pool and my service will keeps on running. Is this thinking right? (I can afford this considering I can kill if my query taking more than 2 min).

FYI - EntityManager should be returning connection back to CP, as I am using Guice Transactional.

Is there any HIKARI config setting to achieve above use case.

If required, how to update Hikari version using maven dependency, currently using org.hibernate hibernate-hikaricp 5.2.10.Final
com.zaxxer:HikariCP:jar: 2.5.1.jar (from the maven build)

@brettwooldridge sir, can you please help me with this. If we can get around this error without killing connection (the way I am suggesting), that will be a better way.

Thanks

[brettwooldridge]

@varunvohra One question is, where does the leak detection stacktrace point to as the source of connection acquisition?

You may need to upgrade Hibernate, but I know that in more recent versions the HikariCP dependency is a separately updatable artifact, hibernate-hikaricp.

[Peroucho8]

@varunvohra did you solve the problem ? im having that problem too