MDEV-24119 MDL BF-BF Conflict caused by TRUNCATE TABLE

A follow-up fix, for original fix for MDEV-21577, which did not handle well
temporary tables.

OPTIMIZE and REPAIR TABLE statements can take a list of tables as argument,
and some of the tables may be temporary. Proper handling of temporary tables
is to skip them and continue working on the real tables. The bad version, skipped all tables,
if a single temporary table was in the argument list. And this resulted so that FK parent
tables were not scnanned for the remaining real tables. Some mtr tests, using OPTIMIZE or REPAIR
for temporary tables caused regressions bacause of this, e.g. galera.galera_optimize_analyze_multi

The fix in this PR opens temporary and real tables first, and in the table handling loop skips
temporary tables, FK parent scanning is done only for real tables.

The test has new scenario for OPTIMIZE and REPAIR issued for two tables of which one is temporary table.

Reviewed-by: Jan Lindström <jan.lindstrom@mariadb.com>

1 files changed, 106 insertions, 0 deletions

diff --git a/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result b/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result
index f0d0d61e58f..5f09345b79b 100644
--- a/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result
+++ b/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result
@@ -104,6 +104,7 @@ connection node_1;
 BEGIN;
 connection node_1b;
 BEGIN;
+connection node_1a;
 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
 connection node_2;
 OPTIMIZE TABLE c2 ;
@@ -148,6 +149,58 @@ EXPECT_2
 DROP TABLE c1, c2;
 DROP TABLE p1, p2;
 ######################################################################
+# Test for OPTIMIZE 
+######################################################################
+connection node_1;
+SET SESSION wsrep_sync_wait=0;
+CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
+INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
+CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
+INSERT INTO c1 VALUES (1,1);
+######################################################################
+#
+# Scenario #4: DML working on FK parent table tries to replicate, but
+#              fails in certification for earlier DDL on child table
+#              and another temporary table. TMP table should be skipped
+#              but FK child table should be replicated with proper keys
+#
+######################################################################
+connection node_1;
+BEGIN;
+SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
+connection node_2;
+CREATE TEMPORARY TABLE tmp (i int);
+OPTIMIZE TABLE c1, tmp ;
+Table	Op	Msg_type	Msg_text
+test.c1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.c1	optimize	status	OK
+test.tmp	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.tmp	optimize	status	OK
+DROP TABLE tmp;
+connection node_1a;
+SET SESSION wsrep_on = 0;
+SET SESSION wsrep_on = 1;
+SET GLOBAL wsrep_provider_options = 'dbug=';
+connection node_1;
+UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
+COMMIT;
+connection node_1a;
+SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
+connection node_1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+SELECT 'I deadlocked';
+I deadlocked
+I deadlocked
+SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
+EXPECT_1
+1
+connection node_2;
+SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
+EXPECT_1
+1
+DROP TABLE c1;
+DROP TABLE p1;
+######################################################################
 # Test for REPAIR 
 ######################################################################
 ######################################################################
@@ -243,6 +296,7 @@ connection node_1;
 BEGIN;
 connection node_1b;
 BEGIN;
+connection node_1a;
 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
 connection node_2;
 REPAIR TABLE c2 ;
@@ -286,6 +340,56 @@ EXPECT_2
 DROP TABLE c1, c2;
 DROP TABLE p1, p2;
 ######################################################################
+# Test for REPAIR 
+######################################################################
+connection node_1;
+SET SESSION wsrep_sync_wait=0;
+CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
+INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
+CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
+INSERT INTO c1 VALUES (1,1);
+######################################################################
+#
+# Scenario #4: DML working on FK parent table tries to replicate, but
+#              fails in certification for earlier DDL on child table
+#              and another temporary table. TMP table should be skipped
+#              but FK child table should be replicated with proper keys
+#
+######################################################################
+connection node_1;
+BEGIN;
+SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
+connection node_2;
+CREATE TEMPORARY TABLE tmp (i int);
+REPAIR TABLE c1, tmp ;
+Table	Op	Msg_type	Msg_text
+test.c1	repair	note	The storage engine for the table doesn't support repair
+test.tmp	repair	note	The storage engine for the table doesn't support repair
+DROP TABLE tmp;
+connection node_1a;
+SET SESSION wsrep_on = 0;
+SET SESSION wsrep_on = 1;
+SET GLOBAL wsrep_provider_options = 'dbug=';
+connection node_1;
+UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
+COMMIT;
+connection node_1a;
+SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
+connection node_1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+SELECT 'I deadlocked';
+I deadlocked
+I deadlocked
+SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
+EXPECT_1
+1
+connection node_2;
+SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
+EXPECT_1
+1
+DROP TABLE c1;
+DROP TABLE p1;
+######################################################################
 # Test for ALTER ENGINE=INNODB
 ######################################################################
 ######################################################################
@@ -377,6 +481,7 @@ connection node_1;
 BEGIN;
 connection node_1b;
 BEGIN;
+connection node_1a;
 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
 connection node_2;
 ALTER TABLE c2 ENGINE=INNODB;
@@ -509,6 +614,7 @@ connection node_1;
 BEGIN;
 connection node_1b;
 BEGIN;
+connection node_1a;
 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
 connection node_2;
 TRUNCATE TABLE c2 ;