Re: MySQL Replication lag in slave due to Delete query - Row Based Replication

[Vinieth S S]

Sorry for the late reply, have checked few other cases which took time.

Acutal Query
'Delete from Table where Pk in (2000ids)' by 2k batch

If I set binlog format to MIXED, even though the query is safe to execute in Statement format, MYSQL will only use RBR.

Because please check the below decision table, which I took from binlog.cc file.


Decision table for logging format
------------------------------------

The following table summarizes how the format and generated
warning/error depends on the tables' capabilities, the statement
type, and the current binlog_format.

     Row capable        N NNNNNNNNN YYYYYYYYY YYYYYYYYY
     Statement capable  N YYYYYYYYY NNNNNNNNN YYYYYYYYY

     Statement type     * SSSUUUIII SSSUUUIII SSSUUUIII

     binlog_format      * SMRSMRSMR SMRSMRSMR SMRSMRSMR

     Logged format      - SS-S----- -RR-RR-RR SRRSRR-RR
     Warning/Error      1 --2732444 5--5--6-- ---7--6--


My Case: 
Row Capable:       Y
Statement Capable: Y
Statement type:    S
Binlog_fomrat:     M

Logged format:    R

As the Logged format is R, it won't be go for SBR in my case. So setting SBR is needed, whereas MIXED can't be used.

Can you please share the points, why SBR it won't reliable in my case?



Edited 1 time(s). Last edit at 09/17/2021 10:12AM by Peter Brawley.