Executing triggers on slave in row-based replication

Sometimes it is wanted that triggers are executed on slaves instead of masters. This is of course possible in statement-based replication but not in row-based replication. Some reasons for trigger execution on slave are:

• to modify triggers without stopping the system, just stop the slave
• reduces load on master
• removes errors on master server in case of some poorly written trigger / UDF.

Galera user don't have the option of using statement-based replication since galera always uses row-based replication. It becomes tricky to get triggers to execute on the slaves.

New functionality is needed to enable triggers to execute on slaves in row-based replication.

Please start this task by doing adding a design here in the task description.

== General notes ==

The idea of the feature is that

• master-slave replication is used (i.e. this won't work inside Galera cluster)
• Triggers that are intended to be ran on the slave are not ran on the master.
• The setup is such that changes made on the master (and replicated) do not conflict with changes that are made on the slave.

== Solution overview ==

The server will get a new command-line option, tenative name
--slave-run-triggers.

When slave-run-triggers=yes, execution of row-based events by the slave SQL thread will cause triggers to be invoked.

Triggers will be invoked for each row inserted, updated, or deleted.

If a trigger fails with an error, replication will stop with an error.

=== Binary logging of trigger actions ===

The slave might be writing its own binary log. Should trigger actions be
logged? This will be controlled with the server option. In order to minimize the number of options, there will be one option with three possible values:

slave-run-triggers=no|yes|logging

=== Triggers on a parallel slave ===

Currently, parallel slave feature is under development for MariaDB. Parallel
execution in the slave will be achieved through two means:

1. "Domain ids" in GTID mode. If two master servers have different domain
ids, it is assumed that their events are independent, and can be applied
in parallel.

2. Group-commit-based. The idea is that if certain transactions were commited
in a group, then they do not conflict. The slave will apply them in any order,
but they will be committed in the same order as they were committed on the
master.

==== Parallel apply with conflicts in triggers ===

If the slave is 1) logging triggers' actions to binlog and 2) uses parallel apply,
there could be a problem:

Let's consider two transactions, T1 and T2, which didn't conflict on
the master and were committed in a group. In binlog, T1 comes before T2.

Let us also assume that slave has triggers for T1 and T2 which will conflict.
Let us assume that the slave tried to apply T1 and T2 in parallel. T2 happened
to come into "PREPARED" state before T1 could. Since the slave will try to
commit the transactions in the same order, it will postpone T2 before T1 has
committed. However, T1 cannot move into PREPARED state, because it conflicts
with T2 and so must wait until T2 either commits or aborts. That is, we got a
deadlock.

The conclusion is:

• If tiggers can cause non-conflicting transactions to come into
  conflict, and triggers' actions are written into the binary log, then
  parallel apply on the slave may cause deadlocks.

The solution for this is: ignore this problem. We think, it is a very rare scenario where
one would want to run parallel slave AND also have on that slave triggers whose execution
will cause conflicts, AND log triggers' actions into the binary log.

=== Triggers and GTIDs ===

Currently, there is work underway on MariaDB Global Transaction IDs (GTIDs).

If actions made by slave's triggers are not written into the binary log, there
is no issue.

If triggers' actions are written in the binary log, the following may happen:

• Master runs, commits and logs transaction with global id $TRX1.

• Slave runs that transaction. However, in slave's log, transaction $TRX1 will
  also include changes made by the slave.

This is okay, as long as DBAs take this into account, and do not do fail-over
between servers who have transactions-with-slave-triggers and
transactions-without-slave-triggers.

GTID replication has a "strict mode" which makes more checks and doesn't let one shoot himself in the foot. The suggestion is to disallow running with slave trigger actions being written to the binary log when in strict GTID mode.

== Implementation ==

• Take RBR event execution code, add two hooks there, one is for running BEFORE triggers, the other for running AFTER triggers.
• Like in regular execution, tables that are accessed by the trigger must be
  opened/locked together with the table(s) that is modified by the RBR event
  itself.

• A trigger can access old record columns with OLD.column_name and new record
  with NEW.column_name. We need to check how that is implemented and provide that also.

• Permission/environment concerns. It seems, trigger execution may depend on the enviroment. Look at the example given at
  http://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html. If the trigger
  like test.ins_sum depends on the value of @sum... Well, there is no way the
  slave could know what was the value of @sum on the master. This puts a
  limitation on what one can do within triggers. Besides that, we need to check
  what other execution context (e.g. CURRENT_USER()) should be provided for trigger execution.
  Note: SBR already runs triggers on slaves. This is an evidence that SQL Slave thread provides
  sufficient environment for triggers to be executed.

== "Triggers were executed" flag in the binlog ==

Monty's suggestion: Introduce a flag into binary log that has meaning "triggers were executed for this change".
The flag will be present in

• statement events for SBR
• Table_map_event for RBR (so that it has statement scope)

The slave will run triggers only when the triggers are present and the flag is not set for the event. If the slave has executed triggers, it will set the flag to true when writing its own binlog.

serg suggestion: not to introduce a flag as it doesn't solve any of the anticipated replication issues, and use something much simpler for mysqlbinlog|mysql case.

== Target version ==

The intent is to push this into 10.0.
The code should be developed against mariadb-5.5