C.3.1. Triggers

Tungsten Replicator does not automatically shut off triggers on Replicas. This can create problems on Replicas as the trigger will run twice. Typical symptoms are duplicate key errors, though other problems may appear.

There is no simple one-answer-fits-all solution as the behaviour of MySQL and Triggers will differ based on various conditions.

  • When using ROW Based Binary Logging, MySQL will log all data changes in the binary log, including any data changes performed as a result of a trigger firing

  • When using MIXED Based Binary Logging...

    • if the Trigger is deemed to be non-deterministic then MySQL will behave based on the ROW Based Logging rules and log all data changes, including any data changes performed as a result of a trigger firing.

    • if the Tigger is deemed to be deterministic, then MySQL will behave based on STATEMENT Based Logging rules and ONLY log the statement issued by the client and NOT log any changes as a result of the trigger firing

The mixed behaviour outlined above presents challenges for Tungsten Replicator because MySQL does not flag transactions as being the result of a trigger firing or a client application. Therefore, it is not possible for the replicator to make a decision either.

This means, that if you are running with MIXED Based Binary Logging enabled, then there may be times when you would want the triggers on the target to fire, and times when you don't. Therefore the recommendations are as follows:

Tungsten Clustering Deployments

  • Switch to ROW Based Binary Logging, and either

    • Implement the is_Primary() function outlined below, or

    • Use the replicate.ignore filter to ignore data changes to tables altered by Triggers (ONLY suitable if the filtered tables are solely managed by the Trigger)

Tungsten Replicator Deployments

  • If source instance is running in ROW Based Binary Logging mode

    • Drop triggers on target. This is practical in fan-in topologies for reporting or other cases where you do not need to failover to the Replica at a later time. Optionally also implement the dropddl.js JavaScript filter (Available in Tungsten Replicator v6.1.2 onwards) to prevent CREATE/DROP TRIGGER DDL being replicated, or

    • Implement the is_Primary() function outlined below, or

    • Use the replicate.ignore filter to ignore data changes to tables altered by Triggers (ONLY suitable if the filtered tables are solely managed by the Trigger)

  • If source instance is running in MIXED Based Binary Logging mode

    • Use the replicate.ignore filter to ignore data changes to tables altered by Triggers (ONLY suitable if the filtered tables are solely managed by the Trigger), or

    • Switch to ROW Based Binary Logging and follow recommendations above

The is_Primary() approach is simple to implement. First, create a function like the following that returns false if we are using the Tungsten user, as would be the case on a Replica.

create function is_Primary()
    returns boolean
    deterministic
    return if(substring_index(user(),'@',1) != 'tungsten',true, false); 

Next add this to triggers that should not run on the Replica, as shown in the next example. This suppresses trigger action to insert into table bar except on the Primary.

delimiter //
create trigger foo_insert after insert on foo
  for each row begin
    if is_Primary() then 
      insert into bar set id=NEW.id; 
    end if; 
  end;
//

As long as applications do not use the Tungsten account on the Primary, the preceding approach will be sufficient to suppress trigger operation.

Alternatively, if you are implementing the is_Primary() within a clustering deployment, you could check the database read_only parameter. In a clustered deployment, the Replica databases will be in read_only mode and therefore the trigger could be coded to only fire when the database read_only mode is OFF