[MDEV-39500] RBR Applier: Silent Update Success via Primary Key despite other Column Mismatch - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.6.25
Fix Version/s: None
Component/s: Replication
Labels:
None
Environment:
RHEL 9/CentOS 9

Description

Note: this is a wishlist bug/feature request, for optionally comparing more columns in before image when applying a row event. For performance reasons, the default, correct behaviour is to locate the row just by a primary key or non-null unique index key.

We are observing cases where the RBR slave applies UPDATEs successfully even when the "Before Image" (the WHERE clause in the binary log) does not match the actual non-primary-key data residing on the slave.

Based on our documentation, when a Primary Key (PK) exists, the replication applier prioritizes the PK to locate the row. If the PK matches, the storage engine may apply the update immediately without validating if the remaining columns match the source’s original state. While this is "mechanically expected" for performance, it allows silent data drift to go undetected, as the slave does not stop with a "Record not found" error.

This is a generic case, I believe happens at both Community and Enterprise any versions of MariaDB. Validated at 10.6.25 both Community and Enterprise versions.

*Test case & Result:
*
*Master node:
*

[root@standalone-node1 ~]# mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 10

Server version: 10.6.25-21-MariaDB-enterprise-log MariaDB Enterprise Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS test_db;

Query OK, 1 row affected (0.004 sec)

MariaDB [(none)]> USE test_db;

Database changed

MariaDB [test_db]>

MariaDB [test_db]> CREATE TABLE replication_test (

    ->     id INT PRIMARY KEY,

    ->     config_key VARCHAR(50),

    ->     config_value VARCHAR(50)

    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.033 sec)

MariaDB [test_db]>

MariaDB [test_db]> INSERT INTO replication_test (id, config_key, config_value)

    -> VALUES (1, 'STATUS', 'OLD_VALUE');

Query OK, 1 row affected (0.009 sec)

MariaDB [test_db]> select * from test_db.replication_test\G

*************************** 1. row ***************************

          id: 1

  config_key: STATUS

config_value: OLD_VALUE

1 row in set (0.001 sec)

MariaDB [test_db]> UPDATE test_db.replication_test SET config_value = 'NEW_VALUE' WHERE id = 1 AND config_key = 'STATUS';

Query OK, 1 row affected (0.004 sec)

Rows matched: 1  Changed: 1  Warnings: 0

*Master binlog extract:
*

# at 1661

#260430  2:16:45 server id 1  end_log_pos 1661 CRC32 0xfda19c77         Annotate_rows:

#Q> UPDATE test_db.replication_test SET config_value = 'NEW_VALUE' WHERE id = 1 AND config_key = 'STATUS'

#260430  2:16:45 server id 1  end_log_pos 1729 CRC32 0xa308e39f         Table_map: `test_db`.`replication_test` mapped to number 18

# at 1729

#260430  2:16:45 server id 1  end_log_pos 1807 CRC32 0xe0595963         Update_rows: table id 18 flags: STMT_END_F

BINLOG '

jbvyaRMBAAAARAAAAMEGAAAAABIAAAAAAAEAB3Rlc3RfZGIAEHJlcGxpY2F0aW9uX3Rlc3QAAwMP

DwQyADIABp/jCKM=

jbvyaRgBAAAATgAAAA8HAAAAABIAAAAAAAEAAwcH+AEAAAAGU1RBVFVTCU9MRF9WQUxVRfgBAAAA

BlNUQVRVUwlORVdfVkFMVUVjWVng

'/*!*/;

### UPDATE `test_db`.`replication_test`

### WHERE

###   @1=1

###   @2='STATUS'

###   @3='OLD_VALUE'

### SET

###   @1=1

###   @2='STATUS'

###   @3='NEW_VALUE'

# Number of rows: 1

# at 1807

#260430  2:16:45 server id 1  end_log_pos 1838 CRC32 0x2ef1ea29         Xid = 29

COMMIT/*!*/;

Slave node:

## Made mismatch data manually

[root@standalone-node2 ~]# mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 21

Server version: 10.6.25-21-MariaDB-enterprise-log MariaDB Enterprise Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select * from test_db.replication_test\G

*************************** 1. row ***************************

          id: 1

  config_key: STATUS

config_value: OLD_VALUE

1 row in set (0.001 sec)

MariaDB [(none)]> SET SQL_LOG_BIN=0

    -> ;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> UPDATE test_db.replication_test

    -> SET config_key = 'MISMATCH_KEY'

    -> WHERE id = 1;

Query OK, 1 row affected (0.018 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> select * from test_db.replication_test\G

*************************** 1. row ***************************

          id: 1

  config_key: MISMATCH_KEY

config_value: OLD_VALUE

1 row in set (0.000 sec)

## After update executed at master, got updated via replication event

MariaDB [(none)]>

MariaDB [(none)]>

MariaDB [(none)]> select * from test_db.replication_test\G

*************************** 1. row ***************************

          id: 1

  config_key: STATUS

config_value: NEW_VALUE

1 row in set (0.003 sec)

MariaDB [(none)]>

1. Relay log extract: Showing exact format of the data, as it gets from the binlogs from master node(expected behavior)*

#260430  2:16:45 server id 1  end_log_pos 1661 CRC32 0xfda19c77         Annotate_rows:

#Q> UPDATE test_db.replication_test SET config_value = 'NEW_VALUE' WHERE id = 1 AND config_key = 'STATUS'

#260430  2:16:45 server id 1  end_log_pos 1729 CRC32 0xa308e39f         Table_map: `test_db`.`replication_test` mapped to number 18

# at 1938

#260430  2:16:45 server id 1  end_log_pos 1807 CRC32 0xe0595963         Update_rows: table id 18 flags: STMT_END_F

BINLOG '

jbvyaRMBAAAARAAAAMEGAAAAABIAAAAAAAEAB3Rlc3RfZGIAEHJlcGxpY2F0aW9uX3Rlc3QAAwMP

DwQyADIABp/jCKM=

jbvyaRgBAAAATgAAAA8HAAAAABIAAAAAAAEAAwcH+AEAAAAGU1RBVFVTCU9MRF9WQUxVRfgBAAAA

BlNUQVRVUwlORVdfVkFMVUVjWVng

'/*!*/;

### UPDATE `test_db`.`replication_test`

### WHERE

###   @1=1

###   @2='STATUS'

###   @3='OLD_VALUE'

### SET

###   @1=1

###   @2='STATUS'

###   @3='NEW_VALUE'

# Number of rows: 1

# at 2016

#260430  2:16:45 server id 1  end_log_pos 1838 CRC32 0x2ef1ea29         Xid = 29

COMMIT/*!*/;

DELIMITER ;

Binlog extract from slave node: After the changes, logging the binglog events at the slave clearly showing the mismatch data with that row(expected behavior)

#260430  2:16:45 server id 1  end_log_pos 1528 CRC32 0x3c93dee2         Annotate_rows:

#Q> UPDATE test_db.replication_test SET config_value = 'NEW_VALUE' WHERE id = 1 AND config_key = 'STATUS'

#260430  2:16:45 server id 1  end_log_pos 1596 CRC32 0x7597736a         Table_map: `test_db`.`replication_test` mapped to number 19

# at 1596

#260430  2:16:45 server id 1  end_log_pos 1680 CRC32 0xe77c5337         Update_rows: table id 19 flags: STMT_END_F

BINLOG '

jbvyaRMBAAAARAAAADwGAAAAABMAAAAAAAEAB3Rlc3RfZGIAEHJlcGxpY2F0aW9uX3Rlc3QAAwMP

DwQyADIABmpzl3U=

jbvyaRgBAAAAVAAAAJAGAAAAABMAAAAAAAEAAwcH+AEAAAAMTUlTTUFUQ0hfS0VZCU9MRF9WQUxV

RfgBAAAABlNUQVRVUwlORVdfVkFMVUU3U3zn

'/*!*/;

### UPDATE `test_db`.`replication_test`

### WHERE

###   @1=1

###   @2='MISMATCH_KEY'

###   @3='OLD_VALUE'

### SET

###   @1=1

###   @2='STATUS'

###   @3='NEW_VALUE'

# Number of rows: 1

# at 1680

#260430  2:16:45 server id 1  end_log_pos 1711 CRC32 0xb071216e         Xid = 22

COMMIT/*!*/;

DELIMITER ;

Attachments

Activity

People

Assignee:: Andrei Elkin

Reporter:: suresh ramagiri

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2026-04-30 04:28

Updated:: 2026-05-04 09:18

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.