MDEV-15855 Deadlock between purge thread and DDL statement

Problem:
========
Truncate operation holds MDL on the table (t1) and tries to
acquire InnoDB dict_operation_lock. Purge holds dict_operation_lock
and tries to acquire MDL on the table (t1) to evaluate virtual
column expressions for indexed virtual columns.
It leads to deadlock of purge and truncate table (DDL).

Solution:
=========
If purge tries to acquire MDL on the table then it should do the following:

i) Purge should release all innodb latches (including dict_operation_lock)
before acquiring metadata lock on the table.

ii) After acquiring metadata lock on the table, it should check whether the
table was dropped or renamed. If the table is dropped then purge should
ignore the undo log record. If the table is renamed then it should
release the old MDL and acquire MDL on the new name.

iii) Once purge acquires MDL, it should use the SQL table handle for all
the remaining virtual index for the purge record.

purge_node_t: Introduce new virtual column information to know whether
the MDL was acquired successfully.

This is joint work with Marko Mäkelä.

2 files changed, 74 insertions, 2 deletions

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 309d8e8f04a..3fbc4576ece 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
@@ -175,8 +175,38 @@ SET DEBUG_SYNC='now WAIT_FOR halfway';
 COMMIT;
 InnoDB		0 transactions not purged
 SET DEBUG_SYNC='now SIGNAL purged';
-disconnect prevent_purge;
 connection default;
 DROP TABLE t1;
+CREATE TABLE t1 (y YEAR, vy YEAR AS (y) VIRTUAL UNIQUE, pk INT PRIMARY KEY)
+ENGINE=InnoDB;
+INSERT INTO t1 (pk,y) VALUES (1,2022);
+CREATE TABLE t2(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
+SET GLOBAL debug_dbug = '+d,ib_purge_virtual_index_callback';
+BEGIN;
+INSERT INTO t2(f1) VALUES(1);
+connection prevent_purge;
+SET DEBUG_SYNC=RESET;
+start transaction with consistent snapshot;
+connection default;
+COMMIT;
+connect truncate,localhost,root,,;
+REPLACE INTO t1(pk, y) SELECT pk,y FROM t1;
+SET DEBUG_SYNC='row_trunc_before_dict_lock SIGNAL commit WAIT_FOR release';
+TRUNCATE TABLE t1;
+connection prevent_purge;
+SET DEBUG_SYNC='now WAIT_FOR commit';
+COMMIT;
+SET DEBUG_SYNC='now SIGNAL purge_start';
+disconnect prevent_purge;
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR purge_start';
+InnoDB		1 transactions not purged
+SET DEBUG_SYNC='now SIGNAL release';
+SET GLOBAL debug_dbug=@old_dbug;
+connection truncate;
+disconnect truncate;
+connection default;
+InnoDB		0 transactions not purged
+DROP TABLE t1, t2;
 set debug_sync=reset;
 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 7feeee768ff..845881a6d07 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
@@ -217,12 +217,54 @@ SET DEBUG_SYNC='now WAIT_FOR halfway';
 COMMIT;
 --source ../../innodb/include/wait_all_purged.inc
 SET DEBUG_SYNC='now SIGNAL purged';
-disconnect prevent_purge;
 
 connection default;
 reap;
 DROP TABLE t1;
 
+CREATE TABLE t1 (y YEAR, vy YEAR AS (y) VIRTUAL UNIQUE, pk INT PRIMARY KEY)
+ENGINE=InnoDB;
+
+INSERT INTO t1 (pk,y) VALUES (1,2022);
+CREATE TABLE t2(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
+
+SET GLOBAL debug_dbug = '+d,ib_purge_virtual_index_callback';
+
+BEGIN;
+INSERT INTO t2(f1) VALUES(1);
+connection prevent_purge;
+SET DEBUG_SYNC=RESET;
+start transaction with consistent snapshot;
+connection default;
+COMMIT;
+
+connect(truncate,localhost,root,,);
+REPLACE INTO t1(pk, y) SELECT pk,y FROM t1;
+SET DEBUG_SYNC='row_trunc_before_dict_lock SIGNAL commit WAIT_FOR release';
+send TRUNCATE TABLE t1;
+
+connection prevent_purge;
+SET DEBUG_SYNC='now WAIT_FOR commit';
+COMMIT;
+SET DEBUG_SYNC='now SIGNAL purge_start';
+disconnect prevent_purge;
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR purge_start';
+let $wait_all_purged=1;
+--source ../../innodb/include/wait_all_purged.inc
+let $wait_all_purged=0;
+SET DEBUG_SYNC='now SIGNAL release';
+SET GLOBAL debug_dbug=@old_dbug;
+
+connection truncate;
+reap;
+disconnect truncate;
+
+connection default;
+--source ../../innodb/include/wait_all_purged.inc
+DROP TABLE t1, t2;
+
 --source include/wait_until_count_sessions.inc
 set debug_sync=reset;
 SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;