Changed FLUSH TABLES to not change share version

Part of MDEV-5336 Implement LOCK FOR BACKUP

Originally both table metadata lock and global read lock protection
were acquired before getting TABLE from table cache. This will be
reordered in a future commit with MDL_BACKUP_XXX locks so that we
first take table metadata lock, then get TABLE from table cache, then
acquire analogue of global read lock.

This patch both simplifies FLUSH TABLES code, makes FLUSH TABLES to
lock less and also enables FLUSH TABLES code to be used with backup
locks.

The usage of FLUSH TABLES changes slightly:
- FLUSH TABLES without any arguments will now only close not used tables
  and tables locked by the FLUSH TABLES connection.  All not used table
  shares will be closed.
  Tables locked by the FLUSH TABLES connection will be reopened and
  re-locked after all others has stoped using the table (as before).
  If there was no locked tables, then FLUSH TABLES is instant and will
  not cause any waits.
  FLUSH TABLES will not wait for any in use table.
- FLUSH TABLES with a table list, will ensure that the tables are closed
  before statement returns. The code is now only using MDL locks and not
  table share versions, which simplices the code greatly. One visible
  change is that the server will wait for the end of the transaction that
  are using the tables. Before FLUSH TABLES only waited for the statements
  to end.

Signed-off-by: Monty <monty@mariadb.org>

32 files changed, 121 insertions, 87 deletions

diff --git a/mysql-test/main/flush.result b/mysql-test/main/flush.result
index c5176527a3a..8149ce29dec 100644
--- a/mysql-test/main/flush.result
+++ b/mysql-test/main/flush.result
@@ -364,16 +364,19 @@ flush table t1;
 connection default;
 # Let flush table sync in.
 select * from t1;
+a
 connection con1;
 select * from t1;
 a
 unlock tables;
+connection default;
+select count(*) from information_schema.processlist where state = "Waiting for table metadata lock";
+count(*)
+1
+commit;
 connection con2;
 # Reaping 'flush table t1'...
 connection default;
-# Reaping 'select * from t1'...
-a
-commit;
 #
 # Repeat the same test but with FLUSH TABLES
 #
@@ -386,13 +389,10 @@ connection con1;
 #
 lock table t1 read;
 connection con2;
-#
-# FLUSH TABLES expels the table definition from the cache.
-# Sending 'flush tables'...
 flush tables;
 connection default;
-# Let flush table sync in.
 select * from t1;
+a
 connection con1;
 select * from t1;
 a
@@ -400,8 +400,6 @@ unlock tables;
 connection con2;
 # Reaping 'flush tables'...
 connection default;
-# Reaping 'select * from t1'...
-a
 commit;
 # Cleanup
 connection con1;
diff --git a/mysql-test/main/flush.test b/mysql-test/main/flush.test
index d626abf8880..821168f7706 100644
--- a/mysql-test/main/flush.test
+++ b/mysql-test/main/flush.test
@@ -449,24 +449,20 @@ connection default;
 --echo # Let flush table sync in.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush"
+  where state = "Waiting for table metadata lock"
   and info = "flush table t1";
 --source include/wait_condition.inc
-send select * from t1;
+select * from t1;
 connection con1;
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush"
-  and info = "select * from t1";
 select * from t1;
 unlock tables;
+connection default;
+select count(*) from information_schema.processlist where state = "Waiting for table metadata lock";
+commit;
 connection con2;
 --echo # Reaping 'flush table t1'...
 reap;
 connection default;
---echo # Reaping 'select * from t1'...
-reap;
-commit;
 
 --echo #
 --echo # Repeat the same test but with FLUSH TABLES
@@ -480,31 +476,16 @@ connection con1;
 --echo #
 lock table t1 read;
 connection con2;
---echo #
---echo # FLUSH TABLES expels the table definition from the cache.
---echo # Sending 'flush tables'...
 send flush tables;
 connection default;
---echo # Let flush table sync in.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush"
-  and info = "flush tables";
---source include/wait_condition.inc
-send select * from t1;
+select * from t1;
 connection con1;
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush"
-  and info = "select * from t1";
 select * from t1;
 unlock tables;
 connection con2;
 --echo # Reaping 'flush tables'...
 reap;
 connection default;
---echo # Reaping 'select * from t1'...
-reap;
 commit;
 
 --echo # Cleanup
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 4a39eea6115..6db839edf51 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -1957,6 +1957,7 @@ lock table t1 read;
 connect con1, localhost, root,,;
 connection con1;
 flush tables;
+flush tables t1;
 connection default;
 select * from information_schema.views;
 TABLE_CATALOG	def
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index 2b318f5f1aa..7ce6437d610 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -1622,12 +1622,13 @@ alter table t1 change b c int;
 lock table t1 read;
 connect(con1, localhost, root,,);
 connection con1;
-send flush tables;
+flush tables;
+send flush tables t1;
 connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-    where state = "Waiting for table flush" and
-          info = "flush tables";
+    where state = "Waiting for table metadata lock" and
+          info = "flush tables t1";
 --source include/wait_condition.inc
 --vertical_results
 select * from information_schema.views;
diff --git a/mysql-test/main/kill.result b/mysql-test/main/kill.result
index dc1cb9252da..4775d111b79 100644
--- a/mysql-test/main/kill.result
+++ b/mysql-test/main/kill.result
@@ -324,7 +324,7 @@ connection blocker;
 lock tables t1 read;
 connection ddl;
 # Let us mark locked table t1 as old
-flush tables;
+flush tables t1;
 connection dml;
 select * from t1;
 connection default;
diff --git a/mysql-test/main/kill.test b/mysql-test/main/kill.test
index b6000ffced1..059d8d40b11 100644
--- a/mysql-test/main/kill.test
+++ b/mysql-test/main/kill.test
@@ -538,18 +538,18 @@ connection blocker;
 lock tables t1 read;
 connection ddl;
 --echo # Let us mark locked table t1 as old
---send flush tables
+--send flush tables t1
 connection dml;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush" and
-        info = "flush tables";
+  where state = "Waiting for table metadata lock" and
+        info = "flush tables t1";
 --source include/wait_condition.inc
 --send select * from t1
 connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush" and
+  where state = "Waiting for table metadata lock" and
         info = "select * from t1";
 --source include/wait_condition.inc
 --replace_result $ID2 ID2
diff --git a/mysql-test/main/lock.result b/mysql-test/main/lock.result
index c49b7141634..6edb86bfa3f 100644
--- a/mysql-test/main/lock.result
+++ b/mysql-test/main/lock.result
@@ -500,7 +500,7 @@ connect  con1,localhost,root,,test;
 LOCK TABLE t2 WRITE;
 SET lock_wait_timeout= 1;
 FLUSH TABLES;
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+FLUSH TABLES t2;
 UNLOCK TABLES;
 disconnect con1;
 connection default;
diff --git a/mysql-test/main/lock.test b/mysql-test/main/lock.test
index ff77b4991c0..8a59f4082b1 100644
--- a/mysql-test/main/lock.test
+++ b/mysql-test/main/lock.test
@@ -609,8 +609,8 @@ LOCK TABLE t1 READ;
 --connect (con1,localhost,root,,test)
 LOCK TABLE t2 WRITE;
 SET lock_wait_timeout= 1;
---error ER_LOCK_WAIT_TIMEOUT
 FLUSH TABLES;
+FLUSH TABLES t2;
  
 # Cleanup
 UNLOCK TABLES;
diff --git a/mysql-test/main/lock_multi.result b/mysql-test/main/lock_multi.result
index 52b4608fdd2..1d9528764df 100644
--- a/mysql-test/main/lock_multi.result
+++ b/mysql-test/main/lock_multi.result
@@ -531,8 +531,9 @@ connect con3, localhost, root;
 connection default;
 LOCK TABLE t1 READ;
 connection con3;
-# Sending:
 FLUSH TABLES;
+# Sending:
+FLUSH TABLES t1;
 connection con2;
 SELECT * FROM t1;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
diff --git a/mysql-test/main/lock_multi.test b/mysql-test/main/lock_multi.test
index ce901126ce5..3167e6d82d6 100644
--- a/mysql-test/main/lock_multi.test
+++ b/mysql-test/main/lock_multi.test
@@ -931,13 +931,19 @@ connection default;
 LOCK TABLE t1 READ;
 
 connection con3;
+
+# first test that flush tables doesn't block
+FLUSH TABLES;
+
+# Check the FLUSH TABLES t1 waits until table lock is released
+
 --echo # Sending:
---send FLUSH TABLES
+--send FLUSH TABLES t1
 
 connection con2;
 let $wait_condition=
   SELECT COUNT(*) = 1 FROM information_schema.processlist
-  WHERE state = "Waiting for table flush" AND info = "FLUSH TABLES";
+  WHERE state = "Waiting for table metadata lock" AND info = "FLUSH TABLES t1";
 --source include/wait_condition.inc
 --error ER_LOCK_WAIT_TIMEOUT
 SELECT * FROM t1;
diff --git a/mysql-test/main/lock_sync.result b/mysql-test/main/lock_sync.result
index 93182399958..bbdc1d43ba5 100644
--- a/mysql-test/main/lock_sync.result
+++ b/mysql-test/main/lock_sync.result
@@ -782,7 +782,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR opened';
 SET DEBUG_SYNC= 'now SIGNAL dropped';
 SET DEBUG_SYNC= 'now WAIT_FOR opened';
 # Sending:
-FLUSH TABLES;
+FLUSH TABLES t1;
 connection default;
 # Waiting for FLUSH TABLES to be blocked.
 SET DEBUG_SYNC= 'now SIGNAL dropped';
diff --git a/mysql-test/main/lock_sync.test b/mysql-test/main/lock_sync.test
index af8435f7fbb..1a8cd7bdbd3 100644
--- a/mysql-test/main/lock_sync.test
+++ b/mysql-test/main/lock_sync.test
@@ -974,12 +974,12 @@ SET DEBUG_SYNC= 'now WAIT_FOR opened';
 SET DEBUG_SYNC= 'now SIGNAL dropped';
 SET DEBUG_SYNC= 'now WAIT_FOR opened';
 --echo # Sending:
---send FLUSH TABLES
+--send FLUSH TABLES t1
 
 connection default;
 --echo # Waiting for FLUSH TABLES to be blocked.
 let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist
-  WHERE state= 'Waiting for table flush' AND info= 'FLUSH TABLES';
+  WHERE state= 'Waiting for table metadata lock' AND info= 'FLUSH TABLES t1';
 --source include/wait_condition.inc
 SET DEBUG_SYNC= 'now SIGNAL dropped';
 
diff --git a/mysql-test/main/mdl_sync.result b/mysql-test/main/mdl_sync.result
index bf659a3616f..7e90720dca5 100644
--- a/mysql-test/main/mdl_sync.result
+++ b/mysql-test/main/mdl_sync.result
@@ -2146,10 +2146,11 @@ flush tables t1, t2 with read lock;
 connection con1;
 # Wait till FLUSH TABLES <list> WITH READ LOCK stops.
 set debug_sync='now WAIT_FOR parked';
+flush tables;
 # Start a statement which will flush all tables and thus
 # invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK.
 # Sending:
-flush tables;
+flush tables t1;
 connection default;
 # Wait till the above FLUSH TABLES blocks.
 # Resume FLUSH TABLES <list> WITH READ LOCK, so it tries to open t2
diff --git a/mysql-test/main/mdl_sync.test b/mysql-test/main/mdl_sync.test
index 20f850a2744..31b2885ae45 100644
--- a/mysql-test/main/mdl_sync.test
+++ b/mysql-test/main/mdl_sync.test
@@ -2690,17 +2690,20 @@ connection con1;
 --echo # Wait till FLUSH TABLES <list> WITH READ LOCK stops.
 set debug_sync='now WAIT_FOR parked';
 
+# Simple flush tables should not block
+flush tables;
+
 --echo # Start a statement which will flush all tables and thus
 --echo # invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK.
 --echo # Sending:
-send flush tables;
+send flush tables t1;
 
 connection default;
 --echo # Wait till the above FLUSH TABLES blocks.
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush" and
-        info = "flush tables";
+  where state = "Waiting for table metadata lock" and
+        info = "flush tables t1";
 --source include/wait_condition.inc
 
 --echo # Resume FLUSH TABLES <list> WITH READ LOCK, so it tries to open t2
diff --git a/mysql-test/main/truncate_coverage.result b/mysql-test/main/truncate_coverage.result
index 078de1ef3ab..9a343832b69 100644
--- a/mysql-test/main/truncate_coverage.result
+++ b/mysql-test/main/truncate_coverage.result
@@ -40,7 +40,7 @@ TRUNCATE TABLE m1;
 connection con2;
 SET DEBUG_SYNC= 'now WAIT_FOR opened';
 # Sending:
-FLUSH TABLES;
+FLUSH TABLES m1;
 connection default;
 # Waiting for FLUSH TABLES to be blocked.
 SET DEBUG_SYNC= 'now SIGNAL dropped';
diff --git a/mysql-test/main/truncate_coverage.test b/mysql-test/main/truncate_coverage.test
index 3351ce84232..1b793c6638c 100644
--- a/mysql-test/main/truncate_coverage.test
+++ b/mysql-test/main/truncate_coverage.test
@@ -81,12 +81,12 @@ SET DEBUG_SYNC= 'open_tables_after_open_and_process_table SIGNAL opened WAIT_FOR
 connection con2;
 SET DEBUG_SYNC= 'now WAIT_FOR opened';
 --echo # Sending:
---send FLUSH TABLES
+--send FLUSH TABLES m1
 
 connection default;
 --echo # Waiting for FLUSH TABLES to be blocked.
 let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist
-  WHERE state= 'Waiting for table flush' AND info= 'FLUSH TABLES';
+  WHERE state= 'Waiting for table metadata lock' AND info= 'FLUSH TABLES m1';
 --source include/wait_condition.inc
 SET DEBUG_SYNC= 'now SIGNAL dropped';
 
diff --git a/mysql-test/suite/handler/interface.result b/mysql-test/suite/handler/interface.result
index c4a169be185..8c39b15b6c9 100644
--- a/mysql-test/suite/handler/interface.result
+++ b/mysql-test/suite/handler/interface.result
@@ -175,6 +175,7 @@ c1
 connect  flush,localhost,root,,;
 connection flush;
 flush tables;
+flush table t1;
 connect  waiter,localhost,root,,;
 connection waiter;
 connection default;
@@ -258,10 +259,11 @@ a	b
 flush tables;
 handler t1 read a next;
 a	b
-0	a
+2	c
+flush tables t1;
 handler t1 read a next;
 a	b
-1	b
+0	a
 flush tables with read lock;
 handler t1 read a next;
 a	b
diff --git a/mysql-test/suite/handler/interface.test b/mysql-test/suite/handler/interface.test
index 15853dfdbf5..0ecdbf9c5cf 100644
--- a/mysql-test/suite/handler/interface.test
+++ b/mysql-test/suite/handler/interface.test
@@ -179,12 +179,13 @@ handler t1 open;
 handler t1 read first;
 connect (flush,localhost,root,,);
 connection flush;
-send flush tables;
+flush tables;
+send flush table t1;
 connect (waiter,localhost,root,,);
 connection waiter;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table flush";
+  where state = "Waiting for table metadata lock";
 --source include/wait_condition.inc
 connection default;
 handler t2 open;
@@ -282,6 +283,7 @@ handler t1 read a first;
 handler t1 read a next;
 flush tables;
 handler t1 read a next;
+flush tables t1;
 handler t1 read a next;
 flush tables with read lock;
 handler t1 read a next;
diff --git a/mysql-test/suite/maria/system_tables.result b/mysql-test/suite/maria/system_tables.result
index 32fddf6127e..c9944482638 100644
--- a/mysql-test/suite/maria/system_tables.result
+++ b/mysql-test/suite/maria/system_tables.result
@@ -3,6 +3,7 @@ LOCK TABLE t1 WRITE;
 connect  con1,localhost,root,,test;
 SET lock_wait_timeout= 2;
 FLUSH TABLES;
+FLUSH TABLES t1;
 connection default;
 CALL non_existing_sp;
 ERROR 42000: PROCEDURE test.non_existing_sp does not exist
diff --git a/mysql-test/suite/maria/system_tables.test b/mysql-test/suite/maria/system_tables.test
index 7b5c20ded85..950989fa5ca 100644
--- a/mysql-test/suite/maria/system_tables.test
+++ b/mysql-test/suite/maria/system_tables.test
@@ -12,8 +12,8 @@ LOCK TABLE t1 WRITE;
 
 --connect (con1,localhost,root,,test)
 SET lock_wait_timeout= 2;
---send
-  FLUSH TABLES;
+FLUSH TABLES;
+--send FLUSH TABLES t1
 --connection default
 --error ER_SP_DOES_NOT_EXIST
 CALL non_existing_sp;
diff --git a/mysql-test/suite/rpl/include/rpl_EE_err.test b/mysql-test/suite/rpl/include/rpl_EE_err.test
index 0b3fec1f605..fa135d12436 100644
--- a/mysql-test/suite/rpl/include/rpl_EE_err.test
+++ b/mysql-test/suite/rpl/include/rpl_EE_err.test
@@ -15,7 +15,7 @@
 -- source include/master-slave.inc
 
 eval create table t1 (a int) engine=$engine_type;
-flush tables;
+flush tables t1;
 let $MYSQLD_DATADIR= `select @@datadir`;
 remove_file $MYSQLD_DATADIR/test/t1.MYI ;
 drop table if exists t1;
diff --git a/mysql-test/suite/rpl/include/rpl_row_annotate.test b/mysql-test/suite/rpl/include/rpl_row_annotate.test
index 317a9c86539..8b4b704cef9 100644
--- a/mysql-test/suite/rpl/include/rpl_row_annotate.test
+++ b/mysql-test/suite/rpl/include/rpl_row_annotate.test
@@ -147,7 +147,7 @@ let $start_pos= `select @binlog_start_pos`;
 connection master;
 SET SESSION binlog_annotate_row_events = ON;
 INSERT DELAYED INTO test1.t4 VALUES (1,1);
-FLUSH TABLES;
+FLUSH TABLES test1.t4;
 SELECT * FROM test1.t4 ORDER BY a;
 
 sync_slave_with_master;
diff --git a/mysql-test/suite/rpl/include/rpl_row_delayed_ins.test b/mysql-test/suite/rpl/include/rpl_row_delayed_ins.test
index bad308ff814..03c7b5282a8 100644
--- a/mysql-test/suite/rpl/include/rpl_row_delayed_ins.test
+++ b/mysql-test/suite/rpl/include/rpl_row_delayed_ins.test
@@ -10,7 +10,7 @@ eval create table t1(a int not null primary key) engine=$engine_type;
 insert delayed into t1 values (1);
 insert delayed into t1 values (2);
 insert delayed into t1 values (3);
-flush tables;
+flush tables t1;
 SELECT * FROM t1 ORDER BY a;
 sync_slave_with_master;
 
diff --git a/mysql-test/suite/rpl/r/rpl_EE_err.result b/mysql-test/suite/rpl/r/rpl_EE_err.result
index 1f605935005..0b0ee84229f 100644
--- a/mysql-test/suite/rpl/r/rpl_EE_err.result
+++ b/mysql-test/suite/rpl/r/rpl_EE_err.result
@@ -1,7 +1,7 @@
 include/master-slave.inc
 [connection master]
 create table t1 (a int) engine=myisam;
-flush tables;
+flush tables t1;
 drop table if exists t1;
 Warnings:
 Warning	1017	Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory")
diff --git a/mysql-test/suite/rpl/r/rpl_row_annotate_do.result b/mysql-test/suite/rpl/r/rpl_row_annotate_do.result
index 52f7b180fae..ba1922566bf 100644
--- a/mysql-test/suite/rpl/r/rpl_row_annotate_do.result
+++ b/mysql-test/suite/rpl/r/rpl_row_annotate_do.result
@@ -180,7 +180,7 @@ slave-bin.000001	#	Rotate	2	#	slave-bin.000002;pos=4
 connection master;
 SET SESSION binlog_annotate_row_events = ON;
 INSERT DELAYED INTO test1.t4 VALUES (1,1);
-FLUSH TABLES;
+FLUSH TABLES test1.t4;
 SELECT * FROM test1.t4 ORDER BY a;
 a	b
 1	1
diff --git a/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result b/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result
index c657cf2fbb5..65535d5d417 100644
--- a/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result
+++ b/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result
@@ -160,7 +160,7 @@ slave-bin.000001	#	Rotate	2	#	slave-bin.000002;pos=4
 connection master;
 SET SESSION binlog_annotate_row_events = ON;
 INSERT DELAYED INTO test1.t4 VALUES (1,1);
-FLUSH TABLES;
+FLUSH TABLES test1.t4;
 SELECT * FROM test1.t4 ORDER BY a;
 a	b
 1	1
diff --git a/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result b/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result
index 978c3d30dbd..4d439c202f5 100644
--- a/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result
+++ b/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result
@@ -5,7 +5,7 @@ create table t1(a int not null primary key) engine=myisam;
 insert delayed into t1 values (1);
 insert delayed into t1 values (2);
 insert delayed into t1 values (3);
-flush tables;
+flush tables t1;
 SELECT * FROM t1 ORDER BY a;
 a
 1
diff --git a/mysql-test/suite/vcol/r/update.result b/mysql-test/suite/vcol/r/update.result
index 5a6355e1773..5b67c9dd0f7 100644
--- a/mysql-test/suite/vcol/r/update.result
+++ b/mysql-test/suite/vcol/r/update.result
@@ -122,7 +122,7 @@ select * from t;
 a	b	c	d	e
 10	5	5	5	5
 replace delayed t (a,b,d) values (10,6,6);
-flush tables;
+flush tables t;
 check table t;
 Table	Op	Msg_type	Msg_text
 test.t	check	status	OK
@@ -130,7 +130,7 @@ select * from t;
 a	b	c	d	e
 10	6	6	6	6
 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
-flush tables;
+flush tables t;
 check table t;
 Table	Op	Msg_type	Msg_text
 test.t	check	status	OK
diff --git a/mysql-test/suite/vcol/r/update_binlog.result b/mysql-test/suite/vcol/r/update_binlog.result
index d4102fc460a..c29200513b0 100644
--- a/mysql-test/suite/vcol/r/update_binlog.result
+++ b/mysql-test/suite/vcol/r/update_binlog.result
@@ -124,7 +124,7 @@ select * from t;
 a	b	c	d	e
 10	5	5	5	5
 replace delayed t (a,b,d) values (10,6,6);
-flush tables;
+flush tables t;
 check table t;
 Table	Op	Msg_type	Msg_text
 test.t	check	status	OK
@@ -132,7 +132,7 @@ select * from t;
 a	b	c	d	e
 10	6	6	6	6
 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
-flush tables;
+flush tables t;
 check table t;
 Table	Op	Msg_type	Msg_text
 test.t	check	status	OK
@@ -304,7 +304,7 @@ select * from t;
 a	b	c	d	e
 10	5	5	5	5
 replace delayed t (a,b,d) values (10,6,6);
-flush tables;
+flush tables t;
 check table t;
 Table	Op	Msg_type	Msg_text
 test.t	check	status	OK
@@ -312,7 +312,7 @@ select * from t;
 a	b	c	d	e
 10	6	6	6	6
 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
-flush tables;
+flush tables t;
 check table t;
 Table	Op	Msg_type	Msg_text
 test.t	check	status	OK
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index f3edfa9c026..9eb4616949c 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -182,16 +182,36 @@ a	b	c
 2	3	y
 0	1	y,n
 drop table t1,t2;
-CREATE TABLE t1 (
+SET @old_debug= @@global.debug;
+SET @old_debug= @@global.debug;
+SET GLOBAL debug_dbug= "+d,write_delay_wakeup";
+CREATE TABLE t1 (a int,
 ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
 ) ENGINE=MyISAM;
-INSERT INTO t1 (tsv) VALUES (DEFAULT);
-INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
+# First test FLUSH TABLES
+INSERT INTO t1 (a,tsv) VALUES (1,DEFAULT);
+INSERT DELAYED INTO t1 (a,tsv) VALUES (2,DEFAULT);
 FLUSH TABLES;
+SELECT COUNT(*) > 0 FROM t1;
+COUNT(*) > 0
+1
+# Then test FLUSH TABLES t1;
+INSERT INTO t1 (a,tsv) VALUES (3,DEFAULT);
+INSERT DELAYED INTO t1 (a,tsv) VALUES (4,DEFAULT);
+FLUSH TABLES t1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+4
+# Then test FLUSH TABLES WITH READ LOCK;
+INSERT INTO t1 (a,tsv) VALUES (5,DEFAULT);
+INSERT DELAYED INTO t1 (a,tsv) VALUES (6,DEFAULT);
+FLUSH TABLES WITH READ LOCK;
 SELECT COUNT(*) FROM t1;
 COUNT(*)
-2
+6
+set GLOBAL debug_dbug= @old_debug;
+unlock tables;
 DROP TABLE t1;
 #
 # MDEV-4823 Server crashes in Item_func_not::fix_fields on
diff --git a/mysql-test/suite/vcol/t/update.test b/mysql-test/suite/vcol/t/update.test
index 53189ee3219..e1351986968 100644
--- a/mysql-test/suite/vcol/t/update.test
+++ b/mysql-test/suite/vcol/t/update.test
@@ -93,10 +93,10 @@ check table t; select * from t;
 insert t(a,b,d) select 10,4,4 on duplicate key update b=5, d=5;
 check table t; select * from t;
 replace delayed t (a,b,d) values (10,6,6);
-flush tables;
+flush tables t;
 check table t; select * from t;
 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
-flush tables;
+flush tables t;
 check table t; select * from t;
 --write_file $MYSQLTEST_VARDIR/tmp/vblobs.txt
 10	8	foo	8	foo
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test
index b351e1eb4a6..95b707e3e12 100644
--- a/mysql-test/suite/vcol/t/vcol_misc.test
+++ b/mysql-test/suite/vcol/t/vcol_misc.test
@@ -1,4 +1,5 @@
 --source include/have_ucs2.inc
+--source include/have_debug.inc
 
 let $MYSQLD_DATADIR= `select @@datadir`;
 
@@ -184,19 +185,35 @@ drop table t1,t2;
 # Bug mdev-3938: INSERT DELAYED for a table with virtual columns
 #
 
-CREATE TABLE t1 (
+SET @old_debug= @@global.debug;
+SET @old_debug= @@global.debug;
+SET GLOBAL debug_dbug= "+d,write_delay_wakeup";
+CREATE TABLE t1 (a int,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
 ) ENGINE=MyISAM;
 
-INSERT INTO t1 (tsv) VALUES (DEFAULT);
-
-INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
-
+--echo # First test FLUSH TABLES
+INSERT INTO t1 (a,tsv) VALUES (1,DEFAULT);
+INSERT DELAYED INTO t1 (a,tsv) VALUES (2,DEFAULT);
 FLUSH TABLES;
+# Count may be 1 or 2, depending on FLUSH happened before or after delayed
+SELECT COUNT(*) > 0 FROM t1;
 
+--echo # Then test FLUSH TABLES t1;
+INSERT INTO t1 (a,tsv) VALUES (3,DEFAULT);
+INSERT DELAYED INTO t1 (a,tsv) VALUES (4,DEFAULT);
+FLUSH TABLES t1;
 SELECT COUNT(*) FROM t1;
 
+--echo # Then test FLUSH TABLES WITH READ LOCK;
+
+INSERT INTO t1 (a,tsv) VALUES (5,DEFAULT);
+INSERT DELAYED INTO t1 (a,tsv) VALUES (6,DEFAULT);
+FLUSH TABLES WITH READ LOCK;
+SELECT COUNT(*) FROM t1;
+set GLOBAL debug_dbug= @old_debug;
+unlock tables;
 DROP TABLE t1;
 
 --echo #