Connection leaks on SQL components using IBM DB2 database

[dpalinic]

I observed connection leaks when using SQL component using Karavan 4.4.0.

How to reproduce:

1. Create integration which has SQL component as a source
2. Leave default settings for everything, but add DataSource
3. Observe connection errors in logs after 50-10 minutes of work

Error:
2024-04-03 09:36:24.743 WARN 15 --- [l-1 housekeeper] om.zaxxer.hikari.pool.ProxyLeakTask : Connection leak detection triggered for com.ibm.db2.jcc.t4.b@5fc3fcb8 on thread Camel (camel-1) thread #5 - sql://SELECT%20*%20FROM%20SIVDB.FHOPEHS%20LIMIT%2050;, stack trace follows java.lang.Exception: Apparent connection leak detected at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:127) ~[HikariCP-5.1.0.jar:?] at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:160) ~[spring-jdbc-6.1.3.jar:6.1.3] at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:118) ~[spring-jdbc-6.1.3.jar:6.1.3] at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) ~[spring-jdbc-6.1.3.jar:6.1.3] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:653) ~[spring-jdbc-6.1.3.jar:6.1.3] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:701) ~[spring-jdbc-6.1.3.jar:6.1.3] at org.apache.camel.component.sql.SqlConsumer.poll(SqlConsumer.java:204) ~[camel-sql-4.4.0.jar:4.4.0] at org.apache.camel.support.ScheduledPollConsumer.doRun(ScheduledPollConsumer.java:204) ~[camel-support-4.4.0.jar:4.4.0] at org.apache.camel.support.ScheduledPollConsumer.run(ScheduledPollConsumer.java:118) ~[camel-support-4.4.0.jar:4.4.0] at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?] at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) [?:?] at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) [?:?] at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?] at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?] at java.base/java.lang.Thread.run(Thread.java:840) [?:?]

Integration which we used for confirming the issue:
connection-leaks.zip

Additional notes:

• We confirmed this against DB2 database
• We confirmed this with both HikariCP and Apache BasicDataSource
• We confirmed this issue exists without using useIterator option

[davsclaus]

You set a very low connection timeout of only 1s (1000 millis). The default value in Hikari is 30s (30000)
And as such I think its a false warning.

private static final long CONNECTION_TIMEOUT = SECONDS.toMillis(30);

The camel-sql component uses Spring JDBC that does all the connection acquire/release to the datasource, and that is known to be working fine world-wide.

[dpalinic]

Hi @davsclaus , that's just a leftover from my debugging. Sorry about that. If I remove that parameter and leave connection timeout to be default one, the same things are happening. I've just re-run my integration without that parameter, and I've got the same exception. Sometimes it appears after a minute, sometimes after 10 minutes... but it's always there.

I've also tried to create an integration in another way, to have Scheduler component as a starting point, and then SQL component to be invoked inside of it, but same issues are happening.

[davsclaus]

okay I dont have a DB2 datbase, and would need to have some other kind of database. I am not sure if you can try to put together with another database - for example some basic database from a public docker image, you can run via docker, and try. I don't have much time this and next week for tracking this down.

[dpalinic]

Unfortunately I couldn't reproduce this with some other database, but only with DB2. Maybe this DB2 docker compose file can be used for testing: https://github.com/gaerfield/db2-docker/blob/master/docker-compose.yml

[davsclaus]

moving to a dicusssion as this is not karavan bug/issue - but pure camel / spring-jdbc etc

[davsclaus]

ok good to know its a db2 driver issue - maybe its some known problem and you need to turn on some special option with db2 etc.

[davsclaus]

https://stackoverflow.com/questions/59087809/hikaricp-is-not-able-to-connect-to-db2-database#59087904

Also try to change the driverClassName to what is suggested in that link above. And it may be that you need to configure some kind of isValid SQL query for DB2, for it to check if a connection is valid or not.

In general this is a DB2 issue and not Karavan/Camel