Hikari CP Connection Recovery Problem After DB Outage

[andrea-silva]

Context:

1. spring_boot_version = 3.2.0
2. ojdbc8 version = 21.11.0.0
3. oracle version = Oracle Database Enterprise Edition Release 19.0.0.0.0
4. HikaryCP = 5.0.1

The following happened in a few of our test environments (but I cannot reproduce it locally).

After an outage of the DB, the connection pool was not able to recover when the DB was up and running again. After the spring application is restarted the problem is solved, but a restart should not be necessary in this scenario.

The exception (happening over and over again until the Spring application is restarted) is:

When I reproduce the scenario locally (stopping and restarting the DB), the datasource recovers after the DB is restarted.

The problem is the same as described in https://stackoverflow.com/questions/69566390/hikari-cp-spring-boot-connection-recovery-problem-after-db-failure

[KazzmanK]

We have same issue, triggered by DB outage. https://github.com/brettwooldridge/HikariCP/issues/2161#issuecomment-1985831188

[Donghh0221]

Have you tried setting the socket read timeout for Oracle JDBC? If you don't set the Read Timeout, Oracle's default socket read timeout is null. This means that after a Tomcat thread borrows a connection from the Hikari connection pool, it could infinitely wait for the query result without recovering, after db outage happened.

Refer to this and the HikariCP Rapid Recovery guide: https://github.com/brettwooldridge/HikariCP/wiki/Rapid-Recovery

You can set up your configuration for HikariCP like this:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:oracle:thin:@//host:port/serviceName?oracle.net.CONNECT_TIMEOUT=5000&oracle.jdbc.ReadTimeout=30000");
config.setUsername("username");
config.setPassword("password");

HikariDataSource dataSource = new HikariDataSource(config);

Try setting up hikariCP configruation like this.