I have an Aurora cross region read replica set up in AWS. It all works perfect at the moment but when I create a trigger on the master instance it causes a replication error on the slave instance. The code for the trigger is below.
CREATE TRIGGER `mydb`.`user_AFTER_UPDATE` AFTER UPDATE ON `user` FOR EACH ROW
BEGIN
END
The error I get is:
Error 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation' on query. Default database: 'mydb'. Query: 'CREATE DEFINER=`myuser`@`%` TRIGGER `mydb`.`user_AFTER_UPDATE` AFTER UPDATE ON `user` FOR EACH ROW
BEGIN
END'
What's strange is that when I check the slave, the trigger is actually created. But because the error blocks up the replication I have to run the following to get it skip the error.
CALL mysql.rds_skip_repl_error;
I've tried setting log_bin_trust_function_creators to 1 but it doesn't seem to make any difference. When I run the following on the slave instance it says it's value is ON
SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';
Do I need to set that variable to 1 on the master instance as well? Any help would be really appreciated. I've been Googling for hours but most of the stuff I find is talking about MySQL dumps which can have the definer removed but obviously that isn't possible in this case.