I have a long-running query that I run against a MySQL 8 database via JDBC. My goal is to be able to exit this query on some trigger (e.g. external signal, data processing error, etc.) and have the query cancel. Based on my tests and research on MySQL forums, the statement.cancel()
does not actually do anything.
How can I achieve this? Is there no other way than to invoke KILL <pid>
on a separate connection? If so, I can manage that, but I don’t understand how I can identify the exact PID of my long-running query. Is there a reliable method for this?
In short, how can I, using MySQL Connector J, cancel a long-running query on demand? I would really appreciate a link to a working example with code, if at all possible.
For reference, here is some sample code written in Kotlin:
val data = produce<List<Data>> {
db.connection.use { conn ->
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY).use { stmt ->
stmt.fetchSize = Int.MIN_VALUE
stmt.executeQuery("...").use { rs ->
var batch = mutableListOf<Data>()
while (rs.next()) {
// Handle result set here
val data = Data(
id = rs.getLong("id"),
subAccountId = rs.getInt("sub_account_id")
)
batch.add(data)
if (batch.size >= 100) {
println("Sending batch...")
send(batch)
if (done) {
println("Done, closing...")
close()
break
}
batch = mutableListOf()
}
}
println("Out of loop, cancelling statement")
synchronized(this) { // my lame attempt at cancelling
println("In synchronized")
if (!stmt.isClosed) {
println("Statement is not yet closed") // DOES print
stmt.cancel()
stmt.close()
conn.close() // probably pointless but I tried
}
}
}
println("Out of statement") // does NOT print
}
println("Out of connection") // does NOT print
}
println("End of production...") // does NOT print
}
I’m able to see up through “Statement is not yet closed” in the console, but nothing beyond that.