Hikari timeout oracle springboot

Question

Hi, I’m trying to manually set up a connection pool with Hikari. I will need to do manual because the application will have its dynamic settings for connection to the databases and schemas/Sid.

How I’m setting up the connections:

    HikariConfig configHikari = new HikariConfig();
    configHikari.setUsername("username");
    configHikari.setPassword("password");
    configHikari.setJdbcUrl("url");
    configHikari.setDriverClassName("driver");
    HikariDataSource dataSource = new HikariDataSource(configHikari);

I am managing to use the connections, but after some time, I have the following error below:

16:31:44.044  WARN          com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection oracle.jdbc.driver.T4CConnection@5111dd1a (IO Error: Invalid Operation, NOT Connected). Possibly consider using a shorter maxLifetime value.
16:31:49.049  WARN          com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection oracle.jdbc.driver.T4CConnection@703110b7 (IO Error: Invalid Operation, NOT Connected). Possibly consider using a shorter maxLifetime value.
16:31:54.054  WARN          com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection oracle.jdbc.driver.T4CConnection@555f60aa (IO Error: Invalid Operation, NOT Connected). Possibly consider using a shorter maxLifetime value.

org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30170ms.
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)

I tried to increase the max-life-time of Hikari, but after a while I have the same mistake.

 configHikari.setMaxLifetime(600000);

What do I need to set up for the connection to reconnect? Is there any infinite max-life-time? and is it healthy to have a very large max-life-time? I have not yet tested in the other banks to see if I am having the same error, for now I tested only in oracle and already got this problem.

And right now I have to be re-starting the app when that time wins.

Answer 1

From what I noticed your bank is oracle.

When this problem is generated, it is related to maxTimeLife and database wait_timeout.

The generated WARN is because maxTimeLife is high (you said yourself you are trying to put a high value) and is higher than the database wait_timeout.

See what it says to documentation:

This Property Controls the Maximum Lifetime of a Connection in the pool. An in-use Connection will Never be Retired, only when it is closed will it then be Removed. On a Connection-by-Connection Basis, minor Negative attenuation is Applied to avoid mass-Extinction in the pool. We strongly recommend Setting this value, and it should be several Seconds Shorter than any database or Infrastructure imposed Connection time limit. A value of 0 indicates no Maximum Lifetime (Infinite Lifetime), Subject of Course to the idleTimeout Setting. The minimum allowed value is 30000ms (30 Seconds). Default: 1800000 (30 minutes)

By what I searched, by default Oracle has not set a value for wait_timeout, which I found strange. There is no setting point for this.

But it has a data associated with the user profile of the bank, which can influence this. It is the parameter IDLE_TIME.

Based in that reply you can consult this with:

SELECT p.limit
FROM dba_profiles p, dba_users u
WHERE p.resource_name = 'IDLE_TIME' and p.profile = u.profile and u.username = '...';

See if this returns anything.

From what I understand, if the maxLifeTime is less than the correct bank parameter, when this time is reached naturally Hiraki recreates connections naturally.

I hope I contributed, even though I felt I helped little.