MySQL 8.0.20 - Master Replica scheme, errors during replication process

Question

This threads follows a previous one, given at this URL: MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica

Replica Server has been configured this way, which ensures a fast replication: innodb_flush_method = O_DIRECT SET GLOBAL sync_binlog = 0; SET GLOBAL innodb_flush_log_at_trx_commit = 2;

The Source binlogs are in ROW format for instance. As I understand, we cannot change to STATEMENT as it is already a Replication process running, and without restarting Source database, which is touchy. An I right?

However, the replication process sometimes falls in error, because of inexisting records: Last_SQL_Error: Could not execute Update_rows event on table levelup.videos; Can't find record in 'videos', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.001822, end_log_pos 328518491

We checked in source Binlog file, without finding anything to show us the incorrect record nor the source of the error. Any help to decrypt the logs would be very appreciated. Thanks in advance

Command executed on Master : mysqlbinlog --base64-output=decode-rows --start-position=328517915 --stop-position=328518679 binlog.001822 --verbose

Result :

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#210217  9:32:50 server id 1  end_log_pos 125 CRC32 0x74769bdd  Start: binlog v 4, server v 8.0.22-13 created 210217  9:32:50
# at 328517915
#210217  9:41:23 server id 1  end_log_pos 328517994 CRC32 0xa92a39bf    Anonymous_GTID  last_committed=628870   sequence_number=628871  rbr_only=yes    original_committed_timestamp=1613551283843458   immediate_commit_timestamp=1613551283843458     transaction_length=607
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1613551283843458 (2021-02-17 09:41:23.843458 CET)
# immediate_commit_timestamp=1613551283843458 (2021-02-17 09:41:23.843458 CET)
/*!80001 SET @@session.original_commit_timestamp=1613551283843458*//*!*/;
/*!80014 SET @@session.original_server_version=80022*//*!*/;
/*!80014 SET @@session.immediate_server_version=80022*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 328517994
#210217  9:41:23 server id 1  end_log_pos 328518081 CRC32 0x56149f5b    Query   thread_id=219241        exec_time=0     error_code=0
SET TIMESTAMP=1613551283/*!*/;
SET @@session.pseudo_thread_id=219241/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1174405120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 328518081
#210217  9:41:23 server id 1  end_log_pos 328518171 CRC32 0x40249bce    Table_map: `XXX`.`videos` mapped to number 227
# at 328518171
#210217  9:41:23 server id 1  end_log_pos 328518491 CRC32 0x8a73f2c2    Update_rows: table id 227 flags: STMT_END_F
### UPDATE `XXX`.`videos`
### WHERE
###   @1=229814401
###   @2=9
###   @3='6801549427720504325'
###   @4='6929519055464353030'
###   @5='2021:02:15'
###   @6='TAG these teammates 😭 #FamilyDay'
###   @7=35
###   @8=NULL
###   @9=NULL
###   @10=1300000
###   @11=8101
###   @12=306600
###   @13=NULL
###   @14=26100
###   @15=NULL
###   @16='0000:00:00'
### SET
###   @1=229814401
###   @2=9
###   @3='6801549427720504325'
###   @4='6929519055464353030'
###   @5='2021:02:15'
###   @6='TAG these teammates 😭 #FamilyDay'
###   @7=35
###   @8=''
###   @9=3
###   @10=1300000
###   @11=8101
###   @12=306600
###   @13=NULL
###   @14=26100
###   @15=NULL
###   @16='0000:00:00'
# at 328518491
#210217  9:41:23 server id 1  end_log_pos 328518522 CRC32 0x7b25222b    Xid = 3758209563
COMMIT/*!*/;
# at 328518522
#210217  9:41:23 server id 1  end_log_pos 328518601 CRC32 0x0342fc32    Anonymous_GTID  last_committed=628871   sequence_number=628872  rbr_only=yes    original_committed_timestamp=1613551283854427   immediate_commit_timestamp=1613551283854427     transaction_length=473
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1613551283854427 (2021-02-17 09:41:23.854427 CET)
# immediate_commit_timestamp=1613551283854427 (2021-02-17 09:41:23.854427 CET)
/*!80001 SET @@session.original_commit_timestamp=1613551283854427*//*!*/;
/*!80014 SET @@session.original_server_version=80022*//*!*/;
/*!80014 SET @@session.immediate_server_version=80022*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 328518601
#210217  9:41:23 server id 1  end_log_pos 328518679 CRC32 0xac04ba69    Query   thread_id=49970 exec_time=0     error_code=0
SET TIMESTAMP=1613551283/*!*/;
BEGIN
/*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Answer 1

Yes, finally found it. Actually, some SQL order programmatically disabled replication using following PHP order : $db->exec('SET sql_log_bin=OFF'). I don't know why it was there and it took us a lot of time to understand where it came from, but we finally did it. I wish nobody experience this, but if it happens, maybe this comment will help