| Bug #88534 | XA may lost prepared transaction and cause different between master and slave. | ||
|---|---|---|---|
| Submitted: | 17 Nov 2017 11:04 | Modified: | 23 Mar 2021 8:17 |
| Reporter: | Ze Yang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: XA transactions | Severity: | S1 (Critical) |
| Version: | 5.7, 5.7.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Nov 2017 9:48]
MySQL Verification Team
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]
MySQL Verification Team
test results
Attachment: 88534.results (application/octet-stream, text), 9.01 KiB.
[23 Feb 2018 3:51]
Ze 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.
[23 Mar 2021 8:17]
Ze Yang
My solution, may be helpful for others: The execution order: 'XA PREPARE':engine->prepare, binlog->prepare. 'XA COMMIT one phase': engine->prepare, binlog->commit, engine->commit. 'XA COMMIT': binlog->commit, engine->commit (ensure engine commit before binary log rotate). 'XA ROLLBACK': binlog->rollback, engine->rollback (ensure engine rollback before binary log rotate). Add two types of event for external XA recovery. Previous_prepared_xids_log_event: Containing the xids of all external XA transactions that are in XA-PREPARED state when rotate binary log. Write to the binary log after Previous_gtids_log_event. This event is for getting the state of external XA without traverse all the binary logs. XA_preparing_log_event: Containing the xid of external XA transaction that execute 'XA PREPARE' before engine->prepare. This event is for recognizing the state of external XA which have the same xid with some one that just execute 'XA COMMIT' successfully.
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));