Bug #99082 Problem with replication: XA transaction, temp tables and row based binlog
Submitted: 26 Mar 2020 11:26 Modified: 27 Mar 2020 6:44
Reporter: Libor Laichmann Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.7.27, 5.7.29 OS:Any
Assigned to: CPU Architecture:Any

[26 Mar 2020 11:26] Libor Laichmann 
Description:
The DB replication has been broken when you using XA transaction (used for enterprise solution typically), temporary tables (in some huge procedures) and row based bin log format.

How to repeat:
-- on the master
XA START 'xatest1';
  
drop temporary table if exists gm_midterm.test_temp_table;
create temporary table gm_midterm.test_temp_table ENGINE = MEMORY as select "a" from dual;
select * from gm_midterm.test_temp_table;
drop temporary table if exists gm_midterm.test_temp_table;

XA END 'xatest1';
XA PREPARE 'xatest1';
XA COMMIT 'xatest1';

-- on the slave after that
Last_SQL_Errno: 3227
               Last_SQL_Error: Error in Xid_log_event: Commit could not be completed, 'The use of replication filters with XA transactions is not supported, and can lead to an undefined state in the replication slave.'
[27 Mar 2020 6:44] MySQL Verification Team 
Hello Libor Laichmann,

Thank you for the report.
Verified as described with 5.7.29 build.

Thanks,
Umesh
[27 Mar 2020 6:45] MySQL Verification Team 
Test results - 5.7.29

Attachment: 99082_5.7.29.results (application/octet-stream, text), 10.90 KiB.
[27 Mar 2020 6:46] MySQL Verification Team 
I'll add 5.6 and 8.0 results later on
[26 Mar 2021 2:31] zhanshui yu 
Coud u help verified Version5.7.32 or later 5.7 series?
[8 Jan 15:08] Jaromír Scherbaum 
Tested on 5.7.42 - error persists.