MySQL Bugs: #104043: skip error DDL GTID is not continuous and restart encounter error

Bug #104043	skip error DDL GTID is not continuous and restart encounter error
Submitted:	17 Jun 2021 4:17	Modified:	26 Jun 2021 1:12
Reporter:	peng gao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	mysql8.0.23 mysql5.7.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
HI ALL：
  when set use gtid auto position and set slave-skip-errors=all，
  when slave encounter error ，DDL is not change GTID，but DML change the GTID
  
  like:
  Retrieved_Gtid_Set : f8cb75ee-7d5d-11eb-8db9-000c29b1d522:1-4
  Executed_Gtid_Set  : f8cb75ee-7d5d-11eb-8db9-000c29b1d522:1:3-4
        Auto_Position: 1
        
  but execute master binlog postion is change。
  we set restart slave this time， set relay_log_recovery=1 and slave-skip-errors='' not skip any error,slave will ignore Retrieved_Gtid_Set,and get gno:2 GTID from master and apply error DDL again like:
  
               Last_SQL_Error: Error 'Can't create database 'test0617'; database exists' on query. Default database: 'test0617'. Query: 'create database test0617'
           Retrieved_Gtid_Set: f8cb75ee-7d5d-11eb-8db9-000c29b1d522:2
            Executed_Gtid_Set: f8cb75ee-7d5d-11eb-8db9-000c29b1d522:1:3-4
                Auto_Position: 1
  
when slave  encounter DDL error then query event apply not binlog_gtid_end_transaction is not call execute gtid is not change so gap appear.but when  encounter DML error xid event also change the execute gtid,so gtid set is continuous.
  

  
  
  
   

How to repeat:
8.0.23
slave parameter:
relay_log_recovery = on
gtid_mode = on
enforce-gtid-consistency = on
slave-skip-errors=all

auto position:
MASTER_AUTO_POSITION = 1

1、
master:
create database test06171;
2、
slave:
set sql_log_bin=0;
create database test06172;
3、
master:
create database test06172;
4、
then create any transaction on master
5、
slave:
remove parameter
slave-skip-errors=all
and restart,error find

how to resolve this issue?
thanks!

Hi,

I'm not sure why do you think this is a bug? You created DB on slave, then created DB on master that system tried to replicate on slave and failed as DB already exist. What was your expected behavior here?

thanks！
but why master insert a slave exsit row ，error Duplicate entry skip。
this scenario gtid is is continuous。

i mean when slave skip error,DDL and DML handle master gtid is different。

DDL LIKE database or table is exsit，skip error，slave skip this GTID。
DML LIKE row is exsit，skip error，slave not skip this GTID，this GTID also present to slave execute gtid set。

  master                   slave
 
  exe gtid set:1-3       exe gtid set:1-3
  
  DDL create database      database exsit
                           skip error
  gtid:4                   gtid:4(skip)
  
  DML INSERT ROW           ROW EXSIT(Duplicate entry)
  
  gtid:5                   gtid:5(also in slave exe gtid,not skip)

Hi, you are correct. This is a bug. I discussed with the Replication team and this is a bug at least at the consistency level since there is in fact an incoherent behavior w.r.t GTID_EXECUTE update after a DDL or DML when skip-slave-errors = all.

test case:

--source include/have_binlog_format_row.inc
--source include/master-slave.inc
--let $assert_text = Skipping errors
--let $assert_cond = "[SELECT @@GLOBAL.slave_skip_errors]" = "all"
--source include/assert.inc
--source include/rpl_connection_slave.inc
SET SESSION sql_log_bin = 0;
create table t1 (a int primary key, b int);
insert into t1 values (1,1);
SET SESSION sql_log_bin = 1;
--source include/rpl_connection_master.inc
create table t2 (a int primary key, b int);
--let $gtid_executed = `SELECT @@GLOBAL.gtid_executed`
create table t1 (a int primary key, b int);
SHOW BINLOG EVENTS;
--sleep 5
--source include/rpl_connection_slave.inc
SELECT @@GLOBAL.gtid_executed;
--source include/rpl_connection_master.inc
insert into t1 values (1,1);
SHOW BINLOG EVENTS;
--sleep 5
--source include/rpl_connection_slave.inc
SELECT @@GLOBAL.gtid_executed;
--let $assert_text = GTID_EXECUTED should have only one GTID
--let $assert_cond = "[SELECT @@GLOBAL.gtid_executed]" = "$gtid_executed"
--source include/assert.inc

OK，thanks for your verification.