MySQL 5.6 in RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?

2018-02-02 21:25:34

I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB).

My solution (do not work) was to add a AFTER TRIGGER listening for UPDATES and INSERTS to MYTable (and only add that TRIGGER in ReplicaDB) and copy everything on MyTableLog. Them pooling MyTableLog (and remove already processed records).

Problem: It looks like the RDS replica it is not firing the AFTER INSERT event (is only firing AFTER UPDATES). However I tested the solution in 5.7 and it worked.

Any ideas? It is a bug in MySQL? Any solution?

According Paragraphs 1-3 of MySQL Documentation Replication and Triggers

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.

This behavior

  • According Paragraphs 1-3 of MySQL Documentation Replication and Triggers

    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.

    This behavior is by design. If under row-based replication the slave applied the triggers as well as the row changes caused by them, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.

    If you want triggers to execute on both the master and the slave—perhaps because you have different triggers on the master and slave—you must use statement-based replication. However, to enable slave-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient to switch to statement-based replication only for those statements wh

    2018-02-03 00:02:19