Force Hikari/Hibernate to close stale (leaked?) connections

Question

I'm working with a FileMaker 16 datasource through the official JDBC driver in Spring Boot 2 with Hibernate 5.3 and Hikari 2.7.

The FileMaker server performance is poor, a SQL query execution time can reach a minute for big tables. Sometimes it results in connection leaking, when the connection pool is full of active connections which are never released.

The question is how to force active connections in the pool which have been hanging there say for two minutes to close, moving them to idle and making available for using again.

As an example, I'm accessing the FileMaker datasource through a RestController using the findAll method in org.springframework.data.repository.PagingAndSortingRepository:

@RestController
public class PatientController {

    @Autowired
    private PatientRepository repository;

    @GetMapping("/patients")
    public Page<Patient> find(Pageable pageable) {
        return repository.findAll(pageable);
    }
}

Calling /patients a few times in a raw causes connection leaking, here's what Hikari reports:

2018-09-20 13:49:00.939 DEBUG 1 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Pool stats (total=10, active=10, idle=0, waiting=2)

It also throws exceptions like this:

java.lang.Exception: Apparent connection leak detected
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-2.7.9.jar!/:na]

What I need is if repository.findAll takes more than N seconds, the connection must be killed and the controller method must throw and exception. How to achieve it?

Here's my Hikari config:

 allowPoolSuspension.............false
 autoCommit......................true
 catalog.........................none
 connectionInitSql...............none
 connectionTestQuery............."SELECT COUNT(*) FROM Clinics"
 connectionTimeout...............30000
 dataSource......................none
 dataSourceClassName.............none
 dataSourceJNDI..................none
 dataSourceProperties............{password=<masked>}
 driverClassName................."com.filemaker.jdbc.Driver"
 healthCheckProperties...........{}
 healthCheckRegistry.............none
 idleTimeout.....................600000
 initializationFailFast..........true
 initializationFailTimeout.......1
 isolateInternalQueries..........false
 jdbc4ConnectionTest.............false
 jdbcUrl.........................jdbc:filemaker://***:2399/ec_data
 leakDetectionThreshold..........90000
 maxLifetime.....................1800000
 maximumPoolSize.................10
 metricRegistry..................none
 metricsTrackerFactory...........none
 minimumIdle.....................10
 password........................<masked>
 poolName........................"HikariPool-1"
 readOnly........................false
 registerMbeans..................false
 scheduledExecutor...............none
 scheduledExecutorService........internal
 schema..........................none
 threadFactory...................internal
 transactionIsolation............default
 username........................"CHC"
 validationTimeout...............5000

score 3 · Answer 1 · edited Sep 21 '18 at 07:03

HikariCP focuses on just connection pool management to managing the connections that it has formed from it.

loginTimeout - how long HikariCP will wait for a connection to be formed to the database (basically a JDBC connection)

spring.datasource.hikari.connectionTimeout=30000

maxLifetime - how long a connection will live in the pool before being closed

spring.datasource.hikari.maxLifetime=1800000

idleTimeout - how long an unused connection lives in the pool

spring.datasource.hikari.idleTimeout=30000

Use javax.persistence.query.timeout to cancel the request if it takes longer than defined timeout.

javax.persistence.query.timeout (Long – milliseconds)

The javax.persistence.query.timeout hint defines how long a query is allowed to run before it gets canceled. Hibernate doesn’t handle this timeout itself but provides it to the JDBC driver via the JDBC Statement.setTimeout method.

this particular JDBC driver ignores the query timeout parameter — super.t, Sep 21 '18 at 13:26

super.t · Accepted Answer · 2018-09-21T13:44:31.277

The filemaker JDBC driver ignores the javax.persistence.query.timeout parameter, even though the timeout value is set in the driver's implementation of the java.sql.setQueryTimeout setter. So I resolved the problem by extending the class com.filemaker.jdbc.Driver and overriding the connect method, so that it adds the sockettimeout parameter to the connection properties. Having this param in place, the FM JDBC driver interrupts the connection if no data have been coming from the socket for the timeout period.

I've also filed an issue with filemaker: https://community.filemaker.com/message/798471

Force Hikari/Hibernate to close stale (leaked?) connections

2 Answers2