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

[15 Mar 23:57] Abdel-Mawla Gharieb
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.
[16 Mar 0:07] Abdel-Mawla Gharieb
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!
[25 Mar 12:18] MySQL Verification Team
Hello Abdel-Mawla Gharieb.

Thank you for the report and feedback.

[25 Mar 12:20] MySQL Verification Team
MySQL Server 5.7.33 test results

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