MDEV-742 XA PREPAREd transaction survive disconnect/server restart

Lifted long standing limitation to the XA of rolling it back at the
transaction's
connection close even if the XA is prepared.

Prepared XA-transaction is made to sustain connection close or server
restart.
The patch consists of

    - binary logging extension to write prepared XA part of
      transaction signified with
      its XID in a new XA_prepare_log_event. The concusion part -
      with Commit or Rollback decision - is logged separately as
      Query_log_event.
      That is in the binlog the XA consists of two separate group of
      events.

      That makes the whole XA possibly interweaving in binlog with
      other XA:s or regular transaction but with no harm to
      replication and data consistency.

      Gtid_log_event receives two more flags to identify which of the
      two XA phases of the transaction it represents. With either flag
      set also XID info is added to the event.

      When binlog is ON on the server XID::formatID is
      constrained to 4 bytes.

    - engines are made aware of the server policy to keep up user
      prepared XA:s so they (Innodb, rocksdb) don't roll them back
      anymore at their disconnect methods.

    - slave applier is refined to cope with two phase logged XA:s
      including parallel modes of execution.

This patch does not address crash-safe logging of the new events which
is being addressed by MDEV-21469.

CORNER CASES: read-only, pure myisam, binlog-*, @@skip_log_bin, etc

Are addressed along the following policies.
1. The read-only at reconnect marks XID to fail for future
   completion with ER_XA_RBROLLBACK.

2. binlog-* filtered XA when it changes engine data is regarded as
   loggable even when nothing got cached for binlog.  An empty
   XA-prepare group is recorded. Consequent Commit-or-Rollback
   succeeds in the Engine(s) as well as recorded into binlog.

3. The same applies to the non-transactional engine XA.

4. @@skip_log_bin=OFF does not record anything at XA-prepare
   (obviously), but the completion event is recorded into binlog to
   admit inconsistency with slave.

The following actions are taken by the patch.

At XA-prepare:
   when empty binlog cache - don't do anything to binlog if RO,
   otherwise write empty XA_prepare (assert(binlog-filter case)).

At Disconnect:
   when Prepared && RO (=> no binlogging was done)
     set Xid_cache_element::error := ER_XA_RBROLLBACK
     *keep* XID in the cache, and rollback the transaction.

At XA-"complete":
   Discover the error, if any don't binlog the "complete",
   return the error to the user.

Kudos
-----
Alexey Botchkov took to drive this work initially.
Sergei Golubchik, Sergei Petrunja, Marko Mäkelä provided a number of
good recommendations.
Sergei Voitovich made a magnificent review and improvements to the code.
They all deserve a bunch of thanks for making this work done!

11 files changed, 1373 insertions, 9 deletions

diff --git a/mysql-test/main/flush_read_lock.result b/mysql-test/main/flush_read_lock.result
index 0f8c2ce9fb9..be710050139 100644
--- a/mysql-test/main/flush_read_lock.result
+++ b/mysql-test/main/flush_read_lock.result
@@ -1310,6 +1310,8 @@ unlock tables;
 # Check that XA non-COMMIT statements are not and COMMIT is
 # blocked by active FTWRL in another connection
 #
+# XA COMMIT, XA ROLLBACK and XA PREPARE does take COMMIT lock to ensure
+# that nothing is written to bin log and redo log under FTWRL mode.
 connection con1;
 flush tables with read lock;
 connection default;
@@ -1322,11 +1324,25 @@ connection con1;
 flush tables with read lock;
 connection default;
 xa end 'test1';
-xa prepare 'test1';
+xa prepare 'test1';;
+connection con1;
+unlock tables;
+# Switching to connection 'default'.
+connection default;
+# Reap XA PREPARE.
+# Switching to connection 'con1'.
+connection con1;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
+# Send XA ROLLBACK 'test1'
 xa rollback 'test1';
+# Switching to connection 'con1'.
 connection con1;
+# Wait until XA ROLLBACK is blocked.
 unlock tables;
 connection default;
+# Reap XA ROLLBACK
 xa start 'test1';
 insert into t3_trans values (1);
 connection con1;
@@ -1334,7 +1350,20 @@ flush tables with read lock;
 connection default;
 connection default;
 xa end 'test1';
+# Send XA PREPARE 'test1'
 xa prepare 'test1';
+# Switching to connection 'con1'.
+connection con1;
+# Wait until XA PREPARE is blocked.
+unlock tables;
+# Switching to connection 'default'.
+connection default;
+# Reap XA PREPARE.
+# Switching to connection 'con1'.
+connection con1;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
 # Send:
 xa commit 'test1';;
 connection con1;
@@ -1344,6 +1373,51 @@ connection default;
 # Reap XA COMMIT.
 delete from t3_trans;
 #
+# Check that XA COMMIT / ROLLBACK for prepared transaction from a
+# disconnected session is blocked by active FTWRL in another connection.
+#
+# Create temporary connection for XA transaction.
+connect  con_tmp,localhost,root,,;
+xa start 'test1';
+insert into t3_trans values (1);
+xa end 'test1';
+xa prepare 'test1';
+# Disconnect temporary connection
+disconnect con_tmp;
+# Create temporary connection for XA transaction.
+connect  con_tmp,localhost,root,,;
+xa start 'test2';
+insert into t3_trans values (2);
+xa end 'test2';
+xa prepare 'test2';
+# Disconnect temporary connection
+disconnect con_tmp;
+# Switching to connection 'con1'.
+connection con1;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
+# Send XA ROLLBACK 'test1'
+xa rollback 'test1';
+# Switching to connection 'con1'.
+connection con1;
+# Wait until XA ROLLBACK is blocked.
+unlock tables;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
+# Reap XA ROLLBACK
+# Send XA COMMIT
+xa commit 'test2';;
+# Switching to connection 'con1'.
+connection con1;
+# Wait until XA COMMIT is blocked.
+unlock tables;
+# Switching to connection 'default'.
+connection default;
+# Reap XA COMMIT.
+delete from t3_trans;
+#
 # Check that XA COMMIT blocks FTWRL in another connection.
 xa start 'test1';
 insert into t3_trans values (1);
diff --git a/mysql-test/main/flush_read_lock.test b/mysql-test/main/flush_read_lock.test
index 80512deac4e..4283358770c 100644
--- a/mysql-test/main/flush_read_lock.test
+++ b/mysql-test/main/flush_read_lock.test
@@ -1592,6 +1592,8 @@ unlock tables;
 --echo # Check that XA non-COMMIT statements are not and COMMIT is
 --echo # blocked by active FTWRL in another connection
 --echo #
+--echo # XA COMMIT, XA ROLLBACK and XA PREPARE does take COMMIT lock to ensure
+--echo # that nothing is written to bin log and redo log under FTWRL mode.
 connection $con_aux1;
 flush tables with read lock;
 connection default;
@@ -1604,11 +1606,37 @@ connection $con_aux1;
 flush tables with read lock;
 connection default;
 xa end 'test1';
-xa prepare 'test1';
-xa rollback 'test1';
+--send xa prepare 'test1';
 connection $con_aux1;
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for backup lock" and
+        info = "xa prepare 'test1'";
+--source include/wait_condition.inc
 unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA PREPARE.
+--reap
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+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=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for backup lock" and
+        info = "xa rollback 'test1'";
+--source include/wait_condition.inc
+unlock tables;
+connection default;
+--echo # Reap XA ROLLBACK
+--reap
 xa start 'test1';
 insert into t3_trans values (1);
 connection $con_aux1;
@@ -1616,7 +1644,27 @@ flush tables with read lock;
 connection default;
 connection default;
 xa end 'test1';
-xa prepare 'test1';
+--echo # Send XA PREPARE 'test1'
+--send xa prepare 'test1'
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Wait until XA PREPARE is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for backup lock" and
+        info = "xa prepare 'test1'";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA PREPARE.
+--reap
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+
 --echo # Send:
 --send xa commit 'test1';
 connection $con_aux1;
@@ -1632,6 +1680,64 @@ connection default;
 --reap
 delete from t3_trans;
 --echo #
+--echo # Check that XA COMMIT / ROLLBACK for prepared transaction from a
+--echo # disconnected session is blocked by active FTWRL in another connection.
+--echo #
+--echo # Create temporary connection for XA transaction.
+connect (con_tmp,localhost,root,,);
+xa start 'test1';
+insert into t3_trans values (1);
+xa end 'test1';
+xa prepare 'test1';
+--echo # Disconnect temporary connection
+disconnect con_tmp;
+--echo # Create temporary connection for XA transaction.
+connect (con_tmp,localhost,root,,);
+xa start 'test2';
+insert into t3_trans values (2);
+xa end 'test2';
+xa prepare 'test2';
+--echo # Disconnect temporary connection
+disconnect con_tmp;
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+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=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for backup lock" and
+        info = "xa rollback 'test1'";
+--source include/wait_condition.inc
+unlock tables;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA ROLLBACK
+--reap
+--echo # Send XA COMMIT
+--send xa commit 'test2';
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Wait until XA COMMIT is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for backup lock" and
+        info = "xa commit 'test2'";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA COMMIT.
+--reap
+delete from t3_trans;
+
+--echo #
 --echo # Check that XA COMMIT blocks FTWRL in another connection.
 xa start 'test1';
 insert into t3_trans values (1);
diff --git a/mysql-test/main/xa.result b/mysql-test/main/xa.result
index 5e03c8f75dc..e2a103a7a77 100644
--- a/mysql-test/main/xa.result
+++ b/mysql-test/main/xa.result
@@ -59,6 +59,9 @@ select * from t1;
 a
 20
 disconnect con1;
+xa rollback 'testb',0x2030405060,11;
+xa recover;
+formatID	gtrid_length	bqual_length	data
 connection default;
 xa start 'tr1';
 insert t1 values (40);
@@ -400,3 +403,61 @@ XA ROLLBACK 'xid1';
 #
 XA START 'gtrid', 'bqual', 0x80000000;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0x80000000' at line 1
+# MDEV-7974 related
+# Check XA state when lock_wait_timeout happens
+# More tests added to flush_read_lock.test
+connect  con_tmp,localhost,root,,;
+set session lock_wait_timeout=1;
+create table asd (a int) engine=innodb;
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+connection default;
+flush table with read lock;
+connection con_tmp;
+# PREPARE error will do auto rollback.
+xa prepare 'test1';
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+show errors;
+Level	Code	Message
+Error	1205	Lock wait timeout exceeded; try restarting transaction
+Error	1402	XA_RBROLLBACK: Transaction branch was rolled back
+connection default;
+unlock tables;
+connection con_tmp;
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+xa prepare 'test1';
+connection default;
+flush tables with read lock;
+connection con_tmp;
+# LOCK error during ROLLBACK will not alter transaction state.
+xa rollback 'test1';
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+show errors;
+Level	Code	Message
+Error	1205	Lock wait timeout exceeded; try restarting transaction
+Error	1401	XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
+xa recover;
+formatID	gtrid_length	bqual_length	data
+1	5	0	test1
+# LOCK error during COMMIT will not alter transaction state.
+xa commit 'test1';
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+show errors;
+Level	Code	Message
+Error	1205	Lock wait timeout exceeded; try restarting transaction
+Error	1401	XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
+xa recover;
+formatID	gtrid_length	bqual_length	data
+1	5	0	test1
+connection default;
+unlock tables;
+connection con_tmp;
+xa rollback 'test1';
+xa recover;
+formatID	gtrid_length	bqual_length	data
+drop table asd;
+disconnect con_tmp;
+connection default;
diff --git a/mysql-test/main/xa.test b/mysql-test/main/xa.test
index e23e3c6a428..7aaa72b1645 100644
--- a/mysql-test/main/xa.test
+++ b/mysql-test/main/xa.test
@@ -82,6 +82,8 @@ xa start 'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz';
 select * from t1;
 disconnect con1;
 --source include/wait_until_count_sessions.inc
+xa rollback 'testb',0x2030405060,11;
+xa recover;
 
 connection default;
 
@@ -541,3 +543,50 @@ XA ROLLBACK 'xid1';
 XA START 'gtrid', 'bqual', 0x80000000;
 
 --source include/wait_until_count_sessions.inc
+
+--echo # MDEV-7974 related
+--echo # Check XA state when lock_wait_timeout happens
+--echo # More tests added to flush_read_lock.test
+connect (con_tmp,localhost,root,,);
+set session lock_wait_timeout=1;
+create table asd (a int) engine=innodb;
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+connection default;
+flush table with read lock;
+connection con_tmp;
+--echo # PREPARE error will do auto rollback.
+--ERROR ER_LOCK_WAIT_TIMEOUT
+xa prepare 'test1';
+show errors;
+connection default;
+unlock tables;
+
+connection con_tmp;
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+xa prepare 'test1';
+connection default;
+flush tables with read lock;
+connection con_tmp;
+--echo # LOCK error during ROLLBACK will not alter transaction state.
+--ERROR ER_LOCK_WAIT_TIMEOUT
+xa rollback 'test1';
+show errors;
+xa recover;
+--echo # LOCK error during COMMIT will not alter transaction state.
+--ERROR ER_LOCK_WAIT_TIMEOUT
+xa commit 'test1';
+show errors;
+xa recover;
+connection default;
+unlock tables;
+connection con_tmp;
+xa rollback 'test1';
+xa recover;
+drop table asd;
+disconnect con_tmp;
+--source include/wait_until_disconnected.inc
+connection default;
diff --git a/mysql-test/main/xa_binlog.result b/mysql-test/main/xa_binlog.result
index 619a6e08b20..c45749d500f 100644
--- a/mysql-test/main/xa_binlog.result
+++ b/mysql-test/main/xa_binlog.result
@@ -18,14 +18,17 @@ a
 1
 2
 3
-SHOW BINLOG EVENTS LIMIT 3,9;
+SHOW BINLOG EVENTS LIMIT 3,12;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	#	Gtid	1	#	BEGIN GTID #-#-#
+master-bin.000001	#	Gtid	1	#	XA START X'786174657374',X'',1 GTID #-#-#
 master-bin.000001	#	Query	1	#	use `test`; INSERT INTO t1 VALUES (1)
-master-bin.000001	#	Query	1	#	COMMIT
+master-bin.000001	#	Query	1	#	XA END X'786174657374',X'',1
+master-bin.000001	#	XA_prepare	1	#	XA PREPARE X'786174657374',X'',1
+master-bin.000001	#	Gtid	1	#	GTID #-#-#
+master-bin.000001	#	Query	1	#	XA COMMIT X'786174657374',X'',1
 master-bin.000001	#	Gtid	1	#	BEGIN GTID #-#-#
 master-bin.000001	#	Query	1	#	use `test`; INSERT INTO t1 VALUES (2)
-master-bin.000001	#	Query	1	#	COMMIT
+master-bin.000001	#	Xid	1	#	COMMIT /* xid=XX */
 master-bin.000001	#	Gtid	1	#	BEGIN GTID #-#-#
 master-bin.000001	#	Query	1	#	use `test`; INSERT INTO t1 VALUES (3)
 master-bin.000001	#	Xid	1	#	COMMIT /* xid=XX */
diff --git a/mysql-test/main/xa_binlog.test b/mysql-test/main/xa_binlog.test
index ecbf1f4f066..91bca2ac8cb 100644
--- a/mysql-test/main/xa_binlog.test
+++ b/mysql-test/main/xa_binlog.test
@@ -27,6 +27,6 @@ SELECT * FROM t1 ORDER BY a;
 
 --replace_column 2 # 5 #
 --replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/
-SHOW BINLOG EVENTS LIMIT 3,9;
+SHOW BINLOG EVENTS LIMIT 3,12;
 
 DROP TABLE t1;
diff --git a/mysql-test/main/xa_prepared_binlog_off-master.opt b/mysql-test/main/xa_prepared_binlog_off-master.opt
new file mode 100644
index 00000000000..789275fa25e
--- /dev/null
+++ b/mysql-test/main/xa_prepared_binlog_off-master.opt
@@ -0,0 +1 @@
+--skip-log-bin
diff --git a/mysql-test/main/xa_prepared_binlog_off.result b/mysql-test/main/xa_prepared_binlog_off.result
new file mode 100644
index 00000000000..ca19f6cdfaf
--- /dev/null
+++ b/mysql-test/main/xa_prepared_binlog_off.result
@@ -0,0 +1,1044 @@
+call mtr.add_suppression("You need to use --log-bin to make --log-slave-updates work.");
+connection default;
+CREATE VIEW v_processlist  as SELECT * FROM performance_schema.threads where type = 'FOREGROUND';
+call mtr.add_suppression("Found 10 prepared XA transactions");
+CREATE TABLE t (a INT) ENGINE=innodb;
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@sql_log_bin = OFF;
+CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb;
+XA START   'trx1tmp';
+INSERT INTO tmp1 SET a=1;
+XA END     'trx1tmp';
+XA PREPARE 'trx1tmp';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@sql_log_bin = OFF;
+CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb;
+XA START   'trx2tmp';
+INSERT INTO tmp1 SET a=1;
+XA END     'trx2tmp';
+XA PREPARE 'trx2tmp';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@sql_log_bin = OFF;
+CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb;
+XA START   'trx3tmp';
+INSERT INTO tmp1 SET a=1;
+XA END     'trx3tmp';
+XA PREPARE 'trx3tmp';
+connection default;
+XA COMMIT 'trx1tmp';
+ERROR XAE04: XAER_NOTA: Unknown XID
+XA ROLLBACK 'trx1tmp';
+ERROR XAE04: XAER_NOTA: Unknown XID
+XA START 'trx1tmp';
+ERROR XAE08: XAER_DUPID: The XID already exists
+connection default;
+*** 3 prepared transactions must be in the list ***
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+connection conn1tmp;
+disconnect conn1tmp;
+connection default;
+XA COMMIT 'trx1tmp';
+KILL connection CONN_ID;
+XA COMMIT 'trx3tmp';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx1ro';
+SELECT * from t ORDER BY a;
+a
+XA END     'trx1ro';
+XA PREPARE 'trx1ro';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx2ro';
+SELECT * from t ORDER BY a;
+a
+XA END     'trx2ro';
+XA PREPARE 'trx2ro';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx3ro';
+SELECT * from t ORDER BY a;
+a
+XA END     'trx3ro';
+XA PREPARE 'trx3ro';
+connection default;
+*** 4 prepared transactions must be in the list ***
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+connection conn1ro;
+disconnect conn1ro;
+connection default;
+XA ROLLBACK 'trx1ro';
+KILL connection CONN_ID;
+XA ROLLBACK 'trx3ro';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx1empty';
+XA END     'trx1empty';
+XA PREPARE 'trx1empty';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx2empty';
+XA END     'trx2empty';
+XA PREPARE 'trx2empty';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx3empty';
+XA END     'trx3empty';
+XA PREPARE 'trx3empty';
+connection default;
+*** 5 prepared transactions must be in the list ***
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+connection conn1empty;
+disconnect conn1empty;
+connection default;
+XA COMMIT 'trx1empty';
+KILL connection CONN_ID;
+XA COMMIT 'trx3empty';
+connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx1unprepared';
+INSERT INTO t set a=0;
+XA END     'trx1unprepared';
+INSERT INTO t set a=0;
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
+XA START 'trx1unprepared';
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
+XA START 'trx1unprepared';
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
+disconnect conn1unprepared;
+connection default;
+XA COMMIT 'trx1unprepared';
+ERROR XAE04: XAER_NOTA: Unknown XID
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_0';
+INSERT INTO t SET a=0;
+XA END     'trx_0';
+XA PREPARE 'trx_0';
+disconnect conn0;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_1';
+INSERT INTO t SET a=1;
+XA END     'trx_1';
+XA PREPARE 'trx_1';
+disconnect conn1;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_2';
+INSERT INTO t SET a=2;
+XA END     'trx_2';
+XA PREPARE 'trx_2';
+disconnect conn2;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_3';
+INSERT INTO t SET a=3;
+XA END     'trx_3';
+XA PREPARE 'trx_3';
+disconnect conn3;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_4';
+INSERT INTO t SET a=4;
+XA END     'trx_4';
+XA PREPARE 'trx_4';
+disconnect conn4;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_5';
+INSERT INTO t SET a=5;
+XA END     'trx_5';
+XA PREPARE 'trx_5';
+disconnect conn5;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_6';
+INSERT INTO t SET a=6;
+XA END     'trx_6';
+XA PREPARE 'trx_6';
+disconnect conn6;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_7';
+INSERT INTO t SET a=7;
+XA END     'trx_7';
+XA PREPARE 'trx_7';
+disconnect conn7;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_8';
+INSERT INTO t SET a=8;
+XA END     'trx_8';
+XA PREPARE 'trx_8';
+disconnect conn8;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_9';
+INSERT INTO t SET a=9;
+XA END     'trx_9';
+XA PREPARE 'trx_9';
+disconnect conn9;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_10';
+INSERT INTO t SET a=10;
+XA END     'trx_10';
+XA PREPARE 'trx_10';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_11';
+INSERT INTO t SET a=11;
+XA END     'trx_11';
+XA PREPARE 'trx_11';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_12';
+INSERT INTO t SET a=12;
+XA END     'trx_12';
+XA PREPARE 'trx_12';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_13';
+INSERT INTO t SET a=13;
+XA END     'trx_13';
+XA PREPARE 'trx_13';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_14';
+INSERT INTO t SET a=14;
+XA END     'trx_14';
+XA PREPARE 'trx_14';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_15';
+INSERT INTO t SET a=15;
+XA END     'trx_15';
+XA PREPARE 'trx_15';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_16';
+INSERT INTO t SET a=16;
+XA END     'trx_16';
+XA PREPARE 'trx_16';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_17';
+INSERT INTO t SET a=17;
+XA END     'trx_17';
+XA PREPARE 'trx_17';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_18';
+INSERT INTO t SET a=18;
+XA END     'trx_18';
+XA PREPARE 'trx_18';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_19';
+INSERT INTO t SET a=19;
+XA END     'trx_19';
+XA PREPARE 'trx_19';
+connection default;
+KILL CONNECTION CONN_ID;
+connection default;
+XA ROLLBACK 'trx_0';
+XA ROLLBACK 'trx_1';
+XA ROLLBACK 'trx_2';
+XA ROLLBACK 'trx_3';
+XA ROLLBACK 'trx_4';
+XA COMMIT 'trx_5';
+XA COMMIT 'trx_6';
+XA COMMIT 'trx_7';
+XA COMMIT 'trx_8';
+XA COMMIT 'trx_9';
+# restart
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_0';
+INSERT INTO t SET a=0;
+XA END     'new_trx_0';
+XA PREPARE 'new_trx_0';
+disconnect conn_restart_0;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_1';
+INSERT INTO t SET a=1;
+XA END     'new_trx_1';
+XA PREPARE 'new_trx_1';
+disconnect conn_restart_1;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_2';
+INSERT INTO t SET a=2;
+XA END     'new_trx_2';
+XA PREPARE 'new_trx_2';
+disconnect conn_restart_2;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_3';
+INSERT INTO t SET a=3;
+XA END     'new_trx_3';
+XA PREPARE 'new_trx_3';
+disconnect conn_restart_3;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_4';
+INSERT INTO t SET a=4;
+XA END     'new_trx_4';
+XA PREPARE 'new_trx_4';
+disconnect conn_restart_4;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_5';
+INSERT INTO t SET a=5;
+XA END     'new_trx_5';
+XA PREPARE 'new_trx_5';
+disconnect conn_restart_5;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_6';
+INSERT INTO t SET a=6;
+XA END     'new_trx_6';
+XA PREPARE 'new_trx_6';
+disconnect conn_restart_6;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_7';
+INSERT INTO t SET a=7;
+XA END     'new_trx_7';
+XA PREPARE 'new_trx_7';
+disconnect conn_restart_7;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_8';
+INSERT INTO t SET a=8;
+XA END     'new_trx_8';
+XA PREPARE 'new_trx_8';
+disconnect conn_restart_8;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_9';
+INSERT INTO t SET a=9;
+XA END     'new_trx_9';
+XA PREPARE 'new_trx_9';
+disconnect conn_restart_9;
+connection default;
+connection default;
+XA COMMIT  'new_trx_0';
+XA COMMIT  'new_trx_1';
+XA COMMIT  'new_trx_2';
+XA COMMIT  'new_trx_3';
+XA COMMIT  'new_trx_4';
+XA COMMIT  'new_trx_5';
+XA COMMIT  'new_trx_6';
+XA COMMIT  'new_trx_7';
+XA COMMIT  'new_trx_8';
+XA COMMIT  'new_trx_9';
+XA START 'trx_10';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_10';
+XA START 'trx_11';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_11';
+XA START 'trx_12';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_12';
+XA START 'trx_13';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_13';
+XA START 'trx_14';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_14';
+XA START 'trx_15';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_15';
+XA START 'trx_16';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_16';
+XA START 'trx_17';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_17';
+XA START 'trx_18';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_18';
+XA START 'trx_19';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_19';
+SELECT * FROM t;
+a
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+disconnect conn2tmp;
+disconnect conn3tmp;
+disconnect conn2ro;
+disconnect conn3ro;
+disconnect conn2empty;
+disconnect conn3empty;
+connection default;
+XA ROLLBACK 'trx_20';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn19;
+connection default;
+XA ROLLBACK 'trx_19';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn18;
+connection default;
+XA ROLLBACK 'trx_18';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn17;
+connection default;
+XA ROLLBACK 'trx_17';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn16;
+connection default;
+XA ROLLBACK 'trx_16';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn15;
+connection default;
+XA ROLLBACK 'trx_15';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn14;
+connection default;
+XA ROLLBACK 'trx_14';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn13;
+connection default;
+XA ROLLBACK 'trx_13';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn12;
+connection default;
+XA ROLLBACK 'trx_12';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn11;
+connection default;
+XA ROLLBACK 'trx_11';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn10;
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@sql_log_bin = OFF;
+CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb;
+XA START   'trx1tmp';
+INSERT INTO tmp1 SET a=1;
+XA END     'trx1tmp';
+XA PREPARE 'trx1tmp';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@sql_log_bin = OFF;
+CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb;
+XA START   'trx2tmp';
+INSERT INTO tmp1 SET a=1;
+XA END     'trx2tmp';
+XA PREPARE 'trx2tmp';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@sql_log_bin = OFF;
+CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb;
+XA START   'trx3tmp';
+INSERT INTO tmp1 SET a=1;
+XA END     'trx3tmp';
+XA PREPARE 'trx3tmp';
+connection default;
+XA COMMIT 'trx1tmp';
+ERROR XAE04: XAER_NOTA: Unknown XID
+XA ROLLBACK 'trx1tmp';
+ERROR XAE04: XAER_NOTA: Unknown XID
+XA START 'trx1tmp';
+ERROR XAE08: XAER_DUPID: The XID already exists
+connection default;
+*** 3 prepared transactions must be in the list ***
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+connection conn1tmp;
+disconnect conn1tmp;
+connection default;
+XA COMMIT 'trx1tmp';
+KILL connection CONN_ID;
+XA COMMIT 'trx3tmp';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx1ro';
+SELECT * from t ORDER BY a;
+a
+0
+1
+2
+3
+4
+5
+5
+6
+6
+7
+7
+8
+8
+9
+9
+10
+11
+12
+13
+14
+XA END     'trx1ro';
+XA PREPARE 'trx1ro';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx2ro';
+SELECT * from t ORDER BY a;
+a
+0
+1
+2
+3
+4
+5
+5
+6
+6
+7
+7
+8
+8
+9
+9
+10
+11
+12
+13
+14
+XA END     'trx2ro';
+XA PREPARE 'trx2ro';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx3ro';
+SELECT * from t ORDER BY a;
+a
+0
+1
+2
+3
+4
+5
+5
+6
+6
+7
+7
+8
+8
+9
+9
+10
+11
+12
+13
+14
+XA END     'trx3ro';
+XA PREPARE 'trx3ro';
+connection default;
+*** 4 prepared transactions must be in the list ***
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+connection conn1ro;
+disconnect conn1ro;
+connection default;
+XA ROLLBACK 'trx1ro';
+KILL connection CONN_ID;
+XA ROLLBACK 'trx3ro';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx1empty';
+XA END     'trx1empty';
+XA PREPARE 'trx1empty';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx2empty';
+XA END     'trx2empty';
+XA PREPARE 'trx2empty';
+connect  conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx3empty';
+XA END     'trx3empty';
+XA PREPARE 'trx3empty';
+connection default;
+*** 5 prepared transactions must be in the list ***
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+1	LEN1	LEN2	TRX_N
+connection conn1empty;
+disconnect conn1empty;
+connection default;
+XA COMMIT 'trx1empty';
+KILL connection CONN_ID;
+XA COMMIT 'trx3empty';
+connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'trx1unprepared';
+INSERT INTO t set a=0;
+XA END     'trx1unprepared';
+INSERT INTO t set a=0;
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
+XA START 'trx1unprepared';
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
+XA START 'trx1unprepared';
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
+disconnect conn1unprepared;
+connection default;
+XA COMMIT 'trx1unprepared';
+ERROR XAE04: XAER_NOTA: Unknown XID
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_0';
+INSERT INTO t SET a=0;
+XA END     'trx_0';
+XA PREPARE 'trx_0';
+disconnect conn0;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_1';
+INSERT INTO t SET a=1;
+XA END     'trx_1';
+XA PREPARE 'trx_1';
+disconnect conn1;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_2';
+INSERT INTO t SET a=2;
+XA END     'trx_2';
+XA PREPARE 'trx_2';
+disconnect conn2;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_3';
+INSERT INTO t SET a=3;
+XA END     'trx_3';
+XA PREPARE 'trx_3';
+disconnect conn3;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_4';
+INSERT INTO t SET a=4;
+XA END     'trx_4';
+XA PREPARE 'trx_4';
+disconnect conn4;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_5';
+INSERT INTO t SET a=5;
+XA END     'trx_5';
+XA PREPARE 'trx_5';
+disconnect conn5;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_6';
+INSERT INTO t SET a=6;
+XA END     'trx_6';
+XA PREPARE 'trx_6';
+disconnect conn6;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_7';
+INSERT INTO t SET a=7;
+XA END     'trx_7';
+XA PREPARE 'trx_7';
+disconnect conn7;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_8';
+INSERT INTO t SET a=8;
+XA END     'trx_8';
+XA PREPARE 'trx_8';
+disconnect conn8;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_9';
+INSERT INTO t SET a=9;
+XA END     'trx_9';
+XA PREPARE 'trx_9';
+disconnect conn9;
+connection default;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_10';
+INSERT INTO t SET a=10;
+XA END     'trx_10';
+XA PREPARE 'trx_10';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_11';
+INSERT INTO t SET a=11;
+XA END     'trx_11';
+XA PREPARE 'trx_11';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_12';
+INSERT INTO t SET a=12;
+XA END     'trx_12';
+XA PREPARE 'trx_12';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_13';
+INSERT INTO t SET a=13;
+XA END     'trx_13';
+XA PREPARE 'trx_13';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_14';
+INSERT INTO t SET a=14;
+XA END     'trx_14';
+XA PREPARE 'trx_14';
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_15';
+INSERT INTO t SET a=15;
+XA END     'trx_15';
+XA PREPARE 'trx_15';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_16';
+INSERT INTO t SET a=16;
+XA END     'trx_16';
+XA PREPARE 'trx_16';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_17';
+INSERT INTO t SET a=17;
+XA END     'trx_17';
+XA PREPARE 'trx_17';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+XA START   'trx_18';
+INSERT INTO t SET a=18;
+XA END     'trx_18';
+XA PREPARE 'trx_18';
+connection default;
+KILL CONNECTION CONN_ID;
+connect  conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+SET @@binlog_format = STATEMENT;
+SET @@binlog_format = ROW;
+XA START   'trx_19';
+INSERT INTO t SET a=19;
+XA END     'trx_19';
+XA PREPARE 'trx_19';
+connection default;
+KILL CONNECTION CONN_ID;
+connection default;
+XA ROLLBACK 'trx_0';
+XA ROLLBACK 'trx_1';
+XA ROLLBACK 'trx_2';
+XA ROLLBACK 'trx_3';
+XA ROLLBACK 'trx_4';
+XA COMMIT 'trx_5';
+XA COMMIT 'trx_6';
+XA COMMIT 'trx_7';
+XA COMMIT 'trx_8';
+XA COMMIT 'trx_9';
+# Kill and restart
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_0';
+INSERT INTO t SET a=0;
+XA END     'new_trx_0';
+XA PREPARE 'new_trx_0';
+disconnect conn_restart_0;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_1';
+INSERT INTO t SET a=1;
+XA END     'new_trx_1';
+XA PREPARE 'new_trx_1';
+disconnect conn_restart_1;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_2';
+INSERT INTO t SET a=2;
+XA END     'new_trx_2';
+XA PREPARE 'new_trx_2';
+disconnect conn_restart_2;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_3';
+INSERT INTO t SET a=3;
+XA END     'new_trx_3';
+XA PREPARE 'new_trx_3';
+disconnect conn_restart_3;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_4';
+INSERT INTO t SET a=4;
+XA END     'new_trx_4';
+XA PREPARE 'new_trx_4';
+disconnect conn_restart_4;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_5';
+INSERT INTO t SET a=5;
+XA END     'new_trx_5';
+XA PREPARE 'new_trx_5';
+disconnect conn_restart_5;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_6';
+INSERT INTO t SET a=6;
+XA END     'new_trx_6';
+XA PREPARE 'new_trx_6';
+disconnect conn_restart_6;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_7';
+INSERT INTO t SET a=7;
+XA END     'new_trx_7';
+XA PREPARE 'new_trx_7';
+disconnect conn_restart_7;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_8';
+INSERT INTO t SET a=8;
+XA END     'new_trx_8';
+XA PREPARE 'new_trx_8';
+disconnect conn_restart_8;
+connection default;
+connect  conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,;
+XA START   'new_trx_9';
+INSERT INTO t SET a=9;
+XA END     'new_trx_9';
+XA PREPARE 'new_trx_9';
+disconnect conn_restart_9;
+connection default;
+connection default;
+XA COMMIT  'new_trx_0';
+XA COMMIT  'new_trx_1';
+XA COMMIT  'new_trx_2';
+XA COMMIT  'new_trx_3';
+XA COMMIT  'new_trx_4';
+XA COMMIT  'new_trx_5';
+XA COMMIT  'new_trx_6';
+XA COMMIT  'new_trx_7';
+XA COMMIT  'new_trx_8';
+XA COMMIT  'new_trx_9';
+XA START 'trx_10';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_10';
+XA START 'trx_11';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_11';
+XA START 'trx_12';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_12';
+XA START 'trx_13';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_13';
+XA START 'trx_14';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA COMMIT 'trx_14';
+XA START 'trx_15';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_15';
+XA START 'trx_16';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_16';
+XA START 'trx_17';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_17';
+XA START 'trx_18';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_18';
+XA START 'trx_19';
+ERROR XAE08: XAER_DUPID: The XID already exists
+XA ROLLBACK 'trx_19';
+SELECT * FROM t;
+a
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+disconnect conn2tmp;
+disconnect conn3tmp;
+disconnect conn2ro;
+disconnect conn3ro;
+disconnect conn2empty;
+disconnect conn3empty;
+connection default;
+XA ROLLBACK 'trx_20';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn19;
+connection default;
+XA ROLLBACK 'trx_19';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn18;
+connection default;
+XA ROLLBACK 'trx_18';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn17;
+connection default;
+XA ROLLBACK 'trx_17';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn16;
+connection default;
+XA ROLLBACK 'trx_16';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn15;
+connection default;
+XA ROLLBACK 'trx_15';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn14;
+connection default;
+XA ROLLBACK 'trx_14';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn13;
+connection default;
+XA ROLLBACK 'trx_13';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn12;
+connection default;
+XA ROLLBACK 'trx_12';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn11;
+connection default;
+XA ROLLBACK 'trx_11';
+ERROR XAE04: XAER_NOTA: Unknown XID
+disconnect conn10;
+connection default;
+XA START   'one_phase_trx_0';
+INSERT INTO t SET a=0;
+XA END     'one_phase_trx_0';
+XA COMMIT  'one_phase_trx_0' ONE PHASE;
+XA START   'one_phase_trx_1';
+INSERT INTO t SET a=1;
+XA END     'one_phase_trx_1';
+XA COMMIT  'one_phase_trx_1' ONE PHASE;
+XA START   'one_phase_trx_2';
+INSERT INTO t SET a=2;
+XA END     'one_phase_trx_2';
+XA COMMIT  'one_phase_trx_2' ONE PHASE;
+XA START   'one_phase_trx_3';
+INSERT INTO t SET a=3;
+XA END     'one_phase_trx_3';
+XA COMMIT  'one_phase_trx_3' ONE PHASE;
+XA START   'one_phase_trx_4';
+INSERT INTO t SET a=4;
+XA END     'one_phase_trx_4';
+XA COMMIT  'one_phase_trx_4' ONE PHASE;
+SELECT SUM(a) FROM t;
+SUM(a)
+290
+DROP TABLE t;
+DROP VIEW v_processlist;
+All transactions must be completed, to empty-list the following:
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
diff --git a/mysql-test/main/xa_prepared_binlog_off.test b/mysql-test/main/xa_prepared_binlog_off.test
new file mode 100644
index 00000000000..edbfa7c2825
--- /dev/null
+++ b/mysql-test/main/xa_prepared_binlog_off.test
@@ -0,0 +1,11 @@
+###############################################################################
+# MDEV-7974 (bug#12161 Xa recovery and client disconnection)
+# Testing XA behaviour with binlog turned off.
+###############################################################################
+
+--source include/not_valgrind.inc
+--source include/not_embedded.inc
+
+# Common part with XA binlogging testing
+call mtr.add_suppression("You need to use --log-bin to make --log-slave-updates work.");
+--source suite/binlog/t/binlog_xa_prepared.inc
diff --git a/mysql-test/main/xa_sync.result b/mysql-test/main/xa_sync.result
index 1482ff5cacf..e7dd9b02847 100644
--- a/mysql-test/main/xa_sync.result
+++ b/mysql-test/main/xa_sync.result
@@ -18,6 +18,11 @@ disconnect con1;
 SET debug_sync='now SIGNAL go';
 connection con2;
 ERROR XAE04: XAER_NOTA: Unknown XID
+*** Must have 'xatest' in the list
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	6	0	xatest
+XA COMMIT 'xatest';
 disconnect con2;
 connection default;
 SET debug_sync='RESET';
@@ -37,6 +42,11 @@ disconnect con1;
 SET debug_sync='now SIGNAL go';
 connection con2;
 ERROR XAE04: XAER_NOTA: Unknown XID
+*** Must have 'xatest' in the list
+XA RECOVER;
+formatID	gtrid_length	bqual_length	data
+1	6	0	xatest
+XA ROLLBACK 'xatest';
 disconnect con2;
 connection default;
 SET debug_sync='RESET';
diff --git a/mysql-test/main/xa_sync.test b/mysql-test/main/xa_sync.test
index bb95af7c0ba..ad1243ce6f8 100644
--- a/mysql-test/main/xa_sync.test
+++ b/mysql-test/main/xa_sync.test
@@ -35,6 +35,11 @@ while ($i)
   connection con2;
   --error ER_XAER_NOTA
   reap;
+  --echo *** Must have 'xatest' in the list
+  XA RECOVER;
+  # second time yields no error
+  --error 0,1402
+  --eval $op
   disconnect con2;
 
   connection default;