Replica crash during ROW apply can lose replica-side trigger effects while replication remains healthy

Summary

MariaDB ROW replication can recover from a replica crash with the replicated
base rows applied and the replica SQL thread running, but without the durable
effects of a replica-side trigger that was executing at the crash point. The
same trigger writes the expected audit rows when no crash is injected. The
observed result is a replica whose source-originated table matches the primary,
while a local table maintained by slave_run_triggers_for_rbr=YES is missing
rows.

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
• Replication: asynchronous file/position replication
• Binary logging: binlog_format=ROW
• Replica trigger mode: slave_run_triggers_for_rbr=YES
• Storage engine: InnoDB

Steps to reproduce

Set up a MariaDB primary and replica using file/position ROW replication. The
steps below assume the replica server process can be terminated and restarted.
For example, if the replica runs in a disposable container, replace
<replica-container> with that container name.

Create the base table on both endpoints:

CREATE DATABASE IF NOT EXISTS mdev_row_apply_trigger_crash;

USE mdev_row_apply_trigger_crash;

DROP TABLE IF EXISTS side_audit;

DROP TABLE IF EXISTS base_t;

CREATE TABLE base_t(

  id INT PRIMARY KEY,

  v INT NOT NULL,

  g INT GENERATED ALWAYS AS (v * 2) STORED,

  note VARCHAR(32) NOT NULL

) ENGINE=InnoDB;

On the replica only, create a local audit table and a local AFTER INSERT trigger
that sleeps after writing the audit row:

USE mdev_row_apply_trigger_crash;

CREATE TABLE side_audit(

  seq INT AUTO_INCREMENT PRIMARY KEY,

  row_id INT NOT NULL,

  seen_v INT NOT NULL,

  seen_g INT NOT NULL,

  note VARCHAR(32) NOT NULL

) ENGINE=InnoDB;

DELIMITER //

CREATE TRIGGER base_t_ai AFTER INSERT ON base_t

FOR EACH ROW

BEGIN

  INSERT INTO side_audit(row_id,seen_v,seen_g,note)

  VALUES(NEW.id,NEW.v,NEW.g,NEW.note);

  SET @mdev_trigger_sleep = SLEEP(8);

END//

DELIMITER ;

SET GLOBAL slave_run_triggers_for_rbr='YES';

Stop only the replica SQL thread, leaving the IO thread running:

STOP SLAVE SQL_THREAD;

On the primary, insert two ordinary rows under ROW logging:

USE mdev_row_apply_trigger_crash;

SET SESSION binlog_format='ROW';

INSERT INTO base_t(id,v,note) VALUES(1,10,'prepared');

INSERT INTO base_t(id,v,note) VALUES(2,20,'witness');

SHOW MASTER STATUS;

On the replica, wait until the IO thread has fetched through the primary log
position shown by SHOW MASTER STATUS, then start the SQL thread:

SHOW SLAVE STATUS\G

START SLAVE SQL_THREAD;

While the SQL thread is executing the trigger sleep, SHOW PROCESSLIST shows
the applier in User sleep. Terminate the replica server at that point and
restart it:

docker kill --signal=KILL <replica-container>

docker start <replica-container>

After the replica server is available again, resume replication and wait for it
to catch up:

START SLAVE;

SHOW SLAVE STATUS\G

Inspect both tables on the replica:

USE mdev_row_apply_trigger_crash;

SELECT id,v,g,note FROM base_t ORDER BY id;

SELECT row_id,seen_v,seen_g,note FROM side_audit ORDER BY seq;

SHOW SLAVE STATUS\G

Expected result

Because slave_run_triggers_for_rbr=YES makes ROW apply run the replica's
local trigger, recovery should not leave the replicated row visible without the
corresponding durable trigger effect. A consistent recovered result would either
replay both the base rows and the audit rows, or stop replication with an apply
or recovery error.

For the SQL above, a completed recovered replica should contain:

base_t:

  (1, 10, 20, 'prepared')

  (2, 20, 40, 'witness')

side_audit:

  (1, 10, 20, 'prepared')

  (2, 20, 40, 'witness')

Actual result

In repeated executions, the replica recovered with the source-originated rows
present and the replication threads healthy, but with the trigger-maintained
audit table empty:

base_t:

  (1, 10, 20, 'prepared')

  (2, 20, 40, 'witness')

side_audit:

  empty

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_SQL_Errno: 0

Last_SQL_Error:

Relevant controls

The same schedule without terminating the replica produces both base rows and
both audit rows on the replica:

base_t:

  (1, 10, 20, 'prepared')

  (2, 20, 40, 'witness')

side_audit:

  (1, 10, 20, 'prepared')

  (2, 20, 40, 'witness')

The same recovery behavior was also observed when the source used one ordinary
BEGIN/COMMIT transaction containing both INSERT statements.

An additional XA variant reproduced the same behavior with XA COMMIT and
XA ROLLBACK, but XA is not required for the ordinary INSERT reproduction
shown above.

Repetition and scope

The ordinary INSERT comparison reproduced the missing trigger effects in the
following completed executions:

MariaDB 12.2.2 -> 12.2.2: 8/8 executions

MariaDB 10.6   -> 12.2.2: 8/8 executions

The no-crash controls completed without the mismatch:

MariaDB 12.2.2 -> 12.2.2: 4/4 executions

MariaDB 10.6   -> 12.2.2: 4/4 executions

The original XA variant reproduced the same missing trigger effects in 24/24
crash executions across the same two version directions, with 8/8 no-crash
controls completing normally.

Nearby issues checked

This is different from a normal replica-side trigger execution issue: without a
crash, slave_run_triggers_for_rbr=YES produced the expected audit rows.

This is different from a replica-side trigger error case: the trigger body above
does not raise SQLSTATE 45000 or another SQL error.

This is different from chained replica trigger logging behavior: the topology
needed for the reproduction is a single primary and a single replica, and the
missing state is local to the crashed replica.