killing 'Flush tables' in 'waiting for table flush' makes other queries block

[Nilnandan Joshi]

CREATE TABLE `flushes` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM

insert into flushes values(1);
insert into flushes values(2);
insert into flushes values(3);

Connection1:
----------------------
select * from flushes where id=3 or sleep(213); ## STEP-1

Connection2:
--------------------
flush tables with read lock; ## STEP-2, (you can run 'flush tables' as well)

#notice this flush blocks with status -> 'waiting for table flush', which is ok.

Connection2: ## STEP-3
-------------------
Do a Control-c and EXIT terminal. , ( i.e. interrupt the flush query)

Connection3: ### STEP-4
--------------------
select * from flushes;

Connection4: ### STEP-5
--------------------
insert into flushes values(5);

Connection5:
---------------------
show processlist;

this show the following:
-----------------------------------------------------------------------------
State | Info
-----------------------------------------------------------------------------
User sleep | select * from flushes where id=3 or sleep(213)
waiting for table flush | select * from flushes;
waiting for table flush | insert into flushes values(5);
-----------------------------------------------------------------------------

the blocked queries will terminate with lock wait timeout error.

Even though the 'flush tables...' terminated without even acquiring a lock , subsequent queries were not allowed to continue.

[Nilnandan Joshi (nilnandan-joshi)]

Confirmed with 5.6.25.

Session 1:
==============
nilnandan@desktop:~/sandboxes$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> CREATE TABLE `flushes` ( `id` int(11) DEFAULT NULL ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into flushes values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into flushes values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into flushes values(3);
Query OK, 1 row affected (0.00 sec)

mysql>

Session 2
==============
mysql> flush tables with read lock;
^CCtrl-C -- sending "KILL QUERY 38" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql>

Session 3
==============
mysql> select * from flushes;

Session 4
==============
mysql> insert into flushes values(5);

Session 5
==============

mysql> show processlist;
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------------+-----------+---------------+
| 37 | root | localhost | test | Query | 133 | User sleep | select * from flushes where id=3 or sleep(213) | 0 | 0 |
| 38 | root | localhost | NULL | Sleep | 130 | | NULL | 0 | 0 |
| 39 | root | localhost | test | Query | 110 | Waiting for table flush | select * from flushes | 0 | 0 |
| 40 | root | localhost | test | Query | 101 | Waiting for global read lock | insert into flushes values(5) | 0 | 0 |
| 41 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------------+-----------+---------------+
5 rows in set (0.00 sec)

------------
TRANSACTIONS
------------
Trx id counter 16140
Purge done for trx's n:o < 10011 undo n:o < 0 state: running but idle
History list length 12
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 41, OS thread handle 0x7f09d3f2e700, query id 158 localhost root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 40, OS thread handle 0x7f09d3f5f700, query id 155 localhost root Waiting for global read lock
insert into flushes values(5)
---TRANSACTION 0, not started
MySQL thread id 39, OS thread handle 0x...

[Nilnandan Joshi (nilnandan-joshi)]

Confirmed with 5.5.43.

nilnandan.joshi@bm-support01:~$ mysql -uroot -p --socket=/tmp/mysql_sandbox15543.sock
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.43-37.2 Percona Server (GPL), Release 37.2, Revision 6202fee
..

mysql> show processlist;
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------------+-----------+---------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------------+-----------+---------------+-----------+
| 13 | root | localhost | test | Query | 0 | NULL | show processlist | 0 | 0 | 0 |
| 14 | root | localhost | test | Query | 6 | Waiting for global read lock | insert into flushes values(5) | 0 | 0 | 0 |
| 15 | root | localhost | test | Query | 12 | Waiting for table flush | select * from flushes | 0 | 0 | 0 |
| 16 | root | localhost | test | Sleep | 33 | | NULL | 0 | 0 | 0 |
| 17 | root | localhost | test | Query | 39 | User sleep | select * from flushes where id=3 or sleep(213) | 0 | 0 | 0 |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------------+-----------+---------------+-----------+
5 rows in set (0.00 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1641