Sample Header Ad - 728x90

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

1 vote
1 answer
1074 views
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
Asked by Karel (11 rep)
Jan 31, 2018, 04:09 PM
Last activity: Apr 20, 2025, 05:10 PM