KILL CONNECTION doesn't work

[smacker]

Gitbase:

MySQL [(none)]> select version();
+--------------------+
| VERSION()          |
+--------------------+
| 8.0.11-v0.20.0-rc1 |
+--------------------+
1 row in set (0.01 sec)

MySQL [(none)]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

MySQL [(none)]> show processlist;
+------+------+-----------------+---------+---------+------+---------+------------------+
| Id   | User | Host            | db      | Command | Time | State   | Info             |
+------+------+-----------------+---------+---------+------+---------+------------------+
|    8 | root | 127.0.0.1:52482 | gitbase | query   |    0 | running | show processlist |
+------+------+-----------------+---------+---------+------+---------+------------------+
1 row in set (0.01 sec)

MySQL [(none)]> kill connection 1;
ERROR 1105 (HY000): unknown error: Connection not found:
MySQL [(none)]> select 1;
+------+
| 1    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

MySQL [(none)]>

MySQL:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.00 sec)

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  256 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

mysql> kill connection 8;
ERROR 1317 (70100): Query execution was interrupted
mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: *** NONE ***

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

Also in gitbase ids in show processlist are increasing with each query instead of using current connection_id.

[smacker]

According to changelog this issue was actually fixed in v0.20.0-rc1. But as you can see above -> not really.

[kuba--]

@smacker - we have some incompatibilities with MySQL implementation - see: src-d/go-mysql-server#719

[smacker]

How to implement query cancelation then?

Current approach:

• select connection_id(); -> store somewhere
• run the query
• in case of user canceled query -> kill connection from stored value in another connection.

With current incompatible implementation, I don't see how to implement cancelation at all.
We can't grep show processlist(), it can have 2 identical queries but only one should be canceled.

[kuba--]

With MySQL you can only kill the latest query (based on connection_id).
With our implementation run query, show processlist (it's sorted by ID) and find a query to kill (the query with the greatest ID is the latest)

[smacker]

With MySQL you can only kill the latest query (based on connection_id)

not sure what you mean. As far as I know you can run only 1 query in 1 connection at the same time.
If you want to run 2 queries you need 2 connections.

With our implementation run query, show processlist (it's sorted by ID) and find a query to kill (the query with the greatest ID is the latest)

this discussion move to slack. Just for the record: we don't cancel the latest query but arbitrary one.

[ajnavarro]

Confirmed that it is fixed on 0.20.0-rc4