MDEV-22953 main.flush_read_lock failed in buildbot with XAER_NOTA: Unknown XIDbb-10.5-sachin

Issue:- Since there is no waiting for the actual disconnection of the con_tmp
(which does XA prepare of test1), We can have a issue when test1 is not
prepared and we are calling rollback on test1 , giving XAER_NOTA: Unknown XID
error

Solution:- Wait for the complete disconnection of con_tmp

2 files changed, 20 insertions, 8 deletions

diff --git a/mysql-test/main/flush_read_lock.result b/mysql-test/main/flush_read_lock.result
index 9a81cae1724..62a94c38124 100644
--- a/mysql-test/main/flush_read_lock.result
+++ b/mysql-test/main/flush_read_lock.result
@@ -1383,19 +1383,22 @@ insert into t3_trans values (1);
 xa end 'test1';
 xa prepare 'test1';
 # Disconnect temporary connection
+set debug_sync='thread_end SIGNAL test1_prepare';
 disconnect con_tmp;
+connection con1;
+set debug_sync='now WAIT_FOR test1_prepare';
 # Create temporary connection for XA transaction.
-connect  con_tmp,localhost,root,,;
+connect  con_tmp1,localhost,root,,;
 xa start 'test2';
 insert into t3_trans values (2);
 xa end 'test2';
 xa prepare 'test2';
 # Disconnect temporary connection
-set debug_sync='thread_end SIGNAL detached';
-disconnect con_tmp;
+set debug_sync='thread_end SIGNAL test2_prepare';
+disconnect con_tmp1;
 # Switching to connection 'con1'.
 connection con1;
-set debug_sync='now WAIT_FOR detached';
+set debug_sync='now WAIT_FOR test2_prepare';
 flush tables with read lock;
 # Switching to connection 'default'.
 connection default;
diff --git a/mysql-test/main/flush_read_lock.test b/mysql-test/main/flush_read_lock.test
index 205b8b302ea..d73820425d2 100644
--- a/mysql-test/main/flush_read_lock.test
+++ b/mysql-test/main/flush_read_lock.test
@@ -1690,25 +1690,34 @@ insert into t3_trans values (1);
 xa end 'test1';
 xa prepare 'test1';
 --echo # Disconnect temporary connection
+set debug_sync='thread_end SIGNAL test1_prepare';
 disconnect con_tmp;
+connection $con_aux1;
+set debug_sync='now WAIT_FOR test1_prepare';
+
+
 --echo # Create temporary connection for XA transaction.
-connect (con_tmp,localhost,root,,);
+connect (con_tmp1,localhost,root,,);
 xa start 'test2';
 insert into t3_trans values (2);
 xa end 'test2';
 xa prepare 'test2';
 --echo # Disconnect temporary connection
-set debug_sync='thread_end SIGNAL detached';
-disconnect con_tmp;
+set debug_sync='thread_end SIGNAL test2_prepare';
+disconnect con_tmp1;
+
+
 --echo # Switching to connection '$con_aux1'.
 connection $con_aux1;
-set debug_sync='now WAIT_FOR detached';
+set debug_sync='now WAIT_FOR test2_prepare';
 flush tables with read lock;
+
 --echo # Switching to connection 'default'.
 connection default;
 --echo # Send XA ROLLBACK 'test1'
 --send xa rollback 'test1'
 --echo # Switching to connection '$con_aux1'.
+
 connection $con_aux1;
 --echo # Wait until XA ROLLBACK is blocked.
 let $wait_condition=