I was facing an issue with Bonecp 0.7.1 on a Playframework app using postgresql 9.2.4 on Heroku. It seems this version had a DB connection leak causing after several DB accesses the folllwing error :
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 1. Exception: null.Message:FATAL: too many connections for role "eonqhnjenuislk" Database warning
[error] c.j.b.PoolWatchThread - Error in trying to obtain a connection. Retrying in 1000ms
org.postgresql.util.PSQLException: FATAL: too many connections for role "eonqhnjenuislk"
at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:469) ~[postgresql-9.1-901.jdbc4.jar:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112) ~[postgresql-9.1-901.jdbc4.jar:na]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) ~[postgresql-9.1-901.jdbc4.jar:na]
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125) ~[postgresql-9.1-901.jdbc4.jar:na]
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30) ~[postgresql-9.1-901.jdbc4.jar:na]
at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22) ~[postgresql-9.1-901.jdbc4.jar:na]
As every threads of the connection pool was acquired and retained, the application was not reachable anymore until I restarted it.
Then I heard that this issue was corrected in Bonecp 0.8.0, so I upgraded the lib. But the issue seems not to be completely fixed. In fact, now connection threads are not retained anymore what make the application reachable at anytime but sometimes a DB connection close suddenly... The app throws the following error causing an 500 error to the end users :
javax.persistence.PersistenceException: org.postgresql.util.PSQLException: This connection has been closed.
at com.avaje.ebeaninternal.server.transaction.TransactionManager.createTransaction(TransactionManager.java:331)
at com.avaje.ebeaninternal.server.core.DefaultServer.createServerTransaction(DefaultServer.java:2056)
at com.avaje.ebeaninternal.server.core.BeanRequest.createImplicitTransIfRequired(BeanRequest.java:58)
at com.avaje.ebeaninternal.server.core.PersistRequest.initTransIfRequired(PersistRequest.java:81)
at com.avaje.ebeaninternal.server.persist.DefaultPersister.executeSqlUpdate(DefaultPersister.java:146)
at com.avaje.ebeaninternal.server.core.DefaultServer.execute(DefaultServer.java:1928)
at com.avaje.ebeaninternal.server.core.DefaultServer.execute(DefaultServer.java:1935)
at com.avaje.ebeaninternal.server.core.DefaultSqlUpdate.execute(DefaultSqlUpdate.java:148)
at actor.PublicParkingPlacesActor$1.apply(PublicParkingPlacesActor.java:41)
at actor.PublicParkingPlacesActor$1.apply(PublicParkingPlacesActor.java:26)
at play.libs.F$Promise$PromiseActor.onReceive(F.java:425)
at akka.actor.UntypedActor$$anonfun$receive$1.applyOrElse(UntypedActor.scala:159)
at akka.actor.ActorCell.receiveMessage(ActorCell.scala:425)
at akka.actor.ActorCell.invoke(ActorCell.scala:386)
at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:230)
at akka.dispatch.Mailbox.run(Mailbox.scala:212)
at akka.dispatch.ForkJoinExecutorConfigurator$MailboxExecutionTask.exec(AbstractDispatcher.scala:502)
at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:262)
at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:975)
at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1478)
at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:104)
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:714)
at org.postgresql.jdbc2.AbstractJdbc2Connection.setAutoCommit(AbstractJdbc2Connection.java:661)
at com.jolbox.bonecp.ConnectionHandle.setAutoCommit(ConnectionHandle.java:1292)
at play.api.db.BoneCPApi$$anon$1.onCheckOut(DB.scala:328)
at com.jolbox.bonecp.AbstractConnectionStrategy.postConnection(AbstractConnectionStrategy.java:75)
at com.jolbox.bonecp.AbstractConnectionStrategy.getConnection(AbstractConnectionStrategy.java:92)
at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:553)
at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:131)
at play.db.ebean.EbeanPlugin$WrappingDatasource.getConnection(EbeanPlugin.java:146)
at com.avaje.ebeaninternal.server.transaction.TransactionManager.createTransaction(TransactionManager.java:297)
... 20 more
Thanks a lot for your help!
EDIT :
DB configuration :
db.default.isolation=READ_COMMITTED
db.default.partitionCount=2
db.default.maxConnectionsPerPartition=10
db.default.minConnectionsPerPartition=5
db.default.acquireIncrement=1
db.default.acquireRetryAttempts=2
db.default.acquireRetryDelay=5 seconds
db.default.connectionTimeout=10 second
db.default.idleMaxAge=10 minute
db.default.idleConnectionTestPeriod=5 minutes
db.default.initSQL="SELECT 1"
db.default.maxConnectionAge=1 hour
EDIT 2:
Here is the DB config I set according to this post Heroku/Play/BoneCp connection issues These changes reduce the number of "This connection has been closed" issues, but I still get 1 or 2 of them perdays what makes some HTTP requests to fail. So the issue is still not fixed:
db.default.isolation=READ_COMMITTED
db.default.partitionCount=2
db.default.maxConnectionsPerPartition=10
db.default.minConnectionsPerPartition=5
db.default.acquireIncrement=1
db.default.acquireRetryAttempts=2
db.default.acquireRetryDelay=5 seconds
db.default.connectionTimeout=10 seconds
db.default.idleMaxAge=10 minutes
db.default.idleConnectionTestPeriod=30 seconds
db.default.initSQL="SELECT 1"
db.default.maxConnectionAge=30 minutes