Unable to create a trigger in mysql hosted on amazon cloud

2
votes

When am trying to create a simple trigger in mysql, am encountering the below error message. Please suggest me how to overcome this.

delimiter $$

create trigger trg_addresses_ins before insert on addresses
for each row
begin
   declare msg varchar(128);
     if length(new.addressstate) > 2 then
        set msg = concat('MyTriggerError: Trying to insert a state value of more than 2 character: ', new.addressstate);
    signal sqlstate '45000' set message_text = msg;
end if;
end$$

delimiter ;

`

Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 0.078 sec

Super user is enabled but still get the same error and also am unable to change database parameter group associated with mysql aws db instance to 1. I am unable to modify db instance to select newly created group as the parameter group field is read only.

enter image description here

Appreciate your valuable inputs.

Thanks!

1
mysqlamazon-web-servicestriggers

1 Answers

3
votes

I guess you are using the default DB parameter group which you can not modify, the solution is you need to create your own parameter group, and set log_bin_trust_function_creators = 1, and apply your own parameter group to your current instance.