0

Environment:

Java 8
WildFly 8 (WF 8)
PostgreSQL 11
Spring 4.3
Hibernate 5

DBCP version 2.5
PostgreSQL JDBC driver: 42.2.5

In this Java app that we have, we run a job via Quartz. Say every N hours a method is called, this method spawns 30-40 threads which start doing some work. This work involves accessing the PostgreSQL DB.

When WildFly is not rebooted for 4-5 consecutive days, and the app deployed in WildFly is also not rebooted... then after some time we start getting this error.

What could be the cause? Seems like the conn pool is giving to the app a conn which is already closed. But we do have testOnBorrow=true in our data source configuration. So how is this possible?

Any ideas how we can get rid of this error and do it in the best possible way? To me it seems a bug either in DBCP or in the JDBC driver but I am far from sure.

    27 May 2019 18:12:37 ERROR [taskExecutor-13] synchronizer.SynchronizerPostProcessUtil:162 - Batch Process Call Errored - DB is not updated
    org.springframework.orm.hibernate5.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [ select count(1) from kwt.Error_BatchUpdate(?,?,?) ]; SQL state [null]; error code [0]; could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:248)
        at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:368)
        at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:315)
        at com.company123.db.dao.common.HibernateDao.callStoredProcedureWithParameters(HibernateDao.java:947)
        at com.company123.db.dao.common.HibernateDao.callOpenXmlBatchProcessingWithParameters(HibernateDao.java:927)
        at com.company123.synchronizer.SynchronizerPostProcessUtil.executeBatchItems(SynchronizerPostProcessUtil.java:156)
        at com.company123.synchronizer.SynchronizerPostProcessUtil.processPostProcessItems(SynchronizerPostProcessUtil.java:76)
        at com.company123.synchronizer.AbstractSubAccountSynchronizer.createSubAccount(AbstractSubAccountSynchronizer.java:51)
        at com.company123.synchronizer.AbstractSubAccountSynchronizer.createSubAccount(AbstractSubAccountSynchronizer.java:23)
        at com.company123.synchronizer.SESynchronizer.syncSubAccount(SESynchronizer.java:990)
        at com.company123.synchronizer.SESynchronizer.syncLocalToSE(SESynchronizer.java:332)
        at com.company123.lifecycle.mb.LifeCycleRunnable.runMBLifeCycle(LifeCycleRunnable.java:188)
        at com.company123.lifecycle.mb.LifeCycleRunnable.run(LifeCycleRunnable.java:113)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
    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:181)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:147)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1985)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1915)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
        at org.hibernate.loader.Loader.doQuery(Loader.java:938)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
        at org.hibernate.loader.Loader.doList(Loader.java:2692)
        at org.hibernate.loader.Loader.doList(Loader.java:2675)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
        at org.hibernate.loader.Loader.list(Loader.java:2502)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2200)
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016)
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
        at org.hibernate.query.internal.AbstractProducedQuery.uniqueResult(AbstractProducedQuery.java:1457)
        at com.company123.db.dao.common.HibernateDao$19.doInHibernate(HibernateDao.java:971)
        at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:361)
        ... 14 more
    Caused by: java.sql.SQLException: Connection org.postgresql.jdbc.PgConnection@25d46a62 is closed.
        at org.apache.commons.dbcp2.DelegatingConnection.checkOpen(DelegatingConnection.java:594)
        at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:289)
        at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:292)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:145)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:171)
        ... 32 more
3
  • I'd guess there is a firewall that randomly drops connections. Try setting tcp_keepalives_idle on the PostgreSQL server to a lower value and see if that helps. Commented May 29, 2019 at 9:04
  • 1
    Could you please post all connection pool related properties.If the connection pool is holding invalid connection then you should have the evictor thread configured to check the connections sitting in the pool while they are idle and have them dropped when they are invalid. Commented Jun 12, 2019 at 14:28
  • You can check the settings here stackoverflow.com/questions/56111248/… Commented Jun 12, 2019 at 14:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.