MariaDB 10.1.1: triggers for RBR
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
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 KEYconstraints (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 KEYconstraints or there are other
UNIQUEkeys 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
UNIQUEconstraint 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.