Feature: MySQL handler Kill Query

[TCeason]

Link to ISSUE: #4871

Summary

When we press Ctrl + C on MySQL Client, the client will receive a kill signal.

And then generate a kill query like this :

kill query 8

Note: the 8 is a connection id, that can be select with function connection_id, and it is PROCESSLIST_ID in MySQL.

How to fix

• Support parser number
  Now, we use parse_identifier to get the object_id in parser_kil. But the query like kill quey 8. So we need support parser numbers first.

• Support link MySQL connection id to query id
  Now the kill query gets a query id and then uses get_session_by_id to get the session id. But now we can directly get the session id. And the plan.id is String but MySQL connection id is a u32 int.
  So I think we need do some convert to get the query id in analyze.

• MySQL connection id need diff.
  In mysql_interactive_worker we hard code MySQL connection set the value always be 8. If we support the second item, the MySQL connection id should be different.

Question

Now I just support parser number but store it as an ident with query quote '. In commit: TCeason@b32a663

But I find the object_id is Ident, value is a string type, how can we let the object_id store u32 connection id?

#[derive(Debug, Clone, PartialEq)]
pub struct DfKillStatement {
    pub object_id: Ident,
    pub kill_query: bool,
}

Maybe we can support an extra item like pub conn_id: u32?

[TCeason]

cc @sundy-li

Is there more suggestion?

[zhang2014]

MySQL also supported KILL CONNECTION 8

[TCeason]

Yes. But if press Ctrl C to interrupt a query, the client will send kill query 8

[sundy-li]

Now we use uuid as query_id which is a long string, yet MySQL connection id is u32 number.

So how about parsing the object_id into integer in previous.

If it's ok, we take the object_id as mysql_connection_id and get the query_id from the cached hashmap.

Otherwise, we take the object_id as query_id.

[TCeason]

Now we use uuid as query_id which is a long string, yet MySQL connection id is u32 number.

So how about parsing the object_id into integer in previous.

Otherwise, we take the object_id as mysql_connection_id and get the query_id from the cached hashmap.

If the parse is not ok, we take the object_id as query_id.

I'm not clear about one thing:

• how to get query_id and mysql_connection_id?
  maybe we need to add a column in processes?

desc processes;
+--------------------------+-----------------+------+---------+-------+
| Field                    | Type            | Null | Default | Extra |
+--------------------------+-----------------+------+---------+-------+
| id                       | VARCHAR         | NO   |         |       |
| type                     | VARCHAR         | NO   |         |       |
| host                     | VARCHAR         | YES  | NULL    |       |
| user                     | VARCHAR         | YES  | NULL    |       |
| state                    | VARCHAR         | NO   |         |       |
| database                 | VARCHAR         | NO   |         |       |
| extra_info               | VARCHAR         | YES  | NULL    |       |
| memory_usage             | BIGINT          | NO   | 0       |       |
| dal_metrics_read_bytes   | BIGINT UNSIGNED | YES  | NULL    |       |
| dal_metrics_write_bytes  | BIGINT UNSIGNED | YES  | NULL    |       |
| scan_progress_read_rows  | BIGINT UNSIGNED | YES  | NULL    |       |
| scan_progress_read_bytes | BIGINT UNSIGNED | YES  | NULL    |       |
+--------------------------+-----------------+------+---------+-------+

• where to store the hashmap?

[sundy-li]

we need to add a column in processes?

Yes, mysql_connection_id, Nullable(INT UNSIGNED)

where to store the hashmap

I think it's ok to store it in memory, hashmap inside the session manager

[TCeason]

Got and I will have a look.

[TCeason]

Yep we support the step one now:

root@mysqldb 22:27:00 [system]> select id, type, mysql_connection_id from processes;
+--------------------------------------+------------+---------------------+
| id                                   | type       | mysql_connection_id |
+--------------------------------------+------------+---------------------+
| eed18d69-dd8a-48b9-93e8-740e5ebf313c | Clickhouse |                NULL |
| fd81b2ea-467d-4e78-a80e-666ef9d1809f | MySQL      |                   8 |
+--------------------------------------+------------+---------------------+
2 rows in set (0.05 sec)
Read 2 rows, 457.00 B in 0.004 sec., 495.93 rows/sec., 110.66 KiB/sec.

[TCeason]

I try to create a new hashmap in session_mgr. The key is mysql conn id and the value is id.

And the hashmap is force inserted in create_session. The code is here: TCeason@7a39bda#diff-05e190acf915432f50b78d25482d816df331623745159f6e235f255aa4c73516R190

But I think this will generate some other error.

the kill query will also generate a new id so the hashmap will store the kill query's id as a new value.

the query that we want to kill will not be killed.

[TCeason]

I try to create a new hashmap in session_mgr. The key is mysql conn id and the value is id.

And the hashmap is force inserted in create_session. The code is here: TCeason@7a39bda#diff-05e190acf915432f50b78d25482d816df331623745159f6e235f255aa4c73516R190

But I think this will generate some other error.

the kill query will also generate a new id so the hashmap will store the kill query's id as a new value.

the query that we want to kill will not be killed.

Now we impl: kill query mysql_connid convert to kill query session_id：

root@mysqldb 19:40:20 [(none)]> show processlist;
+--------------------------------------+-------+-----------------+------+-------+----------+------------------+--------------+------------------------+-------------------------+-------------------------+--------------------------+---------------------+
| id                                   | type  | host            | user | state | database | extra_info       | memory_usage | dal_metrics_read_bytes | dal_metrics_write_bytes | scan_progress_read_rows | scan_progress_read_bytes | mysql_connection_id |
+--------------------------------------+-------+-----------------+------+-------+----------+------------------+--------------+------------------------+-------------------------+-------------------------+--------------------------+---------------------+
| 6e11ab8c-f4a9-465b-9df8-f1103300ffea | MySQL | 127.0.0.1:53914 | root | Query | default  | show processlist |            0 |                      0 |                       0 |                       0 |                        0 |                   8 |
+--------------------------------------+-------+-----------------+------+-------+----------+------------------+--------------+------------------------+-------------------------+-------------------------+--------------------------+---------------------+
1 row in set (0.06 sec)
Read 1 rows, 244.00 B in 0.007 sec., 137.01 rows/sec., 32.65 KiB/sec.

root@mysqldb 19:42:07 [(none)]> kill query 8;
Query OK, 0 rows affected (0.05 sec)
Read 0 rows, 0.00 B in 0.003 sec., 0 rows/sec., 0.00 B/sec.

TODO

We need generate diff mysql connection id that can insert into the hashmap.

Execute a long query：

2022-05-17T11:37:32.492023Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: select * from numbers(999999999999)

Press Ctrl + C the log：

2022-05-17T11:37:34.671791Z  INFO databend_query::sessions::session: === test the id is 389fc734-3e1c-4f2f-b546-a6ea7b989e74
2022-05-17T11:37:34.672073Z  INFO databend_query::sessions::session_mgr: +++++++
2022-05-17T11:37:34.672245Z  INFO databend_query::sessions::session_mgr: before insert : (8,5054011f-54cf-40fd-95d5-2373de6c4d7b)
2022-05-17T11:37:34.672403Z  INFO databend_query::sessions::session_mgr: after insert : (8,389fc734-3e1c-4f2f-b546-a6ea7b989e74)
2022-05-17T11:37:34.672530Z  INFO databend_query::sessions::session_mgr: =======
2022-05-17T11:37:34.672718Z  INFO databend_query::servers::mysql::mysql_handler: MySQL connection coming: Ok(127.0.0.1:53912)
2022-05-17T11:37:34.703082Z  INFO databend_query::servers::mysql::mysql_interactive_worker: Normal query: KILL QUERY 8
2022-05-17T11:37:34.707697Z  INFO databend_query::interpreters::interpreter_kill: ***************
2022-05-17T11:37:34.722264Z  INFO databend_query::interpreters::interpreter_kill: In kill the map is (8,389fc734-3e1c-4f2f-b546-a6ea7b989e74)
2022-05-17T11:37:34.722500Z  INFO databend_query::interpreters::interpreter_kill: %%%%%%%%%%%%%%%
2022-05-17T11:37:34.722656Z  INFO databend_query::interpreters::interpreter_kill: === test get id is 389fc734-3e1c-4f2f-b546-a6ea7b989e74, plan id is 8

MySQL Client create a new session to execute kill query. But because the map is force insert and mysql connection id always is 8, the map value will be replaced.

BTW, if the connection exit, I think we need to remove it from the hashmap.

[sundy-li]

MySQL Client create a new session to execute kill query.

It does not matter, it will send the old connection id to databend.
We should generate an increment connection id when creating a new session.

[TCeason]

MySQL Client create a new session to execute kill query.

It does not matter, it will send the old connection id to databend. We should generate an increment connection id when creating a new session.

Yes I absolutely agree.

[TCeason]

root@mysqldb 17:19:03 [(none)]> select * from numbers(999999999);
^C^C -- query aborted
ERROR 1152 (08S01): Code: 1043, displayText = Aborted query, because the server is shutting down or the query was killed.
root@mysqldb 17:19:07 [(none)]> show tables;
Query OK, 0 rows affected (0.07 sec)
Read 22 rows, 2.61 KiB in 0.019 sec., 1.13 thousand rows/sec., 134.07 KiB/sec.

root@mysqldb 17:19:18 [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| default            |
| system             |
+--------------------+
3 rows in set (0.06 sec)
Read 3 rows, 63.00 B in 0.007 sec., 443.61 rows/sec., 9.10 KiB/sec.

It can make sense on my local pc. TCeason@cac7f4c

But only have some question:

I use the subsec_nanos as the mysql conn id . but it maybe not unique.

[Xuanwo]

I use the subsec_nanos as the mysql conn id . but it maybe not unique.

How about maintaining an Atomic<u32> in session_ctx?