CODEWITHSUNDEEP
javavaadinhikaricp
JRojo
JRojo

Reputation: 11

HikariCP detects connectionleak even though all of my code has a call to close method

I am using HikariCP in our web application and all of a sudden, I am getting this exception:

    [Hikari housekeeper (pool HikariPool-0)] WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for connection com.mysql.cj.jdbc.ConnectionImpl@3b82d04f, stack trace follows

java.lang.Exception: Apparent connection leak detected

I already checked and made sure all my sql codes has a call to close method.

Here's my HikariConfig : 

    private HikariConfig hikariConfig() {
    HikariConfig config = new HikariConfig();
    config.setDriverClassName(CLASS_FOR_NAME);
    config.setJdbcUrl(HOST);
    config.setUsername(USER);
    config.setPassword(PASS);
    config.addDataSourceProperty("cachePrepStmts", "true");
    config.addDataSourceProperty("prepStmtCacheSize", "250");
    config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    config.setLeakDetectionThreshold(TimeUnit.SECONDS.toMillis(30));
    config.setValidationTimeout(TimeUnit.MINUTES.toMillis(1));
    config.setMaximumPoolSize(40);
    config.setMinimumIdle(0);
    config.setMaxLifetime(TimeUnit.MINUTES.toMillis(2)); // 120 seconds max life time
    config.setIdleTimeout(TimeUnit.MINUTES.toMillis(1)); // minutes
    config.setConnectionTimeout(TimeUnit.MINUTES.toMillis(5)); // millis
    config.setConnectionTestQuery("/* ping */ SELECT 1");

    return config;
}

here's how my queries look like : 

   public ArrayList<LocationType> getLocationTypes() {
    ArrayList<LocationType> locationTypes = new ArrayList<>();
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet resultSet = null;

    try {
        connection = DataSource.getInstance().getConnection();

        pstmt = connection.prepareStatement("SELECT\n" +
                "  location_type_id,\n" +
                "  location_type_name\n" +
                "FROM tablename;");

        resultSet = pstmt.executeQuery();

        while (resultSet.next()) {
            locationTypes.add(new LocationType(resultSet.getInt("location_type_id"), resultSet.getString("location_type_name")));
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (resultSet != null)
            try {
                resultSet.close();
            } catch (SQLException e) {
            }

        if (pstmt != null)
            try {
                pstmt.close();
            } catch (SQLException e) {
            }

        if (connection != null)
            try {
                connection.close();
            } catch (SQLException e) {
            }
    }
    return locationTypes;
}

I've already tried increasing the connectionLeakDetection, max connection and minimum idle but none of that solved the issue.

I have read that it could be caused by the machine(low resources) and connections being closed, I think however, none of these causes the issue.

I noticed that some long queries in my code are now being detected as connection leak, even though I am not calling their methods. I hope you guys could help.

This is the stack trace : 

    at com..database.DataSource.getConnection(DataSource.java:148)
at com.database.databaseServices.MileageReportService.MileageService.getMonthlySummaryBySID(MileageService.java:27)
at com.views.reports.mileage.MileageReport.lambda$generateReport$61446b05$1(MileageReport.java:103)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)

The only line this line - MileageService.java:27, is my query to the DB and it has a close statement in its finally call.

Upvotes: 1

Views: 7838

Answers (1)

Nitin
Nitin

Reputation: 1660

Upgrade to latest HikariCP.

HikariCP reports connection as leak because time taken from borrowing to close is longer than specified LeakDetectionThreshold.

Version 2.4.9 and later, it logs if connection returns after being reported as leak.

Also, looking at your config, I would tweak properties as follows (first 4 to 1 minute and last to 5):

config.setLeakDetectionThreshold(TimeUnit.MINUTES.toMillis(1));
config.setConnectionTimeout(TimeUnit.MINUTES.toMillis(1));
config.setValidationTimeout(TimeUnit.MINUTES.toMillis(1));
config.setIdleTimeout(TimeUnit.MINUTES.toMillis(1));

config.setMaxLifetime(TimeUnit.MINUTES.toMillis(5));

HTH

Upvotes: 1

Related Questions