Re: [2.0.4] - BoneCP Returning Closed Connections

[Eishay]

We have the same problem, any resolution?

On Wednesday, October 31, 2012 9:08:43 AM UTC-7, Jordan Van Winkle wrote:

I have an interesting issue which I have not been able to resolve. I am using Play! 2.0.4 and using the integrated BoneCP connection pool to get the DB connections. However, for some reason, BoneCP keeps returning closed connections.

Database Server: Amazon RDS MySQL 5, default timeout settings (which should be 8 hours...)

My Play Datasource configuration looks as follows:

db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://{server}/{schema}?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8"
db.default.partitionCount=4
db.default.idleConnectionTestPeriod=2 minutes

I had assumed setting the idleConnectionTestPeriod to 2 minutes surely would have prevented BoneCP from returning closed connections, but it hasn't.

Every so often, I get the following stack trace in my logs:

    Exception in thread "pool-6-thread-25" java.sql.SQLException: Connection is closed!
    at com.jolbox.bonecp.ConnectionHandle.checkClosed(ConnectionHandle.java:350)
    at com.jolbox.bonecp.ConnectionHandle.setReadOnly(ConnectionHandle.java:1089)
    at play.api.db.BoneCPApi$$anon$1.onCheckOut(DB.scala:328)
    at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:514)
    at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:114)
    at play.api.db.DBApi$class.getConnection(DB.scala:64)
    at play.api.db.BoneCPApi.getConnection(DB.scala:273)
    at play.api.db.DB$$anonfun$getConnection$1.apply(DB.scala:129)
    at play.api.db.DB$$anonfun$getConnection$1.apply(DB.scala:129)
    at scala.Option.map(Option.scala:133)
    at play.api.db.DB$.getConnection(DB.scala:129)
    at play.api.db.DB.getConnection(DB.scala)
    at play.db.DB.getConnection(DB.java:50)
    at play.db.DB.getConnection(DB.java:43)
    at play.db.DB.getConnection(DB.java:29)
    at com.edatasource.inboxtracker.tasks.TrackSiteEventActionTask.run(TrackSiteEventActionTask.java:23)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:662)   

[Thomas Felix]

I have the same problem. Apparently the problem occures from time to time on the mailing list. But at least for me it was never solved. My app ran for about a week or so with no problem and after this I got a:

[many more lines...]
        at play.core.ActionInvoker$$anonfun$receive$1$$anonfun$6.apply(Invoker.scala:126) ~[play.play_2.9.1-play_2.9.1-$
        at play.utils.Threads$.withContextClassLoader(Threads.scala:17) ~[play.play_2.9.1-play_2.9.1-2.0.4.jar:2.0.4]
        at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:125) [play.play_2.9.1-play_2.9.1-2.0.4.jar:2.$
        ... 11 common frames omitted
Caused by: 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.R$
        at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:114) ~[com.jolbox.bonecp-bonecp-0.7.1$
        at play.db.ebean.EbeanPlugin$WrappingDatasource.getConnection(EbeanPlugin.java:146) ~[play.play_2.9.1-play_2.9.$
        at com.avaje.ebeaninternal.server.transaction.TransactionManager.createQueryTransaction(TransactionManager.java$
        ... 25 common frames omitted

My configuration:

# Timeout in ms
db.default.connectionTimeout=15 seconds
db.default.partitionCount=2
db.default.acquireRetryAttempts=10
db.default.idleConnectionTestPeriod=1 second
db.default.maxConnectionPerPartition=2
db.default.minConnectionPerPartition=4

Today I tried to set the min & maxConnectionPerPartition to 1 and reduced the partitionCount also to 1. But according to mysql workbench when I start the app in dev mode locally it consumes 9 (!) connections to the database on my remote server. Why is this the case? Shouldn't it use just one connection with this setting? Has it something to do with dev mode? I didnt test it in production though...

[Eishay Smith]

We're getting this issue now all the time, which makes us bounce servers every 24 hours or so. 

--
 
 

[James Roper]

For a description of the problem and a work around, see this:

https://play.lighthouseapp.com/projects/82401/tickets/754-cannot-set-important-bonecp-parameter-in-configuration

It should be fixed in Play 2.1.

[Eishay Smith]

thanks!

--
 
 

[Thomas Felix]

Nice then I am looking forward to the play 2.1 release. =)

[Joe Zulli]

Was this fixed in 2.1? I am still having occasional issues on Play 2.1.1. Not positive that it's the same thing.

Joe

[Joe Zulli]

Nevermind. I see that it has been fixed.