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.

2 files changed, 40 insertions, 13 deletions

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;