Bug #109485 Contribution by Tencent: Previous_gtids miss gtid when binlog_order_commits off

• Submitted: 27 Dec 2022 0:34
• Modified: 25 Jan 2023 13:52
• Reporter: yewei Xu (OCA)
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.7.40
• OS: Any
• CPU Architecture: Any

[27 Dec 2022 0:34] yewei Xu

Description:
binlog rotate will wait for m_prep_xids decrease to zero, but m_prep_xids is decrease before update thd's gtid to executed_gtids when binlog_order_commits off(see function MYSQL_BIN_LOG::finish_commit), so gtid may missed in next binlog file's Previous_gtids event if binlog_order_commits off.

How to repeat:
diff --git a/sql/binlog.cc b/sql/binlog.cc
index eeb32e1cc53..e048b9258c1 100644
--- a/sql/binlog.cc
+++ b/sql/binlog.cc
@@ -9334,6 +9334,8 @@ MYSQL_BIN_LOG::finish_commit(THD *thd)
   else if (thd->get_transaction()->m_flags.xid_written)
     dec_prep_xids(thd);
 
+  DEBUG_SYNC(thd, "after_dec_prep_xids");
+
   /*
     If the ordered commit didn't updated the GTIDs for this thd yet
     at process_commit_stage_queue (i.e. --binlog-order-commits=0)

use upper patch and run follow testcase with command: ./mtr previous_gtids --mysqld=--log-bin=master-bin --mysqld=--gtid_mode=on --mysqld=--enforce_gtid_consistency=on

Previous_gtids will miss gtid 2.

--source include/have_debug.inc
--source include/have_innodb.inc
--source include/have_debug.inc
--source include/have_log_bin.inc

connection default;

reset master;

SET @old_binlog_order_commits = @@global.binlog_order_commits;
SET GLOBAL binlog_order_commits = 0;

CREATE TABLE t1 (id int primary key auto_increment);

connect(con1,localhost,root,,);
SET DEBUG_SYNC='after_dec_prep_xids WAIT_FOR wakeup_signal';
send insert into t1 values ();

connection default;
SET SESSION debug="+d,force_rotate";
insert into t1 values ();
SET DEBUG_SYNC='now signal wakeup_signal';
insert into t1 values ();
show binlog events in 'master-bin.000002';

drop table t1;
SET GLOBAL binlog_order_commits = @old_binlog_order_commits;

Suggested fix:
call dec_prep_xids after gtid_state->update_on_commit in MYSQL_BIN_LOG::finish_commit

[27 Dec 2022 4:57] MySQL Verification Team

Hello  Yewei Xu,

Thank you for the report and contribution.

regards,
Umesh

[25 Jan 2023 13:52] Jon Stephens

Documented fix as follows in the MySQL 5.7.42 and 8.0.33 changelogs:

    Setting binlog_order_commits to OFF could lead to a missed GTID
    in the next binary log file's Previous_gtids event.

    Our thanks to Yewei Xu and the Tencent team for the
    contribution.

Closed.