Bug #88534 XA may lost prepared transaction and cause different between master and slave.
Submitted: 17 Nov 2017 11:04 Modified: 23 Feb 3:51
Reporter: Michael Yang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: XA transactions Severity:S1 (Critical)
Version:5.7, 5.7.20 OS:Any
Assigned to: CPU Architecture:Any

[17 Nov 2017 11:04] Michael Yang
Description:
1 ha_prepare function first prepare the binlog, then the engines.
If there's error or mysqld crashed when the engine prepare, the engine prepared info w
ould lost. But the slave would receive the XA binlog.

2. As ha_prepare function call binlog_prepare first, the thd->durability_property would be HA_IGNORE_DURABILITY. Then the engine's prepare would not flush logs. Event though the client receive success after issue 'XA prepare', the prepared transaction may also lost after server crash. And this would also cause slave replication not consistent.

How to repeat:
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1460,10 +1460,12 @@ int ha_prepare(THD *thd)
       thd->status_var.ha_prepare_count++;
       if (ht->prepare)
       {
-        DBUG_EXECUTE_IF("simulate_xa_failure_prepare", {
-          ha_rollback_trans(thd, true);
-          DBUG_RETURN(1);
-        });
+        if (ht->db_type == DB_TYPE_INNODB) {
+          DBUG_EXECUTE_IF("simulate_xa_failure_prepare", {
+            ha_rollback_trans(thd, true);
+            DBUG_RETURN(1);
+          });
+        }
         if (ht->prepare(ht, thd, true))
         {
           ha_rollback_trans(thd, true);

replication.test:

--disable_warnings
source include/master-slave.inc;
--enable_warnings
connection master;
CREATE TABLE ti (c1 INT) ENGINE=INNODB;
XA START 'x';
INSERT INTO ti VALUES(1);
XA END 'x';
SET @@session.debug = '+d,simulate_xa_failure_prepare';
--error ER_XA_RBROLLBACK
XA PREPARE 'x';
--echo #Master
XA RECOVER;

--sync_slave_with_master
connection slave;
--echo #Slave
XA RECOVER;

replication.result:

include/master-slave.inc
[connection master]
CREATE TABLE ti (c1 INT) ENGINE=INNODB;
XA START 'x';
INSERT INTO ti VALUES(1);
XA END 'x';
SET @@session.debug = '+d,simulate_xa_failure_prepare';
XA PREPARE 'x';
ERROR XA100: XA_RBROLLBACK: Transaction branch was rolled back
#Master
XA RECOVER;
formatID  gtrid_length  bqual_length  data
#Slave
XA RECOVER;
formatID  gtrid_length  bqual_length  data
1 1 0 x

--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1479,6 +1479,7 @@ int ha_prepare(THD *thd)
       }
       ha_info= ha_info->next();
     }
+    DBUG_EXECUTE_IF("crash_after_xa_prepare", DBUG_SUICIDE(););

     DBUG_ASSERT(thd->get_transaction()->xid_state()->
                 has_state(XID_STATE::XA_IDLE));

replication.test:

-- source include/have_log_bin.inc
CREATE TABLE ti (c1 INT) ENGINE=INNODB;
XA START 'x';
INSERT INTO ti VALUES(1);
XA END 'x';
SET @@session.debug = '+d,crash_after_xa_prepare';
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--error 2013
XA PREPARE 'x';
--source include/wait_until_disconnected.inc
--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--source include/start_mysqld.inc
XA RECOVER;
show binlog events in 'mysql.000001';

replication.result:
CREATE TABLE ti (c1 INT) ENGINE=INNODB;
XA START 'x';
INSERT INTO ti VALUES(1);
XA END 'x';
SET @@session.debug = '+d,crash_after_xa_prepare';
XA PREPARE 'x';
ERROR HY000: Lost connection to MySQL server during query
# restart
XA RECOVER;
formatID  gtrid_length  bqual_length  data
show binlog events in 'mysql.000001';
Log_name  Pos Event_type  Server_id End_log_pos Info
mysql.000001  4 Format_desc 1 123 Server ver: 5.7.19org-debug-log, Binlog ver: 4
mysql.000001  123 Previous_gtids  1 154
mysql.000001  154 Anonymous_Gtid  1 219 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql.000001  219 Query 1 331 use `test`; CREATE TABLE ti (c1 INT) ENGINE=INNODB
mysql.000001  331 Anonymous_Gtid  1 396 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql.000001  396 Query 1 483 XA START X'78',X'',1
mysql.000001  483 Table_map 1 528 table_id: 222 (test.ti)
mysql.000001  528 Write_rows  1 568 table_id: 222 flags: STMT_END_F
mysql.000001  568 Query 1 653 XA END X'78',X'',1
mysql.000001  653 XA_prepare  1 690 XA PREPARE X'78',X'',1

Suggested fix:
diff --git a/sql/handler.cc b/sql/handler.cc
index 37d38e2..94ff1c0 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1436,6 +1436,7 @@ int ha_prepare(THD *thd)
 {
   int error=0;
   Transaction_ctx *trn_ctx= thd->get_transaction();
+  handlerton *binlog_ht= NULL;
   DBUG_ENTER("ha_prepare");

   if (trn_ctx->is_active(Transaction_ctx::SESSION))
@@ -1464,7 +1465,10 @@ int ha_prepare(THD *thd)
           ha_rollback_trans(thd, true);
           DBUG_RETURN(1);
         });
-        if (ht->prepare(ht, thd, true))
+        if (ht->db_type == DB_TYPE_BINLOG)
+        {
+          binlog_ht= ht;
+        } else  if (ht->prepare(ht, thd, true))
         {
           ha_rollback_trans(thd, true);
           error=1;
@@ -1479,6 +1483,15 @@ int ha_prepare(THD *thd)
       }
       ha_info= ha_info->next();
     }
+    if (error == 0 && binlog_ht)
+    {
+      if (binlog_ht->prepare(binlog_ht, thd, true))
+      {
+        ha_rollback_trans(thd, true);
+        error=1;
+      }
+    }
+    DBUG_EXECUTE_IF("crash_after_xa_prepare", DBUG_SUICIDE(););

     DBUG_ASSERT(thd->get_transaction()->xid_state()->
                 has_state(XID_STATE::XA_IDLE));
[20 Nov 2017 9:48] Umesh Shastry
Hello Michael Yang,

Thank you for the report and test case.
Verified as described with 5.7.20 src build after patching.
Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). More details are described in "Contributions" tab, please ensure to re-send the patch via that tab. Otherwise we would not be able to accept it.

Thanks,
Umesh
[20 Nov 2017 9:58] Umesh Shastry
test results

Attachment: 88534.results (application/octet-stream, text), 9.01 KiB.

[23 Feb 3:51] Michael Yang
Last days I thought this bug again. If we call InnoDB prepare first, the binlog would lost, the master would have a prepared xa transaction but the slave not.

Then we found two methods to solve this problem. the second method may better .

1. Remain 'XA prepare': binlog prepare->engine prepare(ensure engine flush logs), 'XA COMMIT': binlog commit->engine commit. Write 'xa rollback' when we found the storage engine on master lost prepared transaction.

When the server restarted, we search the last binlog to check whether there's a 'xa prepare' which no 'xa commit/rollback' matched. If we find the not matched 'xa prepare x' in binlog, we check whether there's prepared xa 'x' in the storage engine.  If there's no prepared xa transacton, we write 'xa rollback x' to the binlog to cancel prepared transaction on slave.

But this method not friendly to HA. If we change to slave after the master crash, there's error happened.

2. 'XA PREPARE': engine prepare->binlog prepare, 'XA COMMIT' binlog commit-> engine commit. Add one table like the 'mysql.gtid_executed' table to record the xa prepared transactions when rotate binlog. Search the last binlog to decide whether the prepared transaction in storage engine should be rollbacked, committed or remained prepared.

If we don't add one table to recored the xa prepared transaction when rotate binlog, we would not know whether the prepared transaction's binlog was logged as  'xa prepare' may recorded in the purged binlog. So add a table to record xa prepared transaction when rotate binlog, then we could use binlog to decide the transaction state.