MySQL Bugs: #102992: XA transactions do not update slave_relay_log

Bug #102992	XA transactions do not update slave_relay_log_info table
Submitted:	15 Mar 2021 23:57	Modified:	25 Mar 2021 12:18
Reporter:	Abdel-Mawla Gharieb	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7, 8.0, 5.7.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using crash safe replication settings (particularly, relay_log_info_repository='TABLE'), the replica does not update the table mysql.slave_relay_log_info when XA transaction is executed on the source DB even though, the transaction itself was replicated. If the replica crashed before either the source's binary log got rotated or non-XA transaction was executed (as these will trigger an update on the table), the replication will break after the DB start up as the used binary log coordinates will point to transactions that were already applied.

The test is reproducible on 5.7.31 and the replication breaks after the crash. However, MySQL 8.0.21 shows an interesting behavior. The table mysql.slave_relay_log_info still doesn't get updated after XA transactions and replication used the incorrect/not-updated binary log coordinates after a crash (as it appear from the error log). However, somehow the replication didn't break and it corrected itself!

How to repeat:
1- Create source and replica DBs.
2- Run the following statements on the source:
a) create database xarepl;
b) use xarepl
c) create table t1(id int primary key auto_increment)engine=innodb;
d) -- Run the following statement several times ...
XA START 'xa_replication_check'; insert into t1(id) values(null); XA END 'xa_replication_check'; XA COMMIT 'xa_replication_check' ONE PHASE;
e) -- Check the table records
select COUNT(*) from t1;
3- Run the following statements on the replica:
a) -- Check the table records
select COUNT(*) from t1;
b) show slave status\G select Master_log_name,Master_log_pos from mysql.slave_relay_log_info;

This will show that the Relay_Master_Log_File and Exec_Master_Log_Pos in SHOW SLAVE STATUS output do not match the info in mysql.slave_relay_log_info table!

4- Crash the replica (pkill -9 mysql).

Suggested fix:
Like the normal transactions, the table "mysql.slave_relay_log_info" should get updated in each XA COMMIT.

The table "mysql.slave_master_info" does not get updated after XA transactions as well (if "master_info_repository=TABLE"). This could lead to copying non-necessary binary log files from the source to the replica after a crash. However, I don't think that is as critical as "mysql.slave_relay_log_info" which breaks the replication!

Hello Abdel-Mawla Gharieb.

Thank you for the report and feedback.

regards,
Umesh

MySQL Server 5.7.33 test results

Attachment: 102992_5.7.33.results.txt (text/plain), 19.46 KiB.