Connection Leak

[sumeetpri]

I am using HikariCP-3.4.2 with viertix.io and scala monix.

Using Hikari connection pool is created like

def createDataSource(autoCommit: Boolean = false, connectionTimeout: Long = 60000,
                       idleTimeout: Long = 30000, maxLifetime: Long = 180000,
                       maxPoolSize: Int = 50, minIdlePoolSize: Int = 2, poolName: String = "Database Connection Pool"): HikariDataSource = {
    val hc = new HikariConfig()
    hc.setPoolName(poolName)
    hc.setConnectionTestQuery("select 1 from dummy")
    hc.setDriverClassName("com.mysql.jdbc.Driver")
    hc.setConnectionTimeout(connectionTimeout)
    hc.setIdleTimeout(idleTimeout)
    hc.setMaxLifetime(maxLifetime)
    hc.setMaximumPoolSize(maxPoolSize)
    hc.setMinimumIdle(minIdlePoolSize)
    hc.setJdbcUrl(host)
    hc.setUsername(user)
    hc.setPassword(password)
    hc.setAutoCommit(autoCommit)
    hc.setRegisterMbeans(true)
    hc.setLeakDetectionThreshold(40000);
    new HikariDataSource(hc)
  }

Data source pool is built like

val ds: HikariDataSource = HanaConnection.createDataSource(maxPoolSize = 40, poolName = "my-db-pool")

I have two different function called from api using vertx.io . noLeak function is getting the data source and building JDBC template and executes simple select statement . Jprofiler shows no connection leak . But another function build jdbctemplate using SingleConnectionDataSource , other function shows connection leak even i am closing the connection explicitly

def noLeak(router: Router)(implicit ds: HikariDataSource): Route ={
    implicit val jt: JdbcTemplate = new JdbcTemplate(ds)
    val route = router.route(HttpMethod.GET, "/noleak").handler(BodyHandler.create())
    route.handler(ResponseTimeHandler.create())
    route.handler(rc=>{
      rc.response.putHeader("content-type", "application/json")
      Task( jt.execute("SELECT *  FROM DUMMY") ).runAsync{
        result =>
          result match {
            case Left(value) => rc.response.end(value.getMessage)
            case Right(value) => rc.response.end("DONE")
          }
      }
    })
  }

  def leak(router: Router)(implicit ds: HikariDataSource): Route ={
    val con = ds.getConnection
    implicit val jt = new JdbcTemplate(new SingleConnectionDataSource(con, true))
    val route = router.route(HttpMethod.GET, "/leak").handler(BodyHandler.create())
    route.handler(ResponseTimeHandler.create())
    route.handler(rc=>{
      rc.response.putHeader("content-type", "application/json")
      Task(jt.execute("SELECT *  FROM DUMMY")).runAsync{
        result =>
          result match {
            case Left(value) => {
              con.close()
              rc.response.end(value.getMessage)
            }
            case Right(value) => {
              con.close()
              rc.response.end("DONE")
            }
          }
      }
    })
  }

Below is the screen shot of jprofiler connection leak , what mistake in second function is not closing connection even after explicit connection close.

[wanglunhui2012]

try to build a SingleConnectionDataSource object and call destroy() method finally ,your code may look like this:

Connection connection=dataSource.getConnection();
SingleConnectionDataSource singleConnectionDataSource=new SingleConnectionDataSource(connection,true);
JdbcTemplate jdbcTemplate=new JdbcTemplate(singleConnectionDataSource);
jdbcTemplate.execute("select * from user where id=1");
singleConnectionDataSource.destroy();

[sumeetpri]

Hi @wanglunhui2012 ,

I tried your idea of having singleConnectionDataSource.destroy() . It did not solve the problem as i could still able to see unclosed type in jProfile connection leak .

But when i tried with suppressClose = false , jProfile shows no unclosed in connection leak

SingleConnectionDataSource singleConnectionDataSource=new SingleConnectionDataSource(connection,false);

Why suppressClose = true is a problem and leak ?

[wanglunhui2012]

I think this problem is nothing to do with HikariCP,your usage of SingleConnectionDataSource is not correct.the official site of Spring,SingleConnectionDataSource indicate that the class will never close the connection.

I change the source code of SingleConnectionDataSource.destroy():

public void destroy() {
    synchronized(this.connectionMonitor) {
        this.closeConnection();
        // append start
        try {
            this.connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
       // append end
    }
}

the connect leak disappear.but this.connection.close(); is proxied and it just return null,do nothing.the sql show full processlist; shows that the connection num is minimum-idle we configure.i don't know how JProfile analysis the connection leak.