CODEWITHSUNDEEP
javaoracle-databasejdbchikaricp
AreXe
AreXe

Reputation: 13

HikariCP not cleaning/using first Oracle db session after internet reconnect

I have a problem with HikariCP connections to Oracle database when using desktop JavaFX application.

Stack:

Java 8 / JavaFX 8
HikariCP 3.4.2
Oracle Database 11g Release 2
DB driver: ojdbc8 19.3.0.0

When WiFi internet connection is lost when trying to make db query, Hikari states that:

11:09:50.551 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase: HikariPool-1 - 
    Closing connection oracle.jdbc.driver.T4CConnection@175d7635: (connection is dead)
    11:10:15.513 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.pool.HikariPool: HikariPool-1 - Timeout failure stats (total=0, active=0, idle=0, waiting=0)
    java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30002ms.
11:10:19.606 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool: HikariPool-1 - Pool stats (total=0, active=0, idle=0, waiting=0)
11:10:32.569 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool: HikariPool-1 - Cannot acquire connection from data source

And after WiFi reconnect it's not using the same connection, session on Oracle database is alive (not closed cause of lost internet), but creates new connection:

11:10:48.320 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool: HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@20c1491b
11:10:49.608 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool: HikariPool-1 - Pool stats (total=1, active=0, idle=1, waiting=0)

So Hikari creates new connection and in Oracle I see 2 sessions of same application. The first session doesn't clean after closing the JavaFX application. The second is cleaned on exit. What can I do to clean first session? Is this Hikari problem or db problem?

This is the Hikari config, mainly default:

11:09:17.754 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: HikariPool-1 - configuration:
11:09:17.759 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: allowPoolSuspension.............false
11:09:17.759 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: autoCommit......................true
11:09:17.760 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: catalog.........................none
11:09:17.760 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: connectionInitSql...............none
11:09:17.760 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: connectionTestQuery.............none
11:09:17.760 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: connectionTimeout...............30000
11:09:17.760 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: dataSource......................none
11:09:17.761 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: dataSourceClassName.............none
11:09:17.761 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: dataSourceJNDI..................none
11:09:17.761 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: dataSourceProperties............{password=<masked>, prepStmtCacheSqlLimit=2048, cachePrepStmts=true, prepStmtCacheSize=250}
11:09:17.762 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: driverClassName.................none
11:09:17.762 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: healthCheckProperties...........{}
11:09:17.762 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: healthCheckRegistry.............none
11:09:17.762 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: idleTimeout.....................600000
11:09:17.762 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: initializationFailTimeout.......1
11:09:17.763 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: isolateInternalQueries..........false
11:09:17.763 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: jdbcUrl.........................jdbc:oracle:thin:@xxx
11:09:17.763 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: leakDetectionThreshold..........20000
11:09:17.763 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: maxLifetime.....................1800000
11:09:17.763 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: maximumPoolSize.................1
11:09:17.763 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: metricRegistry..................none
11:09:17.764 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: metricsTrackerFactory...........none
11:09:17.764 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: minimumIdle.....................1
11:09:17.764 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: password........................<masked>
11:09:17.764 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: poolName........................"HikariPool-1"
11:09:17.764 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: readOnly........................false
11:09:17.764 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: registerMbeans..................false
11:09:17.764 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: scheduledExecutor...............none
11:09:17.765 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: schema..........................none
11:09:17.765 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: threadFactory...................internal
11:09:17.765 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: transactionIsolation............default
11:09:17.765 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: username........................"xxx"
11:09:17.765 DEBUG [JavaFX Application Thread] com.zaxxer.hikari.HikariConfig: validationTimeout...............5000

And every connection within Hikari is created using try-with-resources block, for example:

 String getValueFromDatabase(String sql) {
        String str = "";
        try (final Connection con = DataSource.getConnection();
             final PreparedStatement stm = createStatement(con, sql);
             final ResultSet rs = stm.executeQuery()) {
            final ResultSetMetaData md = rs.getMetaData();
            while (rs.next()) {
                str = Optional.ofNullable(rs.getString(1)).orElse("");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return str;
    }

I'm doing this too to check if internet is still available (WiFi can be lost) and show popup message when connection failed (for example DataSource.getConnection().isClosed()?).

Upvotes: 1

Views: 2628

Answers (1)

pmdba
pmdba

Reputation: 7043

The original session has been orphaned. It will remain visible in the database, but not consuming resources, until it is explicitly killed or timed out by it's profile. Essentially it is waiting for the original client to send another network packet to it, just so that it can inform the client that the session is dead, and then exit. The second session exits cleanly because your application issues an explicit logoff command. The DBA needs to configure the user's profile to limit idle time and clean up the session. See here:

https://oracle-base.com/articles/misc/clearing-down-old-database-sessions

Upvotes: 1

Related Questions