Утечка соединения, вызывающая исключение java.sql.SQLNonTransientConnectionException ⇐ JAVA

[Гость]

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 keys = null; public SqlDataConnector(Map attributes, String driver) { DataSource ds = createDataSource(attributes, driver); jdbc = new JdbcTemplate(ds); } @Override public List 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 performExtraction() { DataConnector connector = connectorFactory.getConnector(task.getConnectors().get(0)); List 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.


Источник: https://stackoverflow.com/questions/780 ... nexception