MDEV-13568 gcol.innodb_virtual_debug_purge failed in buildbot with wrong result

The InnoDB background tasks can modify tables while LOCK TABLES...WRITE
is in effect. The purge of InnoDB history always worked like this in
MariaDB, but in MySQL 5.7 it sometimes yields to LOCK TABLES.

Also, make gcol.innodb_virtual_index run the purge for an UPDATE
before DROP TABLE is executed.

6 files changed, 18 insertions, 14 deletions

diff --git a/mysql-test/suite/gcol/disabled.def b/mysql-test/suite/gcol/disabled.def
deleted file mode 100644
index 37209a4c353..00000000000
--- a/mysql-test/suite/gcol/disabled.def
+++ /dev/null
@@ -1 +0,0 @@
-innodb_virtual_debug_purge : MDEV-13568 should purge yield to LOCK TABLES?
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
index 7d1d1d30198..ac4be0dcc6c 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
@@ -143,20 +143,15 @@ connect prevent_purge, localhost, root;
 start transaction with consistent snapshot;
 connection default;
 update t set a = repeat('m', 16000) where a like "aaa%";
-connect con1, localhost, root;
+connect lock_table, localhost, root;
 lock table t write;
 disconnect prevent_purge;
 connection default;
-select variable_value>1 from information_schema.global_status where variable_name='innodb_purge_trx_id_age';
-variable_value>1
-1
-disconnect con1;
+InnoDB		0 transactions not purged
+disconnect lock_table;
 start transaction with consistent snapshot;
 commit;
 InnoDB		0 transactions not purged
-select variable_value>1 from information_schema.global_status where variable_name='innodb_purge_trx_id_age';
-variable_value>1
-0
 set global debug_dbug=@old_dbug;
 drop table t;
 set debug_sync=reset;
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_index.result b/mysql-test/suite/gcol/r/innodb_virtual_index.result
index 375c62bd173..b1f7976c6c0 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_index.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_index.result
@@ -1,3 +1,5 @@
+SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;
 #
 # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
 # THE NUMBER OF VIRTUAL COLUMNS
@@ -86,6 +88,7 @@ DISTINCT I1.c14 AS y
 FROM t1 AS I1
 ORDER BY I1.c14);
 SET @@SESSION.sql_mode=default;
+InnoDB		0 transactions not purged
 DROP TABLE t1, t2, t3;
 #
 # Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER
@@ -194,3 +197,4 @@ ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
 VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY
 DROP TABLE t1;
+SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
index 2668e26c976..1862de268af 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
@@ -182,16 +182,15 @@ connect(prevent_purge, localhost, root);
 start transaction with consistent snapshot;
 connection default;
 update t set a = repeat('m', 16000) where a like "aaa%";
-connect(con1, localhost, root);
+connect(lock_table, localhost, root);
 lock table t write;
 disconnect prevent_purge;
 connection default;
-select variable_value>1 from information_schema.global_status where variable_name='innodb_purge_trx_id_age';
-disconnect con1;
+--source ../../innodb/include/wait_all_purged.inc
+disconnect lock_table;
 start transaction with consistent snapshot;
 commit;
 --source ../../innodb/include/wait_all_purged.inc
-select variable_value>1 from information_schema.global_status where variable_name='innodb_purge_trx_id_age';
 set global debug_dbug=@old_dbug;
 drop table t;
 
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_index.test b/mysql-test/suite/gcol/t/innodb_virtual_index.test
index 4c4cb2a2d05..432faeb65ae 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_index.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_index.test
@@ -1,5 +1,9 @@
 --source include/have_innodb.inc
 
+# Ensure that the history list length will actually be decremented by purge.
+SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;
+
 --echo #
 --echo # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
 --echo # THE NUMBER OF VIRTUAL COLUMNS
@@ -96,6 +100,7 @@ FROM t1 AS I1
 ORDER BY I1.c14);
 
 SET @@SESSION.sql_mode=default;
+--source ../../innodb/include/wait_all_purged.inc
 
 DROP TABLE t1, t2, t3;
 
@@ -218,3 +223,4 @@ ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
 VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
 
 DROP TABLE t1;
+SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
diff --git a/mysql-test/unstable-tests b/mysql-test/unstable-tests
index dff89fdf60d..4940faa944a 100644
--- a/mysql-test/unstable-tests
+++ b/mysql-test/unstable-tests
@@ -168,7 +168,8 @@ galera_3nodes.*                      : Suite is not stable yet
 
 gcol.innodb_virtual_basic            : Modified in 10.2.11
 gcol.innodb_virtual_debug            : Modified in 10.2.11
-gcol.innodb_virtual_debug_purge      : MDEV-13568 - Wrong result
+gcol.innodb_virtual_debug_purge      : Modified in 10.2.12
+gcol.innodb_virtual_index            : Modified in 10.2.12
 gcol.innodb_virtual_rebuild          : Added in 10.2.11
 gcol.innodb_virtual_stats            : Added in 10.2.12