#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
This error occurs sometimes on RDS instances when you try to use procedures. You will soon find out that grant super privilege for a user won’t work. So the only way to make things work is to set log_bin_trust_function_creators to 1.
RDS console available at https://console.aws.amazon.com/rds/ allows you to create a new group and modify its parameters. Log in to RDS console, go to “DB Parameters Groups” and click the “Create DB Parameter Group”. Set the following
- DB Parameter Group Family: mysql5.1
- DB Parameter Group Name: mygroup
- Description: mygroup
Confirm by clicking “Yes, create” button.
Here comes the ugly part, since you cannot edit from the console the parameters (for the moment, I hope they are going to change that). You will need to log to your instance using SSH and download RDS cli from here: http://aws.amazon.com/developertools/2928?_encoding=UTF8&jiveRedirect=1
To do so right click on “Download” button and copy link location. In the SSH window use wget to download and unzip it:
wget "http://s3.amazonaws.com/rds-downloads/RDSCli.zip" unzip RDSCli.zip
If you don’t have unzip you can quickly get it using “apt-get install unzip”(for ubuntu) or “yum install unzip”(for centos). Of course you will need root privileges.
After successfully unpacking the RDSCli cd to that directory and set a few variables. Following is an example on Ubuntu 10.04:
cd RDSCli-1.4.006 export AWS_RDS_HOME="/home/ubuntu/RDSCli-1.4.006" export JAVA_HOME="/usr/lib/jvm/java-6-sun" cd bin ./rds --help
If rds –help outputs no errors then you have set it correctly. Congrats. One more command:
./rds-modify-db-parameter-group mygroup --parameters="name=log_bin_trust_function_creators, value=on, method=immediate" --I="YOUR_AWS_ACCESS_KEY_ID" --S="YOUR_AWS_SECRET_ACCESS_KEY"
The AWS keys can be obtain from your AWS account Security Credentials->Access Credentials->Access Keys.
Go to AWS RDS console, “DB Instances”, select your instance and right click “Modify”. Set “DB Parameter group” to “mygroup” and check “Apply Immediately”. Confirm with “Yes, modify”.
You are done 🙂