0

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*/;
4
  • can you do your mysqlbinlog command with added --verbose which is needed to decode rows. The replication error is the row that is being updated doesn't exist on the slave. With the verbose information you will be able to find it on the master. binlog_format is dynamic and session based, but you shouldn't need to do that. – danblack Feb 18 at 1:19
  • Thanks for your reply, I directly updated the post above with details you asked. Now we can see information about an UPDATE query. It seems fields 8 & 9 are updated. We can now try to understand why this record has not been replicated. – user1458792 Feb 18 at 6:39
  • About the binlog_format, the document also says "If any replication channel applier thread is currently running, the replication format cannot be changed globally", it is not clear if we can or not change it during a replication crash. Any advice? – user1458792 Feb 18 at 6:46
  • Forget binlog_format for now, focus on looking at the record on master and replica – danblack Feb 18 at 9:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.