Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5095

Executing triggers on slave in row-based replication



    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.0
    • None


      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

      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:


      === 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

      ==== 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

      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

      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
      • 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


        Issue Links



              sanja Oleksandr Byelkin
              ratzpo Rasmus Johansson (Inactive)
              1 Vote for this issue
              11 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.