how does Hikari recycle connections?

Question

I please you all for an answer I could not find in the HikariCP doc. Given I set the following pool parameters:

minimumIdle 1
idleTimeout 10 minutes
maxLifeTime 20 minutes

When my app stays idle (with nobody making requests) during the night, I expect Hikari to close each connection 10 minutes after the connection's last request, after the last connection being closed create a new one (and hold it in the pool), and then close and re-create this idle connection every 20 minutes. Did I understood correctly?

The fact is that after an idle period on my app, I see (uppon the next request) the following exception:

WARN  c.z.hikari.proxy.ConnectionProxy - Connection oracle.jdbc.driver.T4CConnection@3c63f80e <POOL_NAME> marked as broken because of SQLSTATE(08003), ErrorCode(17008).
java.sql.SQLRecoverableException: Closed Connection

The connection has probably been closed by Oracle and cannot be used. I'll try to avoid this using the above config. Another point is that I do not understand why I get a closed connection from the pool. This should never happen, as Hikari does test the connection's state before returning it...

Note: I am not the owner of the DB, I cannot configure it or let it be reconfigured to suit my needs. I also have no access to its config.

Our set-up is Spring 4.1.6, Hibernate 4.3.7 with JPA 2.1 API, Hikari 2.1.0

Solution

Your understanding of the pool parameters is correct. It is possible that the Oracle instance has a shorter idle timeout than 10 minutes. One thing you can do is to enable DEBUG level logging for the com.zaxxer.hikari package. You'll get a lot more information about what is going on internally within HikariCP, without being too noisy.

Feel free to post the log as an issue on Github and we'll take a look at it.

Comments

[Gaël Oberson]

Ok, I'll see if I can get useful logs. What is your opinion about hikari returning me a closed Connection? Since I'm on an Oracle 11g using theyr last JDBC Driver, isValid() should work. I see this big lovely SQLException when Hibernate tries to execute a query on the closed connection. So I definitively get a closed Connection from the pool.

[brettw]

I was pretty sure the Oracle driver properly implemented the JDBC4 isAlive() API, but you might try configuring a connectionTestQuery instead. HikariCP will use that instead, if configured. Note: Also make sure that Spring and/or Hibernate is not caching Connections in the session (somehow) -- possibly a setting.

[Gaël Oberson]

I got more info. It appears that we have a connection leak at the application startup. Even before the first request resulting of user interaction. So we have to investigate this. I will inform you further when I have more info. Thanks to hikari datasource statistics logging. :-)

[brettw]

You might try leakDetectionThreshold to pinpoint the leak source.

[ddb]

please, enhance your answer by adding more info