MySQL 8.0.20 - Master Replica scheme, errors during replication process
0
votes
1
answer
225
views
This threads follows a previous one, given at this URL: https://dba.stackexchange.com/questions/285442/mysql-8-0-20-master-replica-scheme-increasing-delay-between-source-and-replic
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 :
--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*/;
Asked by user1458792
(13 rep)
Feb 17, 2021, 04:07 PM
Last activity: Jun 9, 2025, 07:11 PM
Last activity: Jun 9, 2025, 07:11 PM