You do not have the SUPER Privilege and Binary Logging is Enabled

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 😉

#DEV Tutorials Useful & Productive

Magento Slow Backend but A Fast Frontend

Past two days has been a nightmare. We recently migrated all of our websites to Amazon Web Services (AWS), and the speed has been good. We love it. The infrastructure is excellent and so is the service we’re getting. I wouldn’t have a lot to say about their support, though. Unless you are a reasonably big enterprise which is spending a lot of dollars, you can’t afford their support packages. What I suggest from my personal experience is to subscribe to their developer support. If you get into issues relating to operating websites on their servers, they usually point you in the right direction. You will get a response generally within 24 hours which is ok.

The reason why I am writing this post is not to address that. It’s actually due to our experience with Magento. Over the past two days, I have learned so much about Magento E-Commerce Platform. One of our client who runs one of the biggest online pharmacies in New Zealand – hosts with us. The database is big, and so are the files. Migrating to AWS took a while, but we got there eventually. Since this website was so busy all through the day, the only time we could migrate had to be at midnight when it has the least amount of site traffic.

After migrating, we started noticing a significant problem. The speed of Magento’s backend or as some would address it as admin panel was terrible. So I did my little research on tackling this issue.