Bug #96251 Long query (not only updating query) prevents 'flush tables' and FTWRL.
Submitted: 19 Jul 2019 3:47 Modified: 22 Aug 2019 15:11
Reporter: Meiji Kimura Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Paul Dubois CPU Architecture:Any

[19 Jul 2019 3:47] Meiji Kimura
Description:
The mysqldump manual said like this,

This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MySQL server may get stalled until those statements finish. 

https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

But not only 'long updating statement' but also 'long query'.

This case also should be mentioned on 'FLUSH TABLES' manual.

https://dev.mysql.com/doc/refman/5.6/en/flush.html#flush-tables
https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables
https://dev.mysql.com/doc/refman/5.8/en/flush.html#flush-tables

How to repeat:
(1) Prepare four mysql command line tool (A,B,C,D).

(2) With [A] run query like this.

select *, sleep(100) from mysql.user;

(3) With [B] run querh like this.

flush tables;

(4) with [C] you can see the (2)'s query prevent flush tables;

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
| Id | User            | Host            | db   | Command | Time | State
           | Info                                 |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon  | 8227 | Waiting on em
pty queue  | NULL                                 |
|  8 | root            | localhost:51652 | NULL | Query   |   41 | Waiting for t
able flush | flush tables                         |
| 10 | root            | localhost:51682 | test | Query   |   45 | User sleep
           | select *, sleep(100) from mysql.user |
| 11 | root            | localhost:51712 | NULL | Query   |    0 | starting
           | show processlist                     |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
4 rows in set (0.00 sec)

(5) With [C] run this query.

select * from mysql.user;

(6) With [D] run show processlist. (5)'s query is prevented by flush tables;

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
| Id | User            | Host            | db   | Command | Time | State
           | Info                                 |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon  | 8252 | Waiting on em
pty queue  | NULL                                 |
|  8 | root            | localhost:51652 | NULL | Query   |   66 | Waiting for t
able flush | flush tables                         |
| 10 | root            | localhost:51682 | test | Query   |   70 | User sleep
           | select *, sleep(100) from mysql.user |
| 11 | root            | localhost:51712 | NULL | Query   |   13 | Waiting for t
able flush | select * from mysql.user             |
| 12 | root            | localhost:51713 | NULL | Query   |    0 | starting
           | show processlist                     |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
5 rows in set (0.00 sec)

Suggested fix:
Modify & add the behavior about these.
[19 Jul 2019 4:11] MySQL Verification Team
Hello Meiji-San,

Thank you for the report.

regards,
Umesh
[22 Aug 2019 15:11] Paul Dubois
Posted by developer:
 
Sent query by email to bug reporter, got no reply. Closing without action.