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
