1

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?

UPDATES:

1- I'm adding a new trigger on SlaveDB (it is not a trigger replicated from MasterDB)

2- It is working in 5.7 -> 5.7 ... the issue is in 5.6 -> 5.6 MySQL DB

3- Im adding TWO triggers (both works in 5.7 but only 1 works in 5.6)

4- Update Trigger (works in 5.6 and 5.7)

CREATE TRIGGER after_mytable_update
  AFTER UPDATE ON mytable
FOR EACH ROW
BEGIN
  INSERT INTO mytable_log
  SET is_new = 0, is_processed = 0;
END

5- Insert Trigger (DO NOT WORK IN MYSQL 5.6)

CREATE TRIGGER after_mytable_insert
  AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
  INSERT INTO mytable_log
  SET is_new = 1, is_processed = 0;
END

As simple as it is. The INSERT trigger it **IS NOT being executed in MySQL 5.6**

(From Comment)

CREATE TABLE mytable_log_replica (
    replica_id int(11) unsigned NOT NULL AUTO_INCREMENT, 
    is_new int(11) DEFAULT '0', 
    is_processed int(11) DEFAULT '0', 
    id int(11) unsigned NOT NULL, 
    stamp datetime DEFAULT NULL, 
    user_id int(11) DEFAULT NULL, 
    name varchar(64) DEFAULT NULL, 
    address varchar(64) DEFAULT NULL, 
    transaction_status varchar(64) DEFAULT NULL, 
    ip varchar(64) DEFAULT NULL, 
    cb_code varchar(16) DEFAULT NULL PRIMARY KEY (replica_id)
) ENGINE=InnoDB AUTO_INCREMENT=240878 DEFAULT CHARSET=latin1
5
  • Seeing the CREATE TRIGGER may give us some more clues with which to help you.
    – Rick James
    Feb 4 '18 at 16:04
  • Just to clarify: I'm adding a new trigger in Slave DB (It is not a trigger replicated from Martes DB)
    – Karel
    Feb 5 '18 at 17:35
  • Let's see SHOW CREATE TABLE mytable_log.
    – Rick James
    Feb 16 '18 at 18:22
  • CREATE TABLE mytable_log_replica ( replica_id int(11) unsigned NOT NULL AUTO_INCREMENT, is_new int(11) DEFAULT '0', is_processed int(11) DEFAULT '0', id int(11) unsigned NOT NULL, stamp datetime DEFAULT NULL, user_id int(11) DEFAULT NULL, name varchar(64) DEFAULT NULL, address varchar(64) DEFAULT NULL, transaction_status varchar(64) DEFAULT NULL, ip varchar(64) DEFAULT NULL, cb_code varchar(16) DEFAULT NULL PRIMARY KEY (replica_id) ) ENGINE=InnoDB AUTO_INCREMENT=240878 DEFAULT CHARSET=latin1
    – Karel
    Feb 16 '18 at 19:01
  • Is there a particular reason why you are not using a standard insert in your triggers? You syntax may very well work in MySQL, but is as far as I can tell not valid SQL
    – Lennart
    Jun 2 '19 at 21:06
0

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 where you want this effect, and to use row-based replication the rest of the time.

MySQL default for binlog_format in 5.6 is STATEMENT and ROW in 5.7. You will have to set that in the DB Parameter Group. You may need to convert the binlog_format to STATEMENT on RDS Master, RDS Slave, and the External Slave. Only the can you configure a trigger on the External Slave.

Interestingly, MariaDB has in its Documentation Running Triggers on the Slave for Row-based Events. You may have to switch to MariaDB.

1
  • Good point Rolando. However in my scenario I don’t have the TRIGGER statement in Master DB. I added the Trigger only on slave DB. In 5.7 works and in 5.6 don’t.
    – Karel
    Feb 3 '18 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.