Connection leak on execute (Hikari)

[DeMol-EE]

I'm using rxkotlin-jdbc and a Hikari data source in a TornadoFX project and found that a connection is leaking whenever I use fun DataSource.execute to delete objects from an underlying PostgreSQL database. I came across this by enabling logging with the JVM parameter -Dorg.slf4j.simpleLogger.log.com.zaxxer.hikari=debug and setting the hikari config's leakDetectionThreshold property to 60_000 ms, which showed the following output in my console:

[HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
[HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=1, idle=9, waiting=0)
[HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=1, idle=9, waiting=0)
[HikariPool-1 housekeeper] WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for org.postgresql.jdbc.PgConnection@3fd29f40 on thread tornadofx-thread-2, stack trace follows
java.lang.Exception: Apparent connection leak detected
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
	at org.nield.rxkotlinjdbc.DatasourceKt$execute$1.invoke(datasource.kt:78)
	at org.nield.rxkotlinjdbc.DatasourceKt$execute$1.invoke(datasource.kt)
	at org.nield.rxkotlinjdbc.PreparedStatementBuilder.toPreparedStatement(PreparedStatementBuilder.kt:80)
	at org.nield.rxkotlinjdbc.UpdateOperation$toSingle$1.call(UpdateOperation.kt:38)
	at org.nield.rxkotlinjdbc.UpdateOperation$toSingle$1.call(UpdateOperation.kt:6)
	at io.reactivex.internal.operators.single.SingleDefer.subscribeActual(SingleDefer.java:36)
	at io.reactivex.Single.subscribe(Single.java:3394)
	at io.reactivex.Single.subscribe(Single.java:3380)
	at <<my tornadofx view class>>$deleteSelected$1.invoke(<<my tornadofx view class>>:157)
	at <<my tornadofx view class>>$deleteSelected$1.invoke(<<my tornadofx view class>>:17)
	at tornadofx.FXTask.call(Async.kt:459)
	at javafx.concurrent.Task$TaskCallable.call(Task.java:1423)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
o

The specific function in violation:

fun deleteRegistrations(registrations: List<Registration>, idexam: Int) = db.execute(
    "DELETE FROM registered WHERE idexam = ? AND code in (${registrations.joinToString { "?" }});").parameter(
    idexam).parameters(*registrations.map {
    it.code
}.toTypedArray()).toSingle()

jdk: 1.8.0_144
kotlin version: 1.3.10
rxkotlinfx maven version: 2.2.2
hikaricp maven version: 3.2.0

Not sure this is a bug of Hikari or rxkotlin-jdbc, but I found that swapping the execute to insert (and toSingle() to toSingle{true}) fixes the leak. Seems like the connection is not being closed and returned to the pool correctly?

[DeMol-EE]

In follow up I'd like to add I've discovered another situation in which a connection is leaked, namely on db.execute and db.insert when the query results in a SQLException. I will try to upload a MVC example soon.

[thomasnield]

Okay, I'm on vacation but I'll look at it when I get back in a few days. If you can please confirm it's not an issue with Hikari or the MySQL driver and that it is RxKotlin-JDBC that is the source of the issue

[thomasnield]

Hmm... yes there is something that doesn't look right here when comparing the implementation of insert() and execute(). The insert() relies on the iterator of toObservable() to close the connection whereas the execute() does nothing with the autoClose argument. I'll take care of this.

https://github.com/thomasnield/rxkotlin-jdbc/blob/master/src/main/kotlin/org/nield/rxkotlinjdbc/UpdateOperation.kt#L37-L39

https://github.com/thomasnield/rxkotlin-jdbc/blob/master/src/main/kotlin/org/nield/rxkotlinjdbc/InsertOperation.kt#L53-L55

[thomasnield]

@Warkst Alright, I implemented a fix. Can you test a build? You can use JitPack to bring this in as a dependency:
https://jitpack.io/#thomasnield/rxkotlin-jdbc/596332118a

[DeMol-EE]

Thanks @thomasnield for looking into this. I'm still experiencing connection leaks when something like

    db.execute("some pre-built insert statement").toSingle().blockingGet()

results in an exception.

[sslavian812]

Hi!
Has this issue been resolved yet?

[utshavtimsina]

Any updates on this??