MySQL KILL QUERY support

[CGA1123]

The current support for MySQL is fairly limited to only SELECT statements via MAX_EXECUTION_TIME.

MySQL supports cancellation of queries via KILL QUERY which seems analogous to PostgreSQL's query cancellation.

There are some awkward things though, MySQL doesn't rollback anything after a KILL which can mean that cancellation during UPDATE/DELETE/INSERT can be partial... This might be a no-go of an approach 🤷‍♂️ Needs to be dug into.

From Docs:

During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. If you are not using transactions, the changes are not rolled back.

[CGA1123]

https://github.com/rocketlaunchr/mysql-go is an example of an implementation making use of KILL QUERY

[CGA1123]

# @param conn [Mysql2::Client] The connection to run the query on.
# @param killconn [Mysql2::Client] The connection to use to kill the query.
# @param sql [String] The SQL query string to execute.
# @param timeout [Numeric] The timeout (in seconds) to wait.
def query(conn, killconn, sql, timeout)
  conn.query(sql, {async: true})
  unless IO.select([IO.for_fd(conn.socket)], nil, nil, timeout)
    killconn.query("KILL QUERY #{conn.thread_id}")
  end

  conn.async_result
end

Something like this might work... A bit annoying that it requires a separate connection only for killing.

[CGA1123]

Something a bit annoying: https://vitess.io/docs/reference/compatibility/mysql-compatibility/#killing-running-queries

Although seems that vitess does have some magic comments?
https://github.com/vitessio/vitess/blob/d7f00349980a53cb5345e549046e87eaf3a2e6b8/go/vt/sqlparser/comments.go#L32-L33

As a replacement for /*+ MAX_EXECUTION_TIME(1000) */ vitess supports /*vt+ QUERY_TIME_MS(1000) */