MySQL Bugs: #117069: The slave recorded the master's binary log into the binary log without applying it to the data

Bug #117069	The slave recorded the master's binary log into the binary log without applying it to the data
Submitted:	30 Dec 2024 2:09	Modified:	16 Jan 6:12
Reporter:	SEUNGHO BAEK	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Red Hat (7.9)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
When the master's binary log was replicated to the slave, the update were found in the slave's binary log, but not applied to the data.
This is a batch operation that takes place at 2 a.m. every day.
The slave encountered a replication error when the master updated TRANS_DT column from yesterday's date to today's date.
I found out that the date of the slave TRANS_DT column was two days ago, not yesterday.

# Slave Replication Variables
mysql> show global variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

# Slave binary log (not relay log)
# at 271348888
#241129  2:15:22 server id 1  end_log_pos 271348974 CRC32 0x05eeda4a    Anonymous_GTID  last_committed=10889    sequence_number=10890   rbr_only=yes    original_committed_timestamp=1
732814122471291 immediate_commit_timestamp=1732814122517105     transaction_length=675
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1732814122471291 (2024-11-29 02:15:22.471291 KST)
# immediate_commit_timestamp=1732814122517105 (2024-11-29 02:15:22.517105 KST)
/*!80001 SET @@session.original_commit_timestamp=1732814122471291*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 271348974
#241129  2:15:22 server id 1  end_log_pos 271349044 CRC32 0xa2364f4a    Query   thread_id=354214        exec_time=0     error_code=0
SET TIMESTAMP=1732814122/*!*/;
SET @@session.pseudo_thread_id=354214/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168637984/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=46/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 271349044
#241129  2:15:22 server id 1  end_log_pos 271349162 CRC32 0x04f662c2    Table_map: `mdlc`.`t_sys_user_if` mapped to number 446
# has_generated_invisible_primary_key=0
# at 271349162
#241129  2:15:22 server id 1  end_log_pos 271349532 CRC32 0x7aa04d45    Update_rows: table id 446 flags: STMT_END_F

BINLOG '
KqVIZxMBAAAAdgAAAKp1LBAAAL4BAAAAAAEABG1kbGMADXRfc3lzX3VzZXJfaWYAFA8PDw8PDw8P
Dw8PDw8PDw8PCg8PJkAGQAZABkAGQAZABkAGQAZABkAGQAZABkAGQAZABkAGQAZABkAG/v8JAgEu
wmL2BA==
KqVIZx8BAAAAcgEAABx3LBAAAL4BAAAAAAEAAgAU////////AAAADABqb295b3VuZ2Nob2kJAOy1
nOyjvOyYgQgAMDIxOTAyMDQKADIwMTktMDItMjUAABoAam9veW91bmdjaG9pQGxndXBsdXMuY28u
a3ILADAxMDkxMTU5NzAxAgAxMgcATEdULUVNUAEAQwMAQkIzBgDshKDsnoQBACAIADAwMDU4NTgw
AQA0DgBMR+ycoO2UjOufrOyKpAAAfNEPAQBOAQAgAAAADABqb295b3VuZ2Nob2kJAOy1nOyjvOyY
gQgAMDIxOTAyMDQKADIwMTktMDItMjUAABoAam9veW91bmdjaG9pQGxndXBsdXMuY28ua3ILADAx
MDkxMTU5NzAxAgAxMgcATEdULUVNUAEAQwMAQkIzBgDshKDsnoQBACAIADAwMDU4NTgwAQA0DgBM
R+ycoO2UjOufrOyKpAAAfdEPAQBOAQAgRU2geg==
'/*!*/;
### UPDATE `mdlc`.`t_sys_user_if`
### WHERE
###   @1='abcdefghijkl' /* VARSTRING(1600) meta=1600 nullable=0 is_null=0 */
###   @2='홍길동' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @3='02190204' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @4='2019-02-25' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @5='' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @6='abcdefghijkl@lguplus.co.kr' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @7='01000000000' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @8='12' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @9='LGT-EMP' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @10='C' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @11='BB3' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @12='선임' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @13=' ' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @14='00058580' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @15='4' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @16='LG유플러스' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @17='' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @18='2024:11:28' /* DATE meta=0 nullable=0 is_null=0 */
###   @19='N' /* VARSTRING(1600) meta=1600 nullable=0 is_null=0 */
###   @20=' ' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
### SET
###   @1='abcdefghijkl' /* VARSTRING(1600) meta=1600 nullable=0 is_null=0 */
###   @2='홍길동' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @3='02190204' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @4='2019-02-25' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @5='' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @6='abcdefghijkl@lguplus.co.kr' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @7='01000000000' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @8='12' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @9='LGT-EMP' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @10='C' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @11='BB3' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @12='선임' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @13=' ' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @14='00058580' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @15='4' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @16='LG유플러스' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @17='' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */
###   @18='2024:11:29' /* DATE meta=0 nullable=0 is_null=0 */
###   @19='N' /* VARSTRING(1600) meta=1600 nullable=0 is_null=0 */
###   @20=' ' /* VARSTRING(1600) meta=1600 nullable=1 is_null=0 */

# Slave Data Value
mysql> select * from t_sys_user_if where login_id = 'jooyoungchoi'\G
*************************** 1. row ***************************
         LOGIN_ID: abcdefghijkl
             NAME: 홍길동
             CODE: 02190204
       START_DATE: 2019-02-25
         END_DATE:
            EMAIL: abcdefghijkl@lguplus.co.kr
           MOBILE: **********
     EX_EMP_DV_CD: 12
  EX_PROVUSERTYPE: LGT-EMP
  EX_HOFC_STTS_CD: C
EX_LGT_PSTN_DV_CD: BB3
EX_LGT_PSTN_DV_NM: 선임
      EX_DUTYCODE:
       EX_DEPT_CD: 00058580
    EX_DEPT_LEVEL: 4
     EX_COMP_NAME: LG유플러스
   EX_OUTER_EMAIL:
         TRANS_DT: 2024-11-28
      UPDATE_FLAG: N
      EX_DUTYNAME:
1 row in set (0.02 sec)

How to repeat:
don't know

Hi,

I have issues reproducing this issue.

I assume that table `mdlc`.`t_sys_user_if` do not have primary key?

Without a reproducible test case I doubt there is much we can do.

Bug #116983 is marked as duplicate of this one

Of course, `mdlc`.`t_sys_user_if` table has primary key.
LOGIN_ID is the primary key column of table.

I cannot reproduce this. Please at least share the table structure and SQL that generated this binlog.

Ideally you need to make a reproducible test case that I can use to reproduce this behavior as I can not reproduce the problem with data provided.