MySQL kill session not working?

Background

In a daily test, it was found that after killing a session, the SQL statement was still running and not terminated; the killed session reconnected and continued to execute the original SQL statement.

Test

This test is based on MySQL 8.0.27

1. Create a test table

create table t1 (id int, name varchar(30));
insert into t1 values (1,'a'),(2,'b');

2. Open 3 sessions

session1	session2	session3
begin;
select * from t1;
	rename table t1 to t2; [due to lock waiting, hang]
		show processlist; 【View processlist_id】
		kill session2;
	【session2 reconnects and continues to execute the statement, in a lock waiting state】
		show processlist; [You can see that session2 reconnects and continues to execute SQL]
commit;
	【rename executed successfully】
show tables; [t1 is renamed to t2]

session1: open a transaction without committing

mysql> use test
Database changed
mysql>
mysql>
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
 + ------ + ------+
| id | name |
 + ------ + ------+
| 1 | a |
| 2 | b |
 + ------ + ------+
2 rows in set (0.00 sec)

session2: Execute DDL statements

mysql> use test
Database changed
mysql>
mysql>
mysql> rename table t1 to t2;

session3: kill session2

mysql> show processlist;
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| Id | db | Command | Time |
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| 6 | NULL | Daemon | 4399013 | Waiting on empty queue | NULL |
| 132 | test | Sleep | 232 | | NULL |
| 134 | test | Query | 123 | Waiting for table metadata lock| rename table t1 to t2|
| 135 | test | Query | 0 | init | show processlist |
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
4 rows in set (0.00 sec)

mysql> kill 134;
Query OK, 0 rows affected (0.01 sec)
#For typesetting, the form fields are slightly deleted, please see the picture for details

session2: session2 reconnects, and continues to execute DDL statements, still in the lock waiting state

mysql> rename table t1 to t2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect... ?
Connection id: 136
Current database: test

session3: view session information

mysql> show processlist;
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| Id | db | Command | Time |
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| 6 | NULL | Daemon | 4399260 | Waiting on empty queue | NULL |
| 132 | test | Sleep | 479 | | NULL |
| 135 | test | Query | 0 | init | show processlist |
| 136 | test | Query | 193 | Waiting for table metadata lock| rename table t1 to t2|
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
4 rows in set (0.00 sec)
#For typesetting, the form fields are slightly deleted, please see the picture for details

As you can see, after killing session2, session2 reconnects and continues to execute SQL

session1: commit transaction

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

session2: Executed successfully

mysql> use test
Database changed
mysql>
mysql>
mysql> rename table t1 to t2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect... ?
Connection id: 136
Current database: test

Query OK, 0 rows affected (8 min 38.00 sec)

Through the above tests, we can see that the kill command was clearly executed, but it still did not achieve the desired effect. It seems that the kill command did not take effect.

After querying the data, it is found that due to logging in through the MySQL client, the --reconnect reconnection option is enabled by default. This option will perform a reconnection attempt every time the connection is lost; therefore, after killing session2 , session2 reconnects and executes the previous SQL statement again, resulting in the feeling that the kill command does not take effect.

--reconnect Reconnect if the connection is lost. Disable with
                      --disable-reconnect. This option is enabled by default.
                      (Defaults to on; use --skip-reconnect to disable.)

Resolve

The above problems can be avoided by the following two methods:

1. Execute the kill query command

KILL QUERY terminates the currently executing statement of the connection, but leaves the connection itself unchanged

session3: execute KILL QUERY command

mysql> show processlist;
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| Id | db | Command | Time |
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| 6 | NULL | Daemon | 4401560 | Waiting on empty queue | NULL |
| 132 | test | Sleep | 11 | | NULL |
| 135 | test | Query | 0 | init | show processlist |
| 137 | test | Query | 3 | Waiting for table metadata lock| rename table t1 to t2|
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
4 rows in set (0.00 sec)

mysql>
mysql> kill query 137;
Query OK, 0 rows affected (0.00 sec)
#For typesetting, the form fields are slightly deleted, please see the picture for details

session2:

mysql> rename table t1 to t2;
ERROR 1317 (70100): Query execution was interrupted

It can be seen that the statement executed by session2 has been terminated, achieving the desired effect.

2. Add –skip-reconnect option when logging in to the mysql client

--skip-reconnect indicates that no reconnection attempts will be made when the connection is lost

session2: Add --skip-reconnect option when logging in

shell> mysql -uroot -p -h127.0.0.1 -P3306 --skip-reconnect

session3: Execute the kill command

mysql> show processlist;
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| Id | db | Command | Time |
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
| 6 | NULL | Daemon | 4402073 | Waiting on empty queue | NULL |
| 132 | test | Sleep | 524 | | NULL |
| 135 | test | Query | 0 | init | show processlist |
| 139 | test | Query | 4 | Waiting for table metadata lock| rename table t1 to t2|
 + ----- + ------ + --------- + --------- + ------------- ---------------- + ---------------------- +
4 rows in set (0.00 sec)

mysql> kill 139;
Query OK, 0 rows affected (0.00 sec)

session2:

mysql> rename table t1 to t2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

It can be seen that the session connection of session2 has been terminated, and there is no automatic reconnection, which achieves the effect we want.

Summary

1. When logging in through the MySQL client, the option of session reconnection --reconnect is enabled by default. If you want to prohibit reconnection, you can add –skip-reconnect when logging in

2. KILL CONNECTION Same as KILL, it terminates the session connection after terminating any statements the connection is executing.

3. KILL QUERY Terminates the statement currently being executed by the connection, but leaves the connection itself unchanged.

Reference Link

https://dev.mysql.com/doc/refman/8.0/en/kill.html

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html

Enjoy GreatSQL 🙂

“MGR in simple terms” video course

Click this applet to go directly to Station B

https://www.bilibili.com/medialist/play/1363850082?business=space_collection & amp;business_id=343928 & amp;desc=0

Article recommendation:

• • MySQL optimizer_trace cost quantitative analysis

  • Several schemes for Redis to implement distributed locks

  • Interesting events_statements_current table issue

  • Also use the audit plugin in the MySQL community edition

  • “show engine innodb status” in Percona 8.0.30 leads to coredump troubleshooting and analysis

  • Visual binary installation of GreatSQL on Tongxin UOS

  • Fault case: MySQL unique index has duplicate values, but the official said This is not a bug

  • Analysis of sp running check table version update process in MySQL

  • gt-checksum 1.2.1 is released, adding super practical features such as table structure verification and repair

  • A brief analysis of the execution process of show engine inndob status

If you want to read more technical articles, click “Looking”!