Bug #110533 XA COMMIT may not be recovered after a crash

• Submitted: 28 Mar 2023 15:49
• Modified: 29 Mar 2023 6:39
• Reporter: Libing Song (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[28 Mar 2023 15:49] Libing Song

Description:
For  XA COMMIT, it is binlogged before engine xa commit. Binlog rotation can happen between XA COMMIT binlogging and engine commit. In this case, XA COMMIT is not in the last binlog file, so it can not be recovered correctly.

XA ROLLBACK has same problem.

How to repeat:
--source include/have_debug.inc
--source include/have_debug_sync.inc
--source include/have_binlog_format_row.inc

--connect(con1, localhost, root,,)
--connect(con2, localhost, root,,)

--echo [ connection default ]
--connection default
RESET MASTER;
CREATE TABLE t1(c1 INT);
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)

XA START "xid1";
INSERT INTO t1 VALUES(1);
XA END "xid1";
XA PREPARE "xid1";
SET debug_sync = "RESET";
SET debug_sync ="bgc_after_enrolling_for_commit_stage SIGNAL flushed WAIT_FOR continue";
--send XA COMMIT "xid1"

--echo [ connection con1 ]
--connection con1
SET debug_sync = "now WAIT_FOR flushed";
--source include/show_binlog_events.inc
# XA COMMIT is binlogged but not committed to engine.
XA RECOVER;
FLUSH BINARY LOGS;
XA RECOVER;
--source include/dbug_crash_safe.inc
--source include/start_mysqld.inc
# After crash recovery, it should be committed.
# But it is in prepared state.
XA RECOVER;
select * from t1;

Suggested fix:
FLUSH BINARY LOGS should be blocked until the XA transaction is committed or rolled back to engine.

[29 Mar 2023 6:39] MySQL Verification Team

Hello Libing,

Thank you for the report and feedback.

regards,
Umesh

[29 Mar 2023 12:17] Libing Song

Fix for the bug.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug110533_patch2.diff (application/octet-stream, text), 15.35 KiB.