RDS

MySQL/Amazon RDS error: “you do not have SUPER privilege”

Access denied; you need the SUPER privilege for this operation you do not have SUPER privilege

This error normally will occur when try to import or restore mysql database into Amazon rds. The error clearly shows you do not have SUPER user privileges . Yes thats true , becuase Amazon will not provide any rds super user provilages even for default root or admin user.

You can can enable functions, procedures, and triggers when binary logging is enabled without the SUPER privilege by setting the below parameters to true in rds db parameters group.

"log_bin_trust_function_creators to true "

A default DB parameter group is created if you create a DB instance without specifying a DB parameter group that you create. This group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance.

You cannot modify the parameter settings of a default DB parameter group; you must create your own DB parameter group to change parameter settings from their default value. Note that not all DB engine parameters can be changed in a DB parameter group that you create.

If you still facing the issue , try below option

if you are using a .sql file generated by mysqldump, the file contains a instructor of !50017 DEFINER =root@localhost or similar thing which forces the definer of the function/procedure, this may cause issues in RDS (e.g. non-existed db user).

All you need to do is to remove the instruction on your .sql file:

 *nix : sed -e ‘s/DEFINER=^**\*/\*/’ ori.sql > new.sql 
Windows: findstr /v “DEFINER=” myDump.sql > dumpWithNoDefiner.sql
Tagged

Leave a Reply

Your email address will not be published. Required fields are marked *