Not the answer you need?
Register and ask your own question!

Triggers fail on replicated data

DbaOnAMissionDbaOnAMission EntrantInactive User Role Beginner
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.


  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.