Description:
I've noticed that mysql is not cleaning up certain running queries when a client disconnects while the query is running. This is most noticeable if I have a `TRUNCATE TABLE` that is blocked on a backup lock (i.e.,: https://bugs.mysql.com/bug.php?id=110820).
See test case below. My test case is contrived, but it illustrates a broader point: MySQL doesn't seem to do a great job of cleaning up from clients who are obviously gone. The same thing is not true if I use something like `SELECT SLEEP(1000)` instead of the truncate command. Possibly this is an issue with all queries in a lock wait state.
In my other bug, I have a monitoring tool using TRUNCATE on P_S tables very frequently. Because the truncate blocks indefinitely, I get a LOT of threads trying to truncate, even if my monitoring tool disconnects.
How to repeat:
Have two terminals open:
mysql2> lock instance for backup;
Query OK, 0 rows affected (0.00 sec)
mysql1> truncate performance_schema.table_io_waits_summary_by_table; # Blocks
mysql2> show processlist;
+-----+-----------------+-----------------+--------------------+---------+-------+-------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+--------------------+---------+-------+-------------------------+------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 21927 | Waiting on empty queue | NULL |
| 10 | root | localhost | NULL | Query | 0 | init | show processlist |
| 742 | test | 127.0.0.1:43468 | performance_schema | Query | 6 | Waiting for backup lock | truncate table_io_waits_summary_by_table |
+-----+-----------------+-----------------+--------------------+---------+-------+-------------------------+------------------------------------------+
3 rows in set (0.00 sec)
mysql1> # still blocking
^Z
[1] Terminated mysql -h 127.0.0.1 -P 3306 -u test --prompt="mysql1> "
[2]+ Stopped mysql -h 127.0.0.1 -P 3306 -u test --prompt="mysql1> "
bash-4.4# kill %
[2]+ Stopped mysql -h 127.0.0.1 -P 3306 -u test --prompt="mysql1> "
# mysql 1 client has totally disconnected
mysql2> show processlist;
+-----+-----------------+-----------------+--------------------+---------+-------+-------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+--------------------+---------+-------+-------------------------+------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 22091 | Waiting on empty queue | NULL |
| 10 | root | localhost | NULL | Query | 0 | init | show processlist |
| 742 | test | 127.0.0.1:43468 | performance_schema | Query | 170 | Waiting for backup lock | truncate table_io_waits_summary_by_table |
+-----+-----------------+-----------------+--------------------+---------+-------+-------------------------+------------------------------------------+
3 rows in set (0.00 sec)
# Query lingers indefinitely
Suggested fix:
I'd expect a client that has totally disconnected via TCP to have it's thread killed.
