Play slick session connection timeout

Question

I'm having a problem with the session and connection handling in play with Slick 2.0.1 and play-slick 0.6.0.1

The error is

[SQLException: Timed out waiting for a free available connection.]
[...]
Caused by: java.sql.SQLException: Timed out waiting for a free available connection.
    at com.jolbox.bonecp.DefaultConnectionStrategy.getConnectionInternal(DefaultConnectionStrategy.java:88) ~[bonecp.jar:na]
[...]

I have a play base controller trait for setting up the explicit session in one place

trait BaseController extends Controller with Secured {
    import play.api.Play.current
    implicit def session: SessionDef = play.api.db.slick.DB.withSession {
        implicit session => {session}
    }
}

Then there is the /list action with a simple call to a UserService using the explicit session:

object List extends BaseController with Secured {
  def index = IsAuthenticated { username =>
    implicit request =>
    UserService.findByEmail(username).map { user =>
      Ok(views.html.List.index(user))
    }.getOrElse(Forbidden)
  }
}

I have removed all async actions so I don't think this question would be a duplicate of similar questions: Play slick and Async - is it a race condition? or Scala Play 2.2 Slick 1.0.1 - future { Try {...} } Timed out waiting for a free available connection

The issue occurs after reloading the action about 10 times.

Obviously I'm doing something wrong in the session handling of slick - are there any good examples of slick 2 session handling in playframework? What would be the best practice for that?

edit: One possible source of the issue might be an object that I use for holding the TableQueries

object Models {
    val users = TableQuery[Users]
    val mailinglists = TableQuery[Mailinglists]
    val mailinglistMemberships = TableQuery[MailinglistMemberships]
}

If that may be the source of the problem, what would be a good place to put these references? The main reason for the object is to reference these instances in the foreign keys of the table definition (similar to http://slick.typesafe.com/doc/2.0.1/schemas.html#constraints)

edit: Here's the code for findByEmail - but I don't think it really matters. It seems to be the same behavior and problem in all queries.

def findByEmail(email: String)(implicit session: Session): Option[User]
  = Models.users.filter(_.email === email).firstOption

I'd be thankful for any hint into the right direction.

Answer 1

I have also faced same problem.It seems like application not getting connection from connection pooling.so i have used configuration:

db.default.partitionCount=1  
db.default.maxConnectionsPerPartition=20 
db.default.minConnectionsPerPartition=10 
db.default.acquireIncrement=1    
db.default.acquireRetryAttempts=5 
db.default.acquireRetryDelay=5 seconds 
db.default.acquireRetryDelay=5 seconds
db.default.idleMaxAge=10 minute 
db.default.idleConnectionTestPeriod=5 minutes 
db.default.initSQL="SELECT 1" 
db.default.maxConnectionAge=1 hour

then never get error(Timed out waiting for a free available connection).(my application deployed on heroku with postgres database)

Answer 2

The problem was in the implicit session definition in the BaseController trait. It seems that the session definition needs to be on the Action level and not on the controller level - unfortunately I can't explain the reason for that (yet).

trait BaseController extends Controller with Secured {
    //REMOVED this code
    //import play.api.Play.current
    //implicit def session: SessionDef = play.api.db.slick.DB.withSession {
    //    implicit session => {session}
    //}
}

Updated controller action using DbAction helped - not resulting in connection timeouts.

trait Application extends Controller {
  def index = DBAction { implicit request =>
    request.session.get("email") flatMap (email => UserService.findByEmail(email)) map { user =>
      Ok(views.html.index(user))
    } getOrElse {
      Ok(views.html.index(null))
    }
  }
}

object Application extends Controller with Application

So what is still missing is mixing an authenticated action with the DBAction of play-slick. The very same question has been asked here: compose slick dbaction with authenticated action

Maybe someone of the slick/play-slick team can step in and answer these questions?