MDEV-24432 : galera.galera_fk_cascade_delete_debug MTR failed: query 'reap' failed: 1205: Lock wait timeout exceeded

Add wait_conditions to verify correct database state before
next operation.

4 files changed, 67 insertions, 33 deletions

diff --git a/mysql-test/suite/galera/r/galera_fk_cascade_delete.result b/mysql-test/suite/galera/r/galera_fk_cascade_delete.result
index a6c6504dc39..ba5feadb1ff 100644
--- a/mysql-test/suite/galera/r/galera_fk_cascade_delete.result
+++ b/mysql-test/suite/galera/r/galera_fk_cascade_delete.result
@@ -2,7 +2,6 @@
 # test phase with cascading foreign key through 3 tables
 #
 connection node_1;
-set wsrep_sync_wait=0;
 CREATE TABLE grandparent (
 id INT NOT NULL PRIMARY KEY
 ) ENGINE=InnoDB;
@@ -24,15 +23,26 @@ INSERT INTO grandparent VALUES (1),(2);
 INSERT INTO parent VALUES (1,1), (2,2);
 INSERT INTO child VALUES (1,1), (2,2);
 connection node_2;
-set wsrep_sync_wait=0;
 DELETE FROM grandparent WHERE id = 1;
+SELECT * FROM grandparent;
+id
+2
+SELECT * FROM parent;
+id	grandparent_id
+2	2
+SELECT * FROM child;
+id	parent_id
+2	2
 connection node_1;
-SELECT COUNT(*), COUNT(*) = 0 FROM parent WHERE grandparent_id = 1;
-COUNT(*)	COUNT(*) = 0
-0	1
-SELECT COUNT(*), COUNT(*) = 0 FROM child WHERE parent_id = 1;
-COUNT(*)	COUNT(*) = 0
-0	1
+SELECT * FROM grandparent;
+id
+2
+SELECT * FROM parent;
+id	grandparent_id
+2	2
+SELECT * FROM child;
+id	parent_id
+2	2
 DROP TABLE child;
 DROP TABLE parent;
 DROP TABLE grandparent;
diff --git a/mysql-test/suite/galera/r/galera_fk_cascade_delete_debug.result b/mysql-test/suite/galera/r/galera_fk_cascade_delete_debug.result
index 89613b2856a..e2879c30a98 100644
--- a/mysql-test/suite/galera/r/galera_fk_cascade_delete_debug.result
+++ b/mysql-test/suite/galera/r/galera_fk_cascade_delete_debug.result
@@ -18,12 +18,10 @@ INSERT INTO child VALUES (1,'row one'), (2,'row two');
 connection node_2;
 DELETE FROM parent;
 connection node_1;
-SELECT COUNT(*), COUNT(*) = 0 FROM parent;
-COUNT(*)	COUNT(*) = 0
-0	1
-SELECT COUNT(*), COUNT(*) = 0 FROM child;
-COUNT(*)	COUNT(*) = 0
-0	1
+SELECT * FROM parent;
+id
+SELECT * FROM child;
+id	parent_id
 DROP TABLE child;
 DROP TABLE parent;
 #
@@ -44,6 +42,7 @@ ON DELETE CASCADE
 ) ENGINE=InnoDB;
 INSERT INTO parent VALUES (1);
 INSERT INTO child VALUES (1,0,1);
+connection node_2;
 connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1;
 SET GLOBAL debug_dbug = "d,sync.wsrep_apply_cb";
 connection node_2;
@@ -57,11 +56,9 @@ SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb";
 SET GLOBAL debug_dbug = "";
 SET DEBUG_SYNC = "RESET";
 connection node_1;
-SELECT COUNT(*), COUNT(*) = 0 FROM parent;
-COUNT(*)	COUNT(*) = 0
-0	1
-SELECT COUNT(*), COUNT(*) = 0 FROM child;
-COUNT(*)	COUNT(*) = 0
-0	1
+SELECT * FROM parent;
+id
+SELECT * FROM child;
+id	j	parent_id
 DROP TABLE child;
 DROP TABLE parent;
diff --git a/mysql-test/suite/galera/t/galera_fk_cascade_delete.test b/mysql-test/suite/galera/t/galera_fk_cascade_delete.test
index a3e0dbcf36f..49b54f0f7f0 100644
--- a/mysql-test/suite/galera/t/galera_fk_cascade_delete.test
+++ b/mysql-test/suite/galera/t/galera_fk_cascade_delete.test
@@ -9,7 +9,6 @@
 --echo #
 
 --connection node_1
-set wsrep_sync_wait=0;
 
 CREATE TABLE grandparent (
     id INT NOT NULL PRIMARY KEY
@@ -36,8 +35,12 @@ INSERT INTO parent VALUES (1,1), (2,2);
 INSERT INTO child VALUES (1,1), (2,2);
 
 --connection node_2
-set wsrep_sync_wait=0;
-
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'grandparent'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'parent'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'child'
+--source include/wait_condition.inc
 --let $wait_condition = SELECT COUNT(*) = 2 FROM child;
 --source include/wait_condition.inc
 --let $wait_condition = SELECT COUNT(*) = 2 FROM parent;
@@ -46,6 +49,10 @@ set wsrep_sync_wait=0;
 --source include/wait_condition.inc
 DELETE FROM grandparent WHERE id = 1;
 
+SELECT * FROM grandparent;
+SELECT * FROM parent;
+SELECT * FROM child;
+
 --connection node_1
 --let $wait_condition = SELECT COUNT(*) = 1 FROM child;
 --source include/wait_condition.inc
@@ -53,8 +60,10 @@ DELETE FROM grandparent WHERE id = 1;
 --source include/wait_condition.inc
 --let $wait_condition = SELECT COUNT(*) = 1 FROM grandparent;
 --source include/wait_condition.inc
-SELECT COUNT(*), COUNT(*) = 0 FROM parent WHERE grandparent_id = 1;
-SELECT COUNT(*), COUNT(*) = 0 FROM child WHERE parent_id = 1;
+
+SELECT * FROM grandparent;
+SELECT * FROM parent;
+SELECT * FROM child;
 
 DROP TABLE child;
 DROP TABLE parent;
diff --git a/mysql-test/suite/galera/t/galera_fk_cascade_delete_debug.test b/mysql-test/suite/galera/t/galera_fk_cascade_delete_debug.test
index f38c028b7d6..d902783ed64 100644
--- a/mysql-test/suite/galera/t/galera_fk_cascade_delete_debug.test
+++ b/mysql-test/suite/galera/t/galera_fk_cascade_delete_debug.test
@@ -25,16 +25,25 @@ INSERT INTO parent VALUES ('row one'), ('row two');
 INSERT INTO child VALUES (1,'row one'), (2,'row two');
 
 --connection node_2
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'parent'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'child'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 2 FROM parent;
+--source include/wait_condition.inc
 --let $wait_condition = SELECT COUNT(*) = 2 FROM child;
 --source include/wait_condition.inc
+
 DELETE FROM parent;
 
 --connection node_1
+--let $wait_condition = SELECT COUNT(*) = 0 FROM parent;
+--source include/wait_condition.inc
 --let $wait_condition = SELECT COUNT(*) = 0 FROM child;
 --source include/wait_condition.inc
 
-SELECT COUNT(*), COUNT(*) = 0 FROM parent;
-SELECT COUNT(*), COUNT(*) = 0 FROM child;
+SELECT * FROM parent;
+SELECT * FROM child;
 
 DROP TABLE child;
 DROP TABLE parent;
@@ -61,20 +70,27 @@ CREATE TABLE child (
 INSERT INTO parent VALUES (1);
 INSERT INTO child VALUES (1,0,1);
 
+--connection node_2
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'parent'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'child'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM parent;
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM child;
+--source include/wait_condition.inc
+
 # block applier before applying
 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
 SET GLOBAL debug_dbug = "d,sync.wsrep_apply_cb";
 
 --connection node_2
---let $wait_condition = SELECT COUNT(*) = 1 FROM child;
---source include/wait_condition.inc
 DELETE FROM parent;
 
 --connection node_1a
 # wait until applier has reached the sync point
 SET SESSION DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached";
 
-
 --connection node_1
 # issue conflicting write to child table, it should fail in certification
 --error ER_LOCK_DEADLOCK
@@ -88,11 +104,13 @@ SET DEBUG_SYNC = "RESET";
 
 --connection node_1
 --reap
-
+--let $wait_condition = SELECT COUNT(*) = 0 FROM parent;
+--source include/wait_condition.inc
 --let $wait_condition = SELECT COUNT(*) = 0 FROM child;
 --source include/wait_condition.inc
-SELECT COUNT(*), COUNT(*) = 0 FROM parent;
-SELECT COUNT(*), COUNT(*) = 0 FROM child;
+
+SELECT * FROM parent;
+SELECT * FROM child;
 
 DROP TABLE child;
 DROP TABLE parent;