For the complete documentation index, see llms.txt. This page is also available as Markdown.

Conflict Detection and Resolution (CDR) Triggers

Resolve row-based replication conflicts directly on the replica with Conflict Detection and Resolution (CDR) triggers, available in MariaDB Enterprise Server 12.3.

The terms master and slave have historically been used in replication, and MariaDB has begun the process of adding primary and replica synonyms. The old terms will continue to be used to maintain backward compatibility - see MDEV-18777 to follow progress on this effort.

Overview

In row-based replication (RBR), the replica applies the row events recorded in the primary's binary log. Each event carries a before-image (the row as it existed on the primary) and/or an after-image (the row as the primary changed it). The applier locates the matching row on the replica and performs the same insert, update, or delete.

A conflict occurs when the replica's local data has diverged from what the primary's event expects:

  • The primary inserts a row whose key already exists on the replica.

  • The primary updates or deletes a row that is missing on the replica.

  • The primary updates or deletes a row that exists on the replica but holds different values.

By default, these divergences raise a hard applier error (duplicate key, record not found, and so on) and stop the SQL thread, requiring an operator to intervene manually or skip the event.

CDR triggers let you encode the resolution policy as SQL, on the replica. When a conflict is detected, the applier diverts the failing row event into a user-defined trigger. Inside the trigger you decide, per row, whether to overwrite, merge, ignore, or deliberately halt.

Enabling CDR Triggers

CDR triggers fire only on the replica's SQL (applier) thread, and they operate on row-based replication events.

Primary-Side Configuration

The only requirement on the primary is that it logs changes in row format with full row images, so that the replica receives the before- and after-images CDR triggers depend on:

[mariadbd]
binlog_format    = ROW
binlog_row_image = FULL

No other CDR-specific configuration is needed on the primary, and CDR triggers do not need to exist on the primary. (STATEMENT and MIXED binary log formats do not carry the row images CDR requires, so they are not supported for this feature.)

Replica-Side Configuration

CDR triggers are gated by the same global system variable that controls running triggers on the replica for row-based events, slave_run_triggers_for_rbr. There is no separate CDR switch. For CDR conflict handling, set the variable to YES while the SQL thread is stopped:

Changing slave_run_triggers_for_rbr requires a privilege that permits setting this global variable, such as SUPER or the corresponding fine-grained global-variable grant.

The Five Conflict Types

A CDR trigger is bound to one conflict type. The type names encode what the primary did and what the replica's local state implies:

Conflict type
Primary's operation
Replica's local state
Typical underlying error

INSERT_INSERT

INSERT

Row with the same key already exists

Duplicate key

UPDATE_UPDATE

UPDATE

Row exists but its values differ

Before-image mismatch

DELETE_UPDATE

DELETE

Row exists but has been changed locally

Before-image mismatch

UPDATE_DELETE

UPDATE

Row is missing (already deleted locally)

Record not found

DELETE_DELETE

DELETE

Row is missing (already deleted locally)

Record not found

Syntax

<conflict_type> is one of INSERT_INSERT, UPDATE_UPDATE, DELETE_UPDATE, UPDATE_DELETE, DELETE_DELETE.

A conflict trigger has no BEFORE/AFTER timing keyword — the FOR CONFLICT clause replaces it. The trigger fires only at conflict time, on the applier thread. A single CREATE TRIGGER statement binds to exactly one conflict type: conflict types cannot be chained with OR, and a conflict type cannot be combined with the regular INSERT/UPDATE/DELETE trigger events. To handle several conflict types on the same table, create a separate trigger for each.

Example skeleton:

Row Accessors: NEW, OLD, and ORG

CDR triggers introduce a third row accessor, ORG, alongside the familiar NEW and OLD:

Accessor
Represents
Writable

NEW

The resolution image — the row you want the replica to end up with.

Yes

OLD

The replica's current local row (its actual stored state).

No

ORG

The primary's before-image from the replication event — the state the primary expected.

No

ORG is the key to conflict resolution: it lets the trigger compare what the primary believed (ORG) against what the replica actually has (OLD), and construct what should result (NEW).

Accessor Availability per Conflict Type

Some images do not logically exist for certain conflicts, so the parser rejects triggers that reference an unavailable accessor at CREATE TRIGGER time:

Conflict type

NEW

OLD

ORG

INSERT_INSERT

Yes

Yes

No (an insert has no before-image)

UPDATE_UPDATE

Yes

Yes

Yes

DELETE_UPDATE

Yes

Yes

Yes

UPDATE_DELETE

Yes

No (row missing locally)

Yes

DELETE_DELETE

Yes

No (row missing locally)

Yes

Violations produce an error when the trigger is created:

The Four Resolution Outcomes

Inside the trigger body, you choose one of four outcomes. A CDR trigger is not a general-purpose stored program for arbitrary DML against the conflicting table — these four outcomes are the supported ways to act on it.

Resolve by Assigning NEW (Apply the Row)

Set columns on NEW and let the trigger return normally. The applier writes your NEW image to the table:

  • For INSERT_INSERT, UPDATE_UPDATE, DELETE_UPDATE: the existing local row is overwritten with NEW.

  • For UPDATE_DELETE, DELETE_DELETE: a new row is inserted from NEW.

Do Nothing — Let the Default Operation Apply

If you do not assign NEW and return normally, the applier performs the operation's natural default for that conflict:

Conflict

Default action when NEW is left untouched

INSERT_INSERT

Overwrite the existing row with the primary's image.

UPDATE_UPDATE

Overwrite the local row with the primary's image.

DELETE_UPDATE

Delete the local row.

UPDATE_DELETE

Insert the primary's row.

DELETE_DELETE

No operation (the row is already absent).

Skip the Event — SIGNAL SQLSTATE '02TRG'

Raising the special SQLSTATE 02TRG tells the applier to gracefully ignore this row event and continue replication. The table is left unmodified (the replica keeps OLD, or stays empty for a *_DELETE conflict). The SQL thread does not stop, and the replica's GTID position advances past the event.

02TRG is a "no data in trigger" subclass of the standard 02 ("no data") SQLSTATE class and is intentionally treated as not an error.

Halt Deliberately — SIGNAL SQLSTATE '51CDR'

When a conflict is genuinely unresolvable by policy, raise SQLSTATE 51CDR with a custom error number to stop the SQL thread on purpose:

The SQL thread stops with the error number you supplied (9001 above), letting an operator investigate. After fixing the underlying data, run START REPLICA to resume.

Any other unhandled error raised inside the trigger also stops the SQL thread, but 51CDR is the intentional, documented way to do so.

Resolving Missing-Row Conflicts: the NEW Primary Key Sentinel

For UPDATE_DELETE and DELETE_DELETE the row is physically missing on the replica, so there is no OLD. The applier hands the trigger a blank NEW image with its primary key set to NULL as a sentinel, and interprets the trigger's result as follows:

  • If, after the trigger runs, NEW's primary key is still NULL, the applier treats it as "no resolution row" and performs the default action (insert the primary's row for UPDATE_DELETE, no-op for DELETE_DELETE).

  • If the trigger populates NEW's primary key (making it non-NULL), the applier applies NEW to the table.

Because OLD is unavailable, source the key (and any other values) from ORG, the primary's before-image:

If you instead leave NEW.a untouched, the conflict resolves as a no-op and the row stays absent.

The Before-Image Consistency Check

When a table has CDR triggers and an UPDATE or DELETE row event is applied, the applier locates the target row by primary key and compares the primary's before-image (ORG) against the row it actually found. A mismatch in the non-key columns means the replica's copy has diverged from what the primary expected, and is treated as a conflict.

What happens next depends on whether the mismatch is eligible to be routed to a CDR trigger:

  • Eligible → the trigger handles it. The mismatch is delivered to your UPDATE_UPDATE trigger (for UPDATE) or DELETE_UPDATE trigger (for DELETE), exactly like the other conflict types.

  • Not eligible → the applier raises the consistency error and stops the SQL thread, so an operator can reconcile rather than silently applying changes on top of unexpectedly diverged data:

A mismatch is eligible for trigger routing only when all of these hold:

  • the table has a primary key;

  • the replica's slave_parallel_mode is optimistic or a more conservative setting (conservative, minimal, none); and

  • the row event is not currently being applied as an optimistic speculative attempt. (This case is transient: the transaction rolls back and retries non-speculatively, and on retry the conflict is delivered to the trigger.)

So even on a table that has CDR triggers, error 6001 can still appear when the table lacks a primary key, or when the replica runs a parallel mode more aggressive than optimistic. This is why the recommended beta configuration keeps the replica at optimistic or below — see Limitations and Beta Caveats.

This check only exists when CDR triggers are present on the table. It is suppressed entirely when slave_exec_mode is IDEMPOTENT — but IDEMPOTENT mode is itself outside the supported beta configuration.

Worked Example

The following demonstrates all five conflict types with a mix of resolutions.

Operational Notes

  • One trigger per conflict type per table. Define separate triggers for the conflict types you want to handle. Conflict types you don't define fall back to the normal applier behavior (hard error, SQL thread stops).

  • Define triggers on servers that act as replicas. A server that only acts as a primary does not need CDR triggers. In ring replication, every server replicates from another, so every server should define them. A CDR trigger created on the primary and replicated does not resolve conflicts there — the trigger only fires on a replica's applier thread.

  • You can write to other tables. The NEW/OLD/ORG accessors act on the conflicting table, but the trigger body may run DML against other tables (for example, an audit or exception log). Keep this lightweight — it runs inline on the applier thread.

  • Monitor the SQL thread. A 51CDR halt, an unhandled trigger error, or a before-image mismatch (error 6001) stops the SQL thread. Watch Last_SQL_Errno and Last_SQL_Error in SHOW REPLICA STATUS.

  • The Executed_triggers status variable increments for each trigger invocation (CDR triggers included), which is useful for confirming the feature is firing.

Limitations and Beta Caveats

CDR triggers are beta. The current implementation is validated only within the configuration below. Outside it, behavior is unsupported, unspecified, or known-incomplete:

Area
Current state in beta

Row image format

Supported only with binlog_row_image = FULL. Other row-image settings are not supported.

System-versioned tables

Not supported. Do not create CDR triggers on tables with system versioning.

Parallel replication mode

Supported up to and including slave_parallel_mode = optimistic (that is, none, minimal, conservative, optimistic). aggressive is not supported.

slave_exec_mode = IDEMPOTENT

Behavior in combination with IDEMPOTENT is unspecified. IDEMPOTENT also disables the before-image consistency check.

Multiple triggers per conflict type

Creating more than one CDR trigger for the same conflict type on the same table is not prevented, but the effect is unspecified.

Coexistence with regular RBR triggers

A CDR trigger alongside a normal replica-side RBR trigger on the same table is intended to work but may produce unexpected results. Test thoroughly.

Mixing with multi-event triggers

A single CREATE TRIGGER statement is either a CDR trigger or a regular trigger, never both — see below.

No Mixing of CDR and Regular Trigger Syntax

MariaDB supports defining a single regular trigger that fires on more than one event by chaining events with OR (MDEV-10164). CDR triggers and this multi-event syntax are mutually exclusive within a single CREATE TRIGGER statement:

  • A CDR trigger binds to exactly one conflict type — FOR CONFLICT INSERT_INSERT OR UPDATE_UPDATE is rejected.

  • A conflict type cannot be mixed with regular events in the same statement — FOR CONFLICT combined with INSERT, UPDATE, or DELETE is rejected.

  • The FOR CONFLICT clause replaces the BEFORE/AFTER timing keyword entirely; a regular trigger cannot carry a FOR CONFLICT clause.

Validate CDR triggers in a staging environment that mirrors your production topology before enabling them on a production replica, and watch the SQL thread closely during the initial rollout.

See Also

This page is: Copyright © 2026 MariaDB. All rights reserved.

spinner

Last updated

Was this helpful?