MySQL Bugs: #65777: killing 'Flush tables' in 'waiting for table flush' makes other queries block

Bug #65777	killing 'Flush tables' in 'waiting for table flush' makes other queries block
Submitted:	30 Jun 2012 20:01	Modified:	27 Nov 2015 17:21
Reporter:	vishnu chanderraju (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.5.21	OS:	Other (mac-10.7.1 & debian)
Assigned to:		CPU Architecture:	Any
Tags:	blocked, flush tables, myisam, waiting for flush

Description:
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.

---------------------------------------------------------------
Server version: 5.5.21-log MySQL Community Server (GPL)

How to repeat:
see description.

Suggested fix:
na

Please, check if the same problem still happens with a newer version, 5.5.24.

will test over .24 and update the bug.

with regards,
ch Vishnu

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Verified as described with 5.5.43 Community

seesion 1:
==========
 bm-support01 in ~/sandboxes/msb_5_5_43
○ → ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > 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 [localhost] {msandbox} (test) > DROP TABLE IF EXISTS flushes;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > CREATE TABLE `flushes` (
    ->   `id` int(11) DEFAULT NULL
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > insert into flushes values(1);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into flushes values(2);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into flushes values(3);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > select * from flushes where id=3 or sleep(600);

session 2:
==========
mysql [localhost] {msandbox} ((none)) > flush tables with read lock;
^CCtrl-C -- sending "KILL QUERY 2" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql [localhost] {msandbox} ((none)) > Bye

session 3:
==========
mysql [localhost] {msandbox} ((none)) > use test;
Database changed
mysql [localhost] {msandbox} (test) > select * from flushes;

seesion 4:
==========

mysql [localhost] {msandbox} (test) > insert into flushes values(5);

session 5
==========
 bm-support01 in ~/sandboxes/msb_5_5_43
○ → ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > show processlist;
+----+----------+-----------+------+------------+------+-------------------------+------------------------------------------------+
| Id | User     | Host      | db   | Command    | Time | State                   | Info                                           |
+----+----------+-----------+------+------------+------+-------------------------+------------------------------------------------+
|  1 | msandbox | localhost | test | Query      |   77 | User sleep              | select * from flushes where id=3 or sleep(600) |
|  3 | msandbox | localhost | test | Field List |   42 | Waiting for table flush |                                                |
|  6 | msandbox | localhost | test | Query      |   34 | Waiting for table flush | select * from flushes                          |
|  7 | msandbox | localhost | test | Query      |   25 | Waiting for table flush | insert into flushes values(5)                  |
|  8 | msandbox | localhost | NULL | Query      |    0 | NULL                    | show processlist                               |
+----+----------+-----------+------+------------+------+-------------------------+------------------------------------------------+
5 rows in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
....
------------
TRANSACTIONS
------------
Trx id counter 505
Purge done for trx's n:o < 0 undo n:o < 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 8, OS thread handle 0x7ffff45f3700, query id 44 localhost msandbox
show engine innodb status
---TRANSACTION 504, ACTIVE 137 sec
mysql tables in use 1, locked 0
MySQL thread id 1, OS thread handle 0x7ffff46f7700, query id 21 localhost msandbox User sleep
select * from flushes where id=3 or sleep(600)
Trx read view will not see trx with id >= 505, sees < 505
--------

Verified in 5.6.25
 bm-support01 in ~/sandboxes/msb_5_6_25
○ → ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.25-73.0 Percona Server (GPL), Release 73.0, Revision 5ccddf8

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > show processlist;
+----+----------+-----------+------+---------+------+-------------------------+------------------------------------------------+-----------+---------------+
| Id | User     | Host      | db   | Command | Time | State                   | Info                                           | Rows_sent | Rows_examined |
+----+----------+-----------+------+---------+------+-------------------------+------------------------------------------------+-----------+---------------+
|  1 | msandbox | localhost | test | Query   |   79 | User sleep              | select * from flushes where id=3 or sleep(600) |         0 |             0 |
|  4 | msandbox | localhost | test | Query   |   37 | Waiting for table flush | select * from flushes                          |         0 |             0 |
|  5 | msandbox | localhost | test | Query   |   15 | Waiting for table flush | insert into flushes values(5)                  |         0 |             0 |
|  6 | msandbox | localhost | NULL | Query   |    0 | init                    | show processlist                               |         0 |             0 |
+----+----------+-----------+------+---------+------+-------------------------+------------------------------------------------+-----------+---------------+

------------
TRANSACTIONS
------------
Trx id counter 3335
Purge done for trx's n:o < 1294 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 6, OS thread handle 0x7ffff7e75700, query id 31 localhost msandbox init
show engine innodb status
---TRANSACTION 3334, ACTIVE 138 sec
mysql tables in use 1, locked 0
MySQL thread id 1, OS thread handle 0x7ffff7f38700, query id 15 localhost msandbox User sleep
select * from flushes where id=3 or sleep(600)
Trx read view will not see trx with id >= 3335, sees < 3335

Is this bug duplicated of Bug#44884 ?