We have a Spring Boot application, using HikariCP as connection pool to an Oracle 12.2 database. If there is an issue and the database becomes unavailable, we get this exception, as expected:
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not
available, request timed out after 30000ms.
The problem comes once the database is restarted and working again. According to every single post I've read 1 2 3, HikariCP should automatically reconnect and start working again. And it does so, in our testing environment. However, in the production server, it keeps throwing the same exception until we restart the application.
The exception thrown is always the same in production: Connection is not available, request timed out after 30000ms. However, that's not the case in our testing environment: we get that exception only the first time, subsequent attempts throw java.net.ConnectException: Connection timed out: connect instead. Which seems to suggest that HikariCP has actually identified "there is a problem with this connection, I should drop it and open a new one", unlike in production, where it keeps trying to use the same dead one. Is that assumption true?
What could be causing this behavior? We are unable to replicate it in our testing environment, and everything (software versions, Spring settings...) is the same as in production.
Additional info: the application is written in Java 8 with Spring Boot 2.2.0, and runs in an Apache Tomcat 9.0 server. We use the default connection pool, HikariCP 3.4.1, and the default settings, we have only configured the datasource, like so:
spring.datasource.username = xxx
spring.datasource.password = xxx
spring.datasource.url = jdbc:oracle:thin:@xxx
spring.datasource.driver-class-name = oracle.jdbc.OracleDriver
Our datasource (and almost everything else) is managed by Spring's autoconfiguration:
@SpringBootApplication
@EntityScan(basePackageClasses = { BaseEntity.class })
@EnableJpaRepositories(basePackages = "foo.model",
repositoryBaseClass = SimpleJpaRepository.class)
@EnableJpaAuditing
And that's it, then we have a bunch of repository interfaces extending JpaRepository. We never deal directly with the datasource, the JDBC template or anything like that, we rely on Spring Data's SimpleJpaRepository.
The database driver is Oracle's ojdbc8, version 12.2.0.1. I have checked that it's JDBC4 compliant and it implements the Connection.isValid() method, so there shouldn't be need to define a "connection test query".
Also, HikariCP doesn't need or even support the "test on borrow" option, which seems to be the way to enable automatic reconnection in other connection pools such as the Tomcat one.
In fact, we know the combination of HikariCP and ojdbc8 should automatically reconnect out-of-the-box, because... well, it does, in our testing environment.
