SQLException: Timed out waiting for a free available connection

Question

I'm building an app in java with Play Framework 2.0.4. The app is deployed in heroku with cleardb database.

Users keep getting this occasional error:

PlayException: Execution exception [[PersistenceException: java.sql.SQLException: Timed out waiting for a free available connection.]]
    at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:134)
    at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:115)
    at akka.actor.Actor$class.apply(Actor.scala:318)
    at play.core.ActionInvoker.apply(Invoker.scala:113)
    at akka.actor.ActorCell.invoke(ActorCell.scala:626)
    at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:197)
    at akka.dispatch.Mailbox.run(Mailbox.scala:179)
    at akka.dispatch.ForkJoinExecutorConfigurator$MailboxExecutionTask.exec(AbstractDispatcher.scala:516)
    at akka.jsr166y.ForkJoinTask.doExec(ForkJoinTask.java:259)
    at akka.jsr166y.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:975)
    at akka.jsr166y.ForkJoinPool.runWorker(ForkJoinPool.java:1479)
    at akka.jsr166y.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:104)
Caused by: javax.persistence.PersistenceException: java.sql.SQLException: Timed out waiting for a free available connection.
    at com.avaje.ebeaninternal.server.transaction.TransactionManager.createQueryTransaction(TransactionManager.java:356)
    at com.avaje.ebeaninternal.server.core.DefaultServer.createQueryTransaction(DefaultServer.java:2021)
    at com.avaje.ebeaninternal.server.core.OrmQueryRequest.initTransIfRequired(OrmQueryRequest.java:241)
    at com.avaje.ebeaninternal.server.core.DefaultServer.findId(DefaultServer.java:1212)
    at com.avaje.ebeaninternal.server.core.DefaultServer.find(DefaultServer.java:1118)
    at com.avaje.ebeaninternal.server.core.DefaultServer.find(DefaultServer.java:1105)
    at play.db.ebean.Model$Finder.byId(Model.java:237)

Now is getting worse and sometimes all users get the same error every time until I restart the app in heroku.

Any help or tips to debug it?

Answer 1

I think the problem is the same of Heroku/Play/BoneCp connection issues

Heroku close the connections after 30 seconds.

Answer 2

After adding boneCp 0.8.0.rc1, on Build.scala and

db.default.idleMaxAge=10 minutes
db.default.idleConnectionTestPeriod=30 seconds
db.default.connectionTimeout=20 second
db.default.connectionTestStatement="SELECT 1"
db.default.maxConnectionAge=30 minutes

but I still got the timeout error, specifically when i left the page opened and then hit refresh.

Answer 3

I had faced the same issue.This issue occured when there is huge reuqest thread on your Play Application and play can't able to maintain DB pool.To make Play comfortable for maintaining DB pool You need to add below three point in you aplliaction.conf files

• db.default.maxConnectionsPerPartition=100 // this say what Max connection your Play Framework need ti maintain
• db.default.minConnectionsPerPartition=10 //During start minimum how many DB connection it should have
• db.default.acquireIncrement=10 // Once Db connection exceed to maxConnectionsPerPartition then how much it need to assign more in pool