Connection leak causing java.sql.SQLNonTransientConnectionException

Question

I am developing an ETL application which should periodically. For testing purposes I have set the period to 5 minutes but in production it will be bigger. I am using Spring framework for both scheduling and JdbcTemplate to perform extract and load steps. Right now I have everything in MySQL database on my laptop, but eventually there will be two separate databases (MySQL and DB2) for extraction and Snowflake for loading. I have created the following class:

public class SqlDataConnector implements DataConnector {

    private final JdbcTemplate jdbc;
    private List<String> keys = null;

    public SqlDataConnector(Map<String, String> attributes, String driver) {
        DataSource ds = createDataSource(attributes, driver);
        jdbc = new JdbcTemplate(ds);
    }

    @Override
    public List<Map<String, Object>> getData(String query) {
        return jdbc.queryForList(expression);
    }

    @Override
    public void close() {
        if (jdbc != null) {
            DataSource dataSource = jdbc.getDataSource();
            if (dataSource != null) {
                Connection connection = null;
                try {
                    connection = dataSource.getConnection();
                } catch (SQLException se) {
                } finally {
                    if (connection != null) {
                        try {
                            connection.close();
                        } catch (SQLException ex) {
                        }
                    }
                }
            }
        }
    }
    ....
}

I am closing connection after every step. Like this:

private List<Map<String, Object>> performExtraction() {
    DataConnector connector = connectorFactory.getConnector(task.getConnectors().get(0));
    List<Map<String, Object>> data = connector.getData(task.getExpression());
    connector.close();
    return data;
}

My code is working as expected, however everything is fine for the 4 iterations. At the beginning of the fifth one I am getting the following exception:

2024-03-02 21:27:36.169 [ETL] [pool-6-thread-1] [ERROR] com.zaxxer.hikari.pool.HikariPool : HikariPool-17 - Exception during pool initialization. java.sql.SQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections" at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828) at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:448) at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241) at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198) at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359) at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201) at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470) at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:100) at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:160) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:118) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388)

My connection properties are following:

spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.pool-name="etl-daemon-pool"
spring.datasource.hikari.minimumIdle=2
spring.datasource.hikari.maximumPoolSize=4
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=60000

Apparently, I have a connection leak somewhere, but cannot find where it is. I did go through several SO posting on this topic and tried to follow suggestions there, but nothing works. I did put logging statement and saw that connection was successfully closed there. Cannot figure out why these connections are not released. If someone help me fix this problem, I will greatly appreciate it.

Answer 1

I don't know if this will solve your problem¹, but there is a potential leak here:

private List<Map<String, Object>> performExtraction() {
    DataConnector connector = 
         connectorFactory.getConnector(task.getConnectors().get(0));
    List<Map<String, Object>> data = connector.getData(task.getExpression());
    connector.close();
    return data;
}

If an exception is thrown while you are getting the data, the connector close call won't happen. You should write it like this:

private List<Map<String, Object>> performExtraction() {
    try (DataConnector connector =
        connectorFactory.getConnector(task.getConnectors().get(0));
    ) {
        List<Map<String, Object>> data = 
            connector.getData(task.getExpression());
        return data;
    }
}

And your SqlDataConnector class should implement AutoCloseable.

---

^{1 - Specifically, I don't know if you are getting exceptions in the performExtraction method. The problem could be somewhere else in your codebase.}