Feature: MySQL handler Kill Query

[sundy-li]

Summary

Description for this feature.

Reproduce steps:

Running continous queries using MySQL client and pressed Ctrl + C:

while true;do
./databend-test 09_0004_remote_insert_into_select
sleep 1
done

It's same issue as #3997

[TCeason]

MySQL will send kill query 8. And the 8 is a connection id that generates by the MySQL client.

In MySQL:

mysql -uroot -h127.0.0.1                
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.27 Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@mysqldb 13:09:35 [(none)]> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              14 |
+-----------------+
1 row in set (0.01 sec)

root@mysqldb 13:09:36 [(none)]> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql -uroot -proot -h127.0.0.1 -P3307 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26-v0.2.1-alpha-6c170b4-simd(rust-1.61.0-nightly-2022-04-21T10:58:42.707348427Z) 0

when we press Ctrl + C, the client will send kill query connection_id to the server.

How do we deal with this query?

I this the best way is linking the connection_id to our processlist id.

[TCeason]

In ClickHouse the MySQL connection query id is : mysql:connection_id

press Ctrl + C will send this query:

KILL QUERY WHERE query_id = 'mysql:3'

[sundy-li]

processlist now has id field, but it's query id.

select * from system.processes\G;

kill query connection_id

We can modify the parser to handle this kill query.

[BohuTANG]

MySQL handler does not implement the kill for the new processor, reopened.

[TCeason]

MySQL handler does not implement the kill for the new processor, reopened.

Maybe is this issue? #5485

[BohuTANG]

Related to #5481 (comment)? How can we do executor.finish() in the kill? cc @zhang2014

[BohuTANG]

Client:

mysql> SELECT max(number), sum(number) FROM numbers_mt(100000000000) GROUP BY number % 3, number % 4, number % 5 LIMIT 10;
^C^C -- query aborted
^C^C -- query aborted
^C^C -- query aborted
^C^C -- query aborted
^C^C -- query aborted
^C^C -- query aborted

Server:

2022-06-06T09:28:18.494012Z  INFO databend_query::servers::mysql::mysql_handler: MySQL connection coming: Ok(127.0.0.1:53646)
2022-06-06T09:28:18.559093Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: KILL QUERY 9

2022-06-06T09:28:30.359496Z  INFO databend_query::servers::mysql::mysql_handler: MySQL connection coming: Ok(127.0.0.1:53648)
2022-06-06T09:28:30.457506Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: KILL QUERY 9
2022-06-06T09:28:30.795558Z  INFO databend_query::servers::mysql::mysql_handler: MySQL connection coming: Ok(127.0.0.1:53650)
2022-06-06T09:28:30.874239Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: KILL QUERY 9
2022-06-06T09:28:30.959319Z  INFO databend_query::servers::mysql::mysql_handler: MySQL connection coming: Ok(127.0.0.1:53652)
2022-06-06T09:28:31.021922Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: KILL QUERY 9
2022-06-06T09:28:31.156364Z  INFO databend_query::servers::mysql::mysql_handler: MySQL connection coming: Ok(127.0.0.1:53654)
2022-06-06T09:28:31.229753Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: KILL QUERY 9
2022-06-06T09:28:31.305193Z  INFO databend_query::servers::mysql::mysql_handler: MySQL connection coming: Ok(127.0.0.1:53658)
2022-06-06T09:28:31.371610Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: KILL QUERY 9

[BohuTANG]

Clickhouse handler also has this issue.
When the client quiets (forced by closing the terminal), the databend-query still working until the compute is finished.

[zhang2014]

Clickhouse handler also has this issue.
When the client quiets (forced by closing the terminal), the databend-query still working until the compute is finished.

Also fixed by #5803