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 rao (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

[30 Jun 2012 20:01] vishnu rao
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
[30 Jun 2012 20:08] Valeriy Kravchuk
Please, check if the same problem still happens with a newer version, 5.5.24.
[2 Jul 2012 2:59] vishnu rao
will test over .24 and update the bug.

with regards,
ch Vishnu
[3 Aug 2012 1:00] Bugs System
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".
[30 Jul 2015 2:24] Marcos Albe
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
--------
[30 Jul 2015 2:31] Marcos Albe
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
[12 Oct 2018 6:52] MySQL Verification Team
Is this bug duplicated of Bug#44884 ?