MariaDB 10.1.1: triggers for RBR

Written by Written by Sergei

Sometimes users ask for something that doesn’t really make sense. On the first glance. But then you start asking and realize that the user was right, you were wrong, and it is, actually, a perfectly logical and valid use case.

I’ve had one of these moments when I’ve heard about a request of making triggers to work on the slave in the row-based replication. Like, really? In RBR all changes made by triggers are replicated from the master to slaves as row events. If triggers would be fired on the slave they would do their changes twice. And anyway, assuming that one only has triggers one the slave (why?) in statement-based replication triggers would run on the slave normally, wouldn’t they?

Well, yes, they would, but one cannot always use statement-based replication. If one could, RBR would’ve never been implemented. There are many cases that statement-based replication cannot handle correctly. Galera requires RBR too. And as it turned out, that user, indeed, only had triggers on the slave — the master gets all the updates and slaves maintain summary tables that triggers keep up to date.

That’s why MariaDB 10.1.1 can now optionally invoke triggers for row-based events. This is controlled by the slave_run_triggers_for_rbr system variable. This variable is defined as

MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
   --> VARIABLE_NAME='slave_run_triggers_for_rbr'G
*************************** 1. row ***************************
        VARIABLE_NAME: SLAVE_RUN_TRIGGERS_FOR_RBR
        SESSION_VALUE: NULL
         GLOBAL_VALUE: NO
  GLOBAL_VALUE_ORIGIN: COMPILE-TIME
        DEFAULT_VALUE: NO
       VARIABLE_SCOPE: GLOBAL
        VARIABLE_TYPE: ENUM
     VARIABLE_COMMENT: Modes for how triggers in row-base replication on
slave side will be executed. Legal values are NO (default), YES and
LOGGING. NO means that trigger for RBR will not be running on slave. YES
and LOGGING means that triggers will be running on slave, if there was not
triggers running on the master for the statement. LOGGING also means
results of that the executed triggers work will be written to the binlog.
    NUMERIC_MIN_VALUE: NULL
    NUMERIC_MAX_VALUE: NULL
   NUMERIC_BLOCK_SIZE: NULL
      ENUM_VALUE_LIST: NO,YES,LOGGING
            READ_ONLY: NO
COMMAND_LINE_ARGUMENT: REQUIRED
1 row in set (0.01 sec)

That is, it’s a global variable, it can be set to YES, NO, and LOGGING. By default it’s NO. The value of YES will, naturally, make triggers to run for RBR events. The value of LOGGING will make them to run and changes made by the triggers will be written into the binary log. This mode can be changed run-time or from the command line or a config file.

Either way, when slave_run_triggers_for_rbr is not NO MariaDB slaves will invoke specific triggers for certain row events:

  • Update_row_event will invoke an UPDATE trigger
  • Delete_row_event will invoke a DELETE trigger
  • Write_row_event is a bit tricky. It is applied (yes, in MySQL too) as follows:
    • The slave tries to insert a row.
    • If the table has UNIQUE KEY constraints (or a PRIMARY KEY) and there is a conflicting row — it’ll be updated to have all values as in what should’ve been inserted.
    • But if the table also has FOREIGN KEY constraints or there are other UNIQUE keys in the table, old row will be deleted and new row will be inserted. Two operations instead of one, so it’s slower, but guarantees that there will be no references to the old row after it disappears.

    That is, Write_row_event can invoke INSERT trigger, DELETE trigger, or UPDATE trigger, depending on whether a conflicting row exists, which UNIQUE constraint was violated and whether a table was referenced in a foreign key constraint. If you think it’s too complex — you’re right, we’ll simplify it in 10.1.2.

In setups like this, particularly if one replicates further, these slaves being masters to some other slaves, there is a risk that triggers will be run multiple times for the same row. To detect this kind of mistakes, MariaDB marks row events that have invoked triggers, and when these events are replicated, they won’t cause further trigger invocations on slaves. That is, the master must not have any triggers on tables in question, otherwise slave-side triggers will not be invoked.