Recently while trying to upgrade our project management app, I encountered this error, “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)”.
What we initially thought was the lack of TRIGGER privileges for that MySQL user. But even after giving the MySQL user all necessary privileges, we continued to experience that error during a backup process. By the way, you would need to specify the SUPER privileges for the user running the import into the database along with the CREATE ROUTINE, ALTER ROUTINE, CREATE TRIGGER, ALTER TRIGGER, CREATE FUNCTION and ALTER FUNCTION privileges. We use Amazon RDS for storing and managing data across all our websites and as many of you might be aware, Amazon isn’t keen on giving super privileges to users of their system.
To fix this issue, here is the solution that worked for us…
1) Open the Amazon RDS Console.
2) Go to the “Parameter Groups”
3) Create a New Parameter Group (You can add to existing custom parameter group if you got one). On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm.
4) Click on “Edit Parameters”
5) Look for the parameter “log_bin_trust_function_creators” and set its value to ‘1’
6) Click on “Save Changes”
7) Open “Instances” and Expand your Desired MySQL Instance
8) Click on “Instance Action” and Select “Modify”
9) Select the Parameter Group and enable “Apply Immediately”.
10) Click on “Continue” and Confirm Changes
It’s best to reboot for changes to reflect. Select the instance and reboot your MySQL instance. That should do the job. For those of you on traditional MySQL environment, you can specify the log_bin_trust_function_creators option in two ways.
1) Specify on Server Start as this “–log-bin-trust-function-creators=1”
2) By setting it to “1” using a Global Statement
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Alternatively, If you are not planning to use your MySQL server for the replication consider turning the binary logging off by removing the option –log-bin from the command options for the mysqld utility starting that MySQL server. Hope that helps developers experiencing similar issues with importing and exporting SQL or should I say, while creating a dump 😉