Hikaricp: java.sql.SQLException: Connection is closed

[ramukg]

Environment

HikariCP version: 2.5.1
JDK version     : 1.8.0_111
Database        : PostgreSQL

I am frequently getting this exception :

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)

Here is my configuration for using HikariCP with JPA Hibernate :

    JpaPersistModule jpaPersistModule = new JpaPersistModule("persistenceConfig");
    final Map<String, Object> properties = new HashMap<>();

    properties.put("hibernate.hikari.dataSource.url",
            "jdbc:postgresql://" + configurationData.getDBHost() + ":" + configurationData.getDBPort()
                    + "/" + configurationData.getDBName() + "?stringtype=unspecified");
    properties.put("hibernate.hikari.dataSource.user", configurationData.getDBUser());
    properties.put("hibernate.hikari.dataSource.password", configurationData.getDBPassword());
    properties.put("hibernate.hikari.minimumIdle", "2");
    properties.put("hibernate.hikari.maximumPoolSize", "8");
    properties.put("hibernate.hikari.connectionTimeout", "30000");
    properties.put("hibernate.hikari.idleTimeout", "60000");
    properties.put("hibernate.hikari.poolName", "master_pool");
    properties.put("hibernate.hikari.leakDetectionThreshold", "60000");
    properties.put("hibernate.hikari.maxLifetime", "600000");
    properties.put("hibernate.hikari.connectionTestQuery", "select 1");
    properties.put("hibernate.hikari.connectionInitSql", "select 1");

Here is my peristence.xml for JPA:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

    <persistence-unit name="persistenceConfig" transaction-type="RESOURCE_LOCAL">

        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL94Dialect"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.jdbc.time_zone" value="UTC"/>
            <property name="hibernate.jdbc.batch_size" value="25"/>

            <property name="hibernate.connection.provider_class" value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider"/>
            <property name="hibernate.hikari.dataSourceClassName" value="org.postgresql.ds.PGSimpleDataSource"/>

        </properties>

    </persistence-unit>

</persistence>

I have checked idle_in_transaction_session_timeout in postgres db is 1 hour. It means there is no issue from db side.

I have tried to adjust hikaricp configuration. But it is not helping.

Initially I thought it maybe because of leak connection for which I added leakDetectionThreshold to 60 sec.

Then again I thought it maybe because of getting invalid connection from pool for which I have set connectionTestQuery. But it's also not helping.

I have checked there is maximum 1 or 2 connection are active at any time. So it can't be maxpool size issue.

I have looked into this stackoverflow question. It is not making any sense for my problem statement as I am using entitymanager with Guice Transactional.So I don't need to care about connection closing which will take care by Guice Transactional.

Can anyone help with it ?

[brettwooldridge]

Maybe @vladmihalcea has some thoughts?

[vladmihalcea]

@ramukg @brettwooldridge Maybe it is a PostgreSQL Driver issue. Try to change the Driver version and see how it works.

[ramukg]

@vladmihalcea So for postgresql driver currently I am using 42.1.4 version.
<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.1.4</version> </dependency>
Should I update to latest one ? My psql server is running on version 10.6 .

[brettwooldridge]

@ramukg I always recommend being on the latest driver available from the DB vendor. Reading the driver change log might also reveal whether the issue was obviously fixed or not (though it depends on how well the change log is maintained).

[ramukg]

@brettwooldridge @vladmihalcea
Q1) What's your thoughts? Should I update HikariCP version to latest one and also postgresql version ? Will it help ?
Q2) Do I need these properties in case of PSQL driver dependency
<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.1.4</version> </dependency>

properties.put("hibernate.hikari.connectionTestQuery", "select 1"); properties.put("hibernate.hikari.connectionInitSql", "select 1");

Because according to documentation of HikariCp : If your driver supports JDBC4 we strongly recommend not setting this property. Will these properties create problem if I go against the documentation.

[brettwooldridge]

If using the latest driver, I recommend not setting a test query. You can, but performance may be slightly worse as a result, and timeouts not as precise.

[ramukg]

@brettwooldridge I have removed connectionTestQuery & update org.postgresql version to 42.2.5 (latest one) & reduced the minimumIdle connection from 2 to 1. Now still I am getting exception : java.sql.SQLException: Connection is closed but it is less frequent but it is still occur what else can I do. But connection is closed exception is unacceptable for me.

[paulharte]

I am facing the same issue with Oracle and the below versions

HikariCP : 2.7.8
JDK: 1.8.0_192
Springboot: 2.0.1.RELEASE
Oracle JDBC driver: 12.2.0.1.0

[brettwooldridge]

@paulharte I definitely recommend upgrading to v3.3.1 to see if the error still occurs. Also, consider shortening the maxLifeTime -- or at least determining who/why the connection is being severed.

[Pooj2]

Update: Fixed: My bad. The access to the blob was outside the scope of the resultset (statement, and connection).

Same issue here, sometimes, while reading a BLOB from Oracle, for different blobs. A previous version of the software used the Tomcat pool and did not give the exception Caused by: java.sql.SQLRecoverableException: Closed Connection at oracle.sql.BLOB.getDBAccess(BLOB.java:1289).

Versions:

• Spring boot 2.1.1.RELEASE
• (and so) com.zaxxer:HikariCP:jar: 3.2.0 (from the maven build)
• Oracle: ojdbc6/12.1.0.2.0 update: with the driver class oracle.jdbc.OracleDriver
• JDK: Openjdk version "1.8.0_191" OpenJDK Runtime Environment (build 1.8.0_191-b12) OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)

[paulharte]

Hey Brett,

I updated, as you suggested, unfortunately the issue still persists. The disconnect is caused by DB maintenance/outage, but we were hopeful that hikari would reconnect when a user tries to access after the DB comes back. Currently, we have to bounce. Is our maxLifetime too long?

Here are our versions now:

HikariCP: 3.2.0
JDK: 1.8.0_192
Springboot: 2.1.3.RELEASE
Oracle JDBC driver: 12.2.0.1.0

hikari config:
datasource:
hikari:
connectionTimeout: 6000
maxLifetime: 900000

[varunvohra]

I am facing this same issue. 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.

Current Config -
Database : PostgreSQL/

org.hibernate
hibernate-hikaricp
5.2.10.Final

com.zaxxer:HikariCP:jar: 2.5.1.jar (from the maven build) (if required, how to update it using maven dependency).

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

@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.

[paulharte]

@varunvohra : could we hack this by setting the hikari.maximumPoolSize to something larger, and setting minimumIdle and idleTimeout? Does that make sense with what you have observed?

[hebelala]

@brettwooldridge I think, this isn't driver problem, but the reason is that the framwork higher level than HikariCP reuse the broken HikariProxyConnection. Because please see the thread dump, the exception Connection is closed isn't Driver connection class, but ocuurs at ProxyConnection$ClosedConnection$1 :

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)

[vaquarkhan]

I am using latest DB driver MYSQL 8 and AWS RDS and getting same exception , did anyone get root cause and fix ?

                   <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.11</version>
            </dependency>

and with Springboot 2.1.2.RELEASE

                             <groupId>com.zaxxer</groupId>
                           <artifactId>HikariCP</artifactId>
                            <version>3.2.0</version>