connection leak with hikari and multithreading

[hafarooki]

I'm not 100% sure what the cause is here. It seems to be leaking connections through simple transactions.

First, Hikari warns about it: https://gist.github.com/Miclebrick/2771678343cea9cf5be56990a0ab9077

Then anything trying to access the database times out: https://gist.github.com/Miclebrick/401c91dfbddca4b7cdbdf88d9dd76b5c

I'm not really sure where the problem lies on this one.

        transaction {
            val updated: Int = SLPlayers.update({ SLPlayers.id eq uuid }) {
                it[this.lastKnownName] = name
                it[this.lastSeen] = System.currentTimeMillis()
            }

            if (updated == 0) SLPlayers.insert {
                it[this.id] = EntityID(uuid, SLPlayers)
                it[this.lastKnownName] = name
                it[this.lastSeen] = System.currentTimeMillis()
            }
        }

that's where the connection leak warning is triggered (also, does exposed let you update if present else insert somehow? I did it that way as a workaround when I first started using it.)

[Tapac]

What database do you use? Do you have concurrent requests or that issue happens in a single thread?

Check this part of FAQ about upsert.

[hafarooki]

I'm using MySQL, the database stuff happens concurrently so as to not block the main game thread. I figured it'd be thread-safe using Hikari as a data source?

Thanks for the link to the upsert page

[hafarooki]

it might not actually be an application level issue, because it happens in multiple application instances at the same time, but other (non-Exposed) applications using the database with Hikari are fine for some reason.

[hafarooki]

https://gist.github.com/Miclebrick/c6ecd93d3b192de3e4507b691f384344 I made a system to report the stack trace of thread stuck on database calls, here is one (it's another upsert-lack workaround, I wonder if that's related?)

[hafarooki]

https://gist.github.com/Miclebrick/3bbe765035ed96b0d969d13fc523fca9 Hikari is also warning of a connection leak on startup now

[hafarooki]

https://gist.github.com/Miclebrick/e9b2770722107872ea7c0c070c15b8ad once again it happened, except this time, it was using this upsert method: #167 (comment)

seems to consistently be either upserts or upsert workarounds? Maybe a coincidence but that's what it is so far

[hafarooki]

brettwooldridge/HikariCP#754 after reading this I think it may be a bug with the mysql connector, I'm going to try mariadb instead

[hafarooki]

It still happens with the mariadb driver: https://gist.github.com/Miclebrick/634d384a82d34bdd48f3d12b8f9386b4 but it's still waiting on sockerRead0

[Tapac]

I would advise to connect to MySQL directly (with CLI or some client) and execute SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS. Output could give you some clue on what's happened on a database side.

[hafarooki]

Could I automate that by opening a new connection without Hikari and doing transaction(tempDatabase) { exec("SHOW ENGINE INNODB STATUS") }?

[Tapac]

I think you can, but I'd prefer to use some tool where you can easily copy output from. You can take free MySQL Workbench for example.

[hafarooki]

I made it automatically do it in case it happens when I'm not present like this:

                        val tmpDb = Database.connect(/* ... */)

                        transaction(tmpDb) {
                            exec("SHOW PROCESSLIST") { processList ->
                                val file = File(plugin.dataFolder, "processListDump.txt")
                                Files.write(file.toPath(), processList.toStrings())
                                log.info("Dumped process list to ${file.path}")
                            }

                            exec("SHOW ENGINE INNODB STATUS") { innoDbStatus ->
                                val file = File(plugin.dataFolder, "innoDbStatusDump.txt")
                                Files.write(file.toPath(), innoDbStatus.toStrings())
                                log.info("Dumped innodb status to ${file.path}")
                            }
                        }

tested it with thread.sleep

[hafarooki]

I "fixed" this by making a method for synchronized transactions, which locks on a file so that multiple application instances can't insert concurrently to mess it up. I guess this means this is not an Exposed issue, though, it's just an issue with concurrent inserting and my inability to deal with it properly, so I'll close this