Squeryl 0.9.5 (with Lift 2.4) not releasing database connections/pools

Question

Following the recommended transaction setup for Squeryl, in my Boot.scala:

import net.liftweb.squerylrecord.SquerylRecord
import org.squeryl.Session
import org.squeryl.adapters.H2Adapter

SquerylRecord.initWithSquerylSession(Session.create(
  DriverManager.getConnection("jdbc:h2:lift_proto.db;DB_CLOSE_DELAY=-1", "sa", ""),
  new H2Adapter
))

The first startup works fine. I can connect via H2's web-interface and if I use my app, it updates the database appropriately. However if I restart jetty without restarting the JVM, I get:

java.sql.SQLException: No suitable driver found for jdbc:h2:lift_proto.db;DB_CLOSE_DELAY=-1

The same result is had if I replace "DB_CLOSE_DELAY=-1" with "AUTO_SERVER=TRUE", or remove it entirely.

Following the recommendations on the Squeryl list, I tried C3P0:

import com.mchange.v2.c3p0.ComboPooledDataSource

val cpds = new ComboPooledDataSource 
cpds.setDriverClass("org.h2.Driver") 
cpds.setJdbcUrl("jdbc:h2:lift_proto") 
cpds.setUser("sa") 
cpds.setPassword("")
org.squeryl.SessionFactory.concreteFactory =
  Some(() => Session.create( 
    cpds.getConnection, new H2Adapter())
  )

This produces similar behavior:

WARNING: A C3P0Registry mbean is already registered. This probably means that an application using c3p0 was undeployed, but not all PooledDataSources were closed prior to undeployment. This may lead to resource leaks over time. Please take care to close all PooledDataSources.

To be sure it wasn't anything I was doing which was causing this, I started and stopped the server without calling a transaction { } block. No exceptions were thrown. I then added to my Boot.scala:

transaction { /* Do nothing */ }

And the exception was once again thrown (I'm assuming because connections are lazy). So I moved the db initialization code to its own file away from Lift:

SessionFactory.concreteFactory = Some(()=>
  Session.create(
  java.sql.DriverManager.getConnection("jdbc:h2:mem:test", "sa", ""),
  new H2Adapter
))
transaction {}

Results were unchanged. What am I doing wrong? I cannot find any mention of needing to explicitly close connections or sessions in the Squeryl documentation, and this is my first time using JDBC.

I found mention of the same issue here on the Lift google group, but no resolution.

Thanks for any help.

Answer 1

When you say you are restarting Jetty, I think what you're actually doing is reloading your webapp within Jetty. Neither the h2 database or C3P0 will automatically shut down when your app reloads, which explains the errors you are receiving when Lift tries to initialize them a second time. You don't see the error when you don't create a transaction block because both h2 and C3P0 are initialized when the first DB connection is retrieved.

I tend to use BoneCP as a connection pool myself. You can configure the minimum number of pooled connections to be > 1, which will stop h2 from shutting down without the need for DB_CLOSE_DELAY=-1. Then you can use:

    LiftRules.unloadHooks append { () =>
      yourPool.close() //should destroy the pool and it's associated threads
    }

That will close all of the connections when Lift is shutdown, which should properly shutdown the h2 database as well.