How to setup AWS RDS triggers
AWS RDS triggers setup
When I first started working with AWS RDS triggers there was a slight learning curve as to how the Relational Database Service (RDS) works. Although the initial setup is straight forward, I ran into an issue when it came to creating triggers. I would get the following error.
[cc lang=”html” width=”100%”]ERROR 1227 (42000) at line 875: Access denied; you need (at least one of) the SUPER privilege(s) for this operation[/cc]
It took me awhile but I figured out the solution was to change log_bin_trust_function_creators from 0 to 1. Sounds simple right? So the next question is how do you do it? In the video below you will see the set by step process to do this…In addition I have also outlined the process below.
How to change log_bin_trust_function_creators step by step
- Create a new instance of MySQL RDS (if you have a current instance this will work too.)
- Click on Create Parameter (You will see your default parameter group. You can not edit this group, you must make a new parameter group to edit.)
- Fill in the required information then click create.
- Once you see your parameter group in the parameter group table check the box next to it.
- Click on the parameter group actions dropdown menu.
- Click edit.
- Search in the filter input for log_bin_trust_function_creators
- Change the setting from 0 to 1
- Then click Save Changes
- Now we need to assign the parameter group to the MySQL DB instance.
- Find your instances
- Check the radio button next to the instance you wish to assign the parameter group to
- Click on the instance actions drop down menu
- click modify
- Find the DB parameter group drop down
- Select the newly created triggers parameter group.
- Click continue
- Then save the DB changes
- Once you make the update you will need to reboot the DB instance before the triggers can be added.
- Go ahead and reboot your DB instance.
- Now you can create triggers in your MySQL instance.
The word “created” refers to the datetime column that will be updated automatically once a row is inserted.
create trigger trigger_name before insert
for each row
set new.created = now();
The word “updated” refers to the datetime column that will be updated automatically once a row is updated.
create trigger trigger_name before update
for each row
set new.updated = now();