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
Last activity: Apr 20, 2025, 05:10 PM