Getting "Timed out waiting for a free available connection" (from BoneCP).

[Rintcius Blok]

Hi,

I am using scalaquery 0.9.5 & have a problem using it together BoneCP (0.7.1) and Play 2.0.3.
Every now and then I get this exception:

2012-11-20 17:34:54,226 - [ERROR] - from akka.routing.RouterConfig$$anon$1 in play-akka.actor.default-dispatcher-1
Timed out waiting for a free available connection.
java.sql.SQLException: Timed out waiting for a free available connection.
        at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:503) ~[com.jolbox.bonecp-bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE]
        at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:114) ~[com.jolbox.bonecp-bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE]
        at org.scalaquery.session.Database$$anon$1.createConnection(Database.scala:82) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.session.BaseSession.conn(Session.scala:98) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.session.Session$class.prepareStatement(Session.scala:25) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.session.BaseSession.prepareStatement(Session.scala:92) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.StatementInvoker.results(StatementInvoker.scala:29) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.StatementInvoker.elementsTo(StatementInvoker.scala:17) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.Invoker$class.foreach(Invoker.scala:95) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.StatementInvoker.foreach(StatementInvoker.scala:10) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.Invoker$class.firstOption(Invoker.scala:39) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.StatementInvoker.firstOption(StatementInvoker.scala:10) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.UnitInvoker$class.firstOption(Invoker.scala:153) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]
        at org.scalaquery.ql.basic.BasicQueryInvoker.firstOption(BasicQueryInvoker.scala:9) ~[org.scalaquery.scalaquery_2.9.0-1-scalaquery_2.9.0-1-0.9.5.jar:na]

This happens usually on the first request after the application has not been used for a while. Requests after this first one will run fine.
I guess I am not closing some DB resource correctly but I cannot figure out what.

Basically, this is how I use scalaquery:

val database = Database.forDataSource(dataSource)

def doWithSession[A](f: (Session) => A) = {
  database.withSession(f)
}

Then all queries use doWithSession. Any ideas what may be going on?

Regards,
Rintcius

[Stefan Zeiger]

On 2012-11-26 14:29, Rintcius Blok wrote:

This happens usually on the first request after the application has not been used for a while. Requests after this first one will run fine.
I guess I am not closing some DB resource correctly but I cannot figure out what.

This could be caused by a dead connection in the pool. Connection pools usually have a setting for pinging idle connections at regular intervals to keep them alive. Have you tried that?

--
Stefan Zeiger
Typesafe - The software stack for applications that scale
Twitter: @StefanZeiger

[sb]

Sounds like you connections are timing out while sitting idle in the pool.  You'll want to check out the setIdleConnectionTestPeriod or one of the other similar methods that BoneCP offers for managing idle connections, http://jolbox.com/bonecp/downloads/site/apidocs/com/jolbox/bonecp/BoneCPConfig.html.

[Ivan Meredith]

You arent using this with an AsyncResult are you?

Anyay I made https://github.com/hadashi/play2-c3p0-plugin for play2.1, it should be compatable with 2.x if you change the play version in build.sbt. It can help rule out bonecp.

[Rintcius Blok]

Hi Stefan, sb & Ivan, thanks for your replies.

I tried setting the idle connection test period and initSQL, but that gives the same exception. I also tried adding all settings that are mentioned here (with the same values): https://github.com/playframework/Play20/wiki/SettingsJDBC which also gives the same error.
Any further ideas?

Thanks for suggesting c3p0 plugin, Ivan. I will give that a try & update here how that goes.

Rintcius

[Rintcius Blok]

FYI. The changed settings worked, my bad. Coincidently I still had the same end result - first request not working, requests after that working - but with a different cause (not DB related).

[virtualeyes]

I have to Play 2.1 apps in production, using SQ 0.10, on Scala 2.9.2 -- have yet to see this issue with BoneCP

Check Player user group, however, I do recall seeing a BoneCP problem as you are describing, they may have fixed in Play 2.1