SQLException: Timed out waiting for a free availab

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

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 2

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

Answer 3

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

Heroku close the connections after 30 seconds.

Answer 4

Hi have this question too, have you managed to solve it? I searched on the web, about this. They all say that you do not close your connections to the database. But I dont open them at all, I mean play do this for me. They also found the code close only works on scala..

The last "solution" I found, but it is not working 100%, is in the configuration file. http://www.playframework.com/documentation/2.0/SettingsJDBC