C.3.1. Triggers

Tungsten Replicator does not automatically shut off triggers on slaves.
This can create problems on slaves 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_master() 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 slave 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_master() 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_master() 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 slave.

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

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

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

Alternatively, if you are implementing the is_master()
within a clustering deployment, you could check the database read_only parameter. In a clustered
deployment, the slave 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