Buy Percona ServicesBuy Now!

Triggers fail on replicated data

  • Filter
  • Time
  • Show
Clear All
new posts

  • Triggers fail on replicated data

    I upgraded server to xenial and as part of the process moved from Percona 5.5.34 to Percona 5.5.51.
    I have a slave running Percona 5.5.20 that has a few on update, delete and insert triggers that has been working for the last 6 months.
    Since the upgrade triggers on the slave no longer fire for replicated events. When I make the updates local the trigger works fine, but any replicated updates gets ignored.
    The binlog_format for the master is ROW based. The tx_isolation is set to REPEATABLE-READ. I experimented with is as read-commited and also changed binlog format to MIXED but got no change.

    Is there a known issue between the versions or am I missing something else? I will upgrade the slave, but it is a heavilly utilized reporting slave so I will have to wait for a maintenance window.

    Here is an example of the trigger:
    CREATE DEFINER=`xxxxx`@`localhost` TRIGGER tr_change_to_admintable
    AFTER update ON admintable for each row
    insert into account_tracking (updatetime, identity) values (now(), NEW.identity) on duplicate key update updatetime = now();

    Any help or insight would be appreciated.

  • #2
    Quoting from manual

    With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
    If you are saying that replicating triggers events which are fired on master are not replicated to slave then it should be. Can you please post master and slave my.cnf to see If you are using replication filters.