ReplicaDB in Amazon RDS environment. I need to move 1 table (
MYTable) from replica to other external DB (
My solution (do not work) was to add a
AFTER TRIGGER listening for
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?
1- I'm adding a new trigger on
SlaveDB (it is not a trigger replicated from
2- It is working in 5.7 -> 5.7 ... the issue is in 5.6 -> 5.6
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
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