Bug #106875 Contribution by Tencent: writeset can not be used when binlog_format is mixed

• Submitted: 30 Mar 2022 14:43
• Modified: 5 Apr 2022 14:12
• Reporter: Yin Peng (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0.28
• OS: Any
• CPU Architecture: Any

[30 Mar 2022 14:43] Yin Peng

Description:
When writeset is used, changing binlog_format may cause inconsistency between master and slave.

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc
connection master;

set global transaction_write_set_extraction=xxhash64;
set transaction_write_set_extraction=xxhash64;
set global binlog_transaction_dependency_tracking=writeset;

set binlog_format=mixed;

connection master;

create table t1(id int auto_increment primary key, c1 int);
create table t2(c1 int);
create table t3(c1 int);
insert into t2 values(1);

connection master;
--source include/sync_slave_sql_with_master.inc

connection slave;
stop slave;

# disable slave_preserve_commit_order to avoid deadlock
set global slave_preserve_commit_order=OFF;
set global slave_parallel_type=logical_clock;
set global slave_parallel_workers=2;

connection master;

# trx1
begin;
update t2 set c1=2 where c1=1;
commit;

# trx2
begin;
insert into t3 values(1);
update t2 set c1=3 where c1=2;
insert into t1(c1) select * from t2;
commit;

# trx3
begin;
update t2 set c1=9 where c1=3;
insert into t1(c1) select * from t2;
commit;

connection slave;
# lock t3 to block trx2
lock table t3 read;
start slave;
connection master;
sleep 2;
connection slave;
unlock tables;

connection master;
--source include/sync_slave_sql_with_master.inc
connection master;
select * from t2;
checksum table t2;
connection slave;
select * from t2;
checksum table t2;

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
--source include/rpl_end.inc

============================

Run this testcase and you will see table t2 on slave is inconsistent with table t2 on master. 

Suggested fix:
Prohibit the modification of binlog_format when writeset is in use.

[30 Mar 2022 14:44] Yin Peng

Prohibit the modification of binlog_format when writeset is in use.

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

Contribution: patch.txt (text/plain), 2.43 KiB.

[31 Mar 2022 11:20] Yin Peng

Last patch is not suitable for this scenario: A session's binlog_format has been set to MIXED before enabling writeset. I find a simpler way to solve this problem: we just need to prohibit the modification of binlog_format when transaction_write_set_extraction is not OFF. If someone wants to change binlog_format, he needs to set transaction_write_set_extraction to OFF first.

[1 Apr 2022 8:02] Yin Peng

Is there anyone replies to me? I find that prohibiting the modification of binlog_format when writeset is in use may cause many mtr failures. There is another way to solve this problem: Check the transaction's binlog_format when determining if a transaction can use writesets. A transaction can not use writesets if it's binlog_format is not row.

Corresponding fix:

diff --git a/sql/rpl_trx_tracking.cc b/sql/rpl_trx_tracking.cc
index 3f4ea4d21e8..a42468e74e2 100644
--- a/sql/rpl_trx_tracking.cc
+++ b/sql/rpl_trx_tracking.cc
@@ -239,7 +239,9 @@ void Writeset_trx_dependency_tracker::get_dependency(THD *thd,
       // must not use foreign keys
       !write_set_ctx->get_has_related_foreign_keys() &&
       // it did not broke past the capacity already
-      !write_set_ctx->was_write_set_limit_reached();
+      !write_set_ctx->was_write_set_limit_reached() &&
+      // the binlog_format should be row
+      (thd->variables.binlog_format == BINLOG_FORMAT_ROW);
   bool exceeds_capacity = false;

[5 Apr 2022 14:12] MySQL Verification Team

Hi,

Thanks for the report, test case and fix