ROW replication applies a row that the replica rejects under its local CHECK constraint

Summary

MariaDB ROW replication can store a row on a replica even though that row
violates a CHECK constraint that exists on the replica table. The same row is
rejected by an ordinary local INSERT on the replica with error 4025, and
statement-style replication also stops with error 4025. In ROW format, however,
the replica SQL thread remains running and the row is present on the replica.

Environment

• Product: MariaDB Server
• Observed versions:
  • MariaDB 12.2.2 primary -> MariaDB 12.2.2 replica
  • MariaDB 10.6 primary -> MariaDB 12.2.2 replica
  • MariaDB 12.2.2 primary -> MariaDB 10.6 replica
  • MariaDB 10.6 primary -> MariaDB 10.6 replica
• Replication: asynchronous primary/replica, file/position replication
• Positive case: binlog_format=ROW
• Format controls: binlog_format=MIXED and binlog_format=STATEMENT
• Storage engine: InnoDB

Steps to reproduce

Set up an ordinary MariaDB primary and replica using file/position replication.
Use binlog_format=ROW on the primary.

Create intentionally different table definitions on the two endpoints. The
table has the same column layout on both endpoints; the replica has one
additional CHECK constraint. The local DDL is shown explicitly below because
the stricter replica definition is the condition being tested.

On the primary:

SET SESSION sql_log_bin=0;

DROP TABLE IF EXISTS d;

CREATE TABLE d(

  id INT PRIMARY KEY,

  v INT NOT NULL,

  note VARCHAR(64)

) ENGINE=InnoDB;

SET SESSION sql_log_bin=1;

On the replica:

SET SESSION sql_log_bin=0;

DROP TABLE IF EXISTS d;

CREATE TABLE d(

  id INT PRIMARY KEY,

  v INT NOT NULL,

  note VARCHAR(64),

  CONSTRAINT ck_nonnegative CHECK(v >= 0)

) ENGINE=InnoDB;

SET SESSION sql_log_bin=1;

First verify that the replica enforces its CHECK constraint under ordinary
local execution:

INSERT INTO d VALUES(999, -999, 'local-control');

The replica rejects this local statement:

ERROR 4025 (23000): CONSTRAINT `ck_nonnegative` failed for `test`.`d`

Then execute these statements on the primary and wait for the replica to apply
the primary's binary log position:

INSERT INTO d VALUES(1, 10, 'valid-before');

INSERT INTO d VALUES(2, -7, 'violates-replica-check');

Finally inspect the replica SQL-thread state and the table rows on both
endpoints:

SHOW SLAVE STATUS\G

SELECT * FROM d ORDER BY id;

Expected result

The replica has a local CHECK constraint that rejects v < 0 during ordinary
execution. The row arriving through replication should therefore not be stored
silently while the replica SQL thread reports no error. A consistent outcome
would be for the replica to stop with error 4025, as it does in the
statement-style controls below.

Actual result

With binlog_format=ROW, the replica SQL thread remains healthy and the row
that violates the replica CHECK constraint is stored on the replica:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_SQL_Errno: 0

Last_SQL_Error:

primary rows:

  (1, 10, 'valid-before')

  (2, -7, 'violates-replica-check')

replica rows:

  (1, 10, 'valid-before')

  (2, -7, 'violates-replica-check')

This is different from ordinary local execution on the replica, where the same
negative value is rejected by ck_nonnegative with error 4025.

Relevant controls

The same schema and INSERT were repeated with statement-style logging.

With binlog_format=MIXED on MariaDB 12.2.2 -> 12.2.2, the replica SQL
thread stopped with error 4025:

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_SQL_Errno: 4025

Last_SQL_Error: Error 'CONSTRAINT `ck_nonnegative` failed for `test`.`d`'

  on query. Default database: 'test'.

  Query: 'INSERT INTO d VALUES(2,-7,'violates-replica-check')'

With binlog_format=STATEMENT on MariaDB 12.2.2 -> 12.2.2, the replica SQL
thread also stopped with error 4025 on the same INSERT.

With binlog_format=MIXED on MariaDB 10.6 -> 12.2.2, the replica SQL thread
again stopped with error 4025 on the same INSERT.

These controls show that the replica CHECK constraint is active and that
statement-style replay reports the violation. The difference appears when the
same write is delivered as a ROW event.

The same shape was also run on MySQL 8.0.36 -> MySQL 8.0.36. With
binlog_format=ROW, MySQL stopped the replica SQL thread instead of storing
the row:

Replica_IO_Running: Yes

Replica_SQL_Running: No

Last_SQL_Errno: 3819

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s).

  The most recent failure being: Worker 1 failed executing transaction ...

The local MySQL replica control rejected the same row with:

ERROR 3819 (HY000): Check constraint 'ck_nonnegative' is violated.

Repetition and scope

The ROW-format behavior reproduced in the following completed executions:

MariaDB 12.2.2 -> 12.2.2: 5/5 executions

MariaDB 10.6   -> 12.2.2: 5/5 executions

MariaDB 12.2.2 -> 10.6:   3/3 executions

MariaDB 10.6   -> 10.6:   5/5 executions

In every execution, the ordinary local INSERT on the replica was rejected by
the CHECK constraint with error 4025, while the ROW-format replicated INSERT
left the replica SQL thread running and the violating row present.

The statement-style controls completed as follows:

MariaDB 12.2.2 -> 12.2.2, binlog_format=MIXED:     3/3 stopped with error 4025

MariaDB 12.2.2 -> 12.2.2, binlog_format=STATEMENT: 3/3 stopped with error 4025

MariaDB 10.6   -> 12.2.2, binlog_format=MIXED:     3/3 stopped with error 4025

The MySQL 8.0.36 comparison completed as follows:

MySQL 8.0.36 -> 8.0.36, binlog_format=ROW:       3/3 stopped with error 3819

MySQL 8.0.36 -> 8.0.36, binlog_format=STATEMENT: 3/3 stopped with error 3819

Nearby issues checked

This observation is not the same as cases where the primary disables
foreign_key_checks before writing inconsistent rows. In this reproduction,
the primary has no CHECK constraint at all, and it does not disable CHECK
constraint enforcement for the tested row. The stricter rule exists only on
the replica, and the replica enforces that rule under ordinary local execution
and statement-style replication.

The behavior is also different from a replica-side trigger error: no trigger is
defined in this reproduction. The only stricter rule on the replica is the
declarative CHECK constraint.