MySQL/Amazon RDS error: "you do not have SUPER privileges..."
Solution 1
Per http://getasysadmin.com/2011/06/amazon-rds-super-privileges/, you need to set log_bin_trust_function_creators
to 1 in AWS console, to load your dump file without errors.
If you want to ignore these errors, and load the rest of the dump file, you can use the -f
option:
mysql -f my_database -u my_username -p -h
my_new_database.xxxxxxxxx.us-east-1.rds.amazonaws.com < my_database.sql
The -f
will report errors, but will continue processing the remainder of the dump file.
Solution 2
- Open the RDS web console.
- Open the “Parameter Groups” tab.
- Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm. Select the just created Parameter Group and issue “Edit Parameters”.
- Look for the parameter
log_bin_trust_function_creators
and set its value to1
. - Save the changes.
- Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
- Select the just created Parameter Group and enable “Apply Immediately”.
- Click on “Continue” and confirm the changes.
- Wait for the "Modifying" operation to be completed.
- Again, open the “Instances” tab. Expand your MySQL instance and expand “Instance Action” tab and select "Reboot".
A reboot isn't necessary since log_bin_trust_function_creators
has apply type = dynamic
. At least this is true if your RDS already has an attached parameter group and you edit it, as opposed to creating a new parameter group. Merely save the parameter edit and you're good to go.
Solution 3
The problem with triggers and stored procedures in the dump file is that these definitions include the user who the stored procedure should be created by, the DEFINER. The user most likely doesn't exist in the RDS so a error is then raised. To be able to load the dump file you can remove the DEFINER using sed or Perl and create the stored procedure/trigger with the user who is performing the import.
perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < mysqldump.sql > mysqldump.fixed.sql
Now you should be able to load the fixed dump file
mysql my_database -u my_username -p -h rds_host < mysqldump.fixed.sql
As said in earlier answer, you should set the DB Parameter:
log_bin_trust_function_creators = 1
Solution 4
For me, there was only 2 commands in my dump file which required SUPER privileges:
SET @@GLOBAL.gtid_purged
SET @@SESSION.SQL_LOG_BIN
According to the mysqldump docs you can disable these with --set-gtid-purged=OFF
.
Then looking at man mysqldump:
Use ON if the intention is to deploy a new replication slave using only some of the data from the dumped server. Use OFF if the intention is to repair a table by copying it within a topology. Use OFF if the intention is to copy a table between replication topologies that are disjoint and will remain so.
So I decided to add --set-gtid-purged=OFF
to my mysqldump
command and then I could successfully import the resulting dump file.
Solution 5
As defined in AWS documentation triggers, procedures, and functions are disabled by default because binary logging is enabled by default. Disabling basically makes your db more secure, but if you have properly secured through the network it won't matter.
Follow these steps and your problem will be fixed https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/
Also you shouldn't use definers when creating procedures. A simple sed command can remove it.
Related videos on Youtube
tim peterson
web programming-javascript, php, mysql, css, html-is my thang
Updated on April 09, 2022Comments
-
tim peterson about 2 years
I'm attempting to copy my mysql database from an Amazon EC2 to an RDS:
I successfully did a
mysqldump
of my database into my root folder using this:root@ip-xx-xx-xx-xx:~# mysqldump my_database -u my_username -p > my_database.sql
Then I tried to transfer this .sql file to my new RDS database:
root@ip-xx-xx-xx-xx:~# mysql my_database -u my_username -p -h my_new_database.xxxxxxxxx.us-east-1.rds.amazonaws.com < my_database.sql
Unfortunately, I get following error message:
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)
I tried to
GRANT SUPER..
in a variety of ways but I'm getting errors when I try to do that too. Typingmysql > FLUSH privileges;
doesn't work either.I'm a mysql beginner so sorry for such an easy question. Thoughts?
-
ceejayoz almost 12 yearsYou can't
GRANT SUPER
on RDS. RDS offers no way to get SUPER privileges. -
ad4s over 7 yearsUse same MySQL username for creating dump and restoring it (For connection and DEFINER keyword in the dump). Changing log_bin_trust_function_creators is not the desired solution. The worst thing is to use -f parameter in this case
-
-
tim peterson almost 12 yearshi @Ross, thanks for this. Unfortunately, using
-f
didn't help. I got the same error. Using your link, i'm having trouble with the RDS Cli tools syntax. Meaning, when I go to change the privileges I get the following error:rds-modify-db-parameter-group: Malformed input-Unrecognized option: -–parameters=name=log_bin_trust_function_creators, Usage: rds-modify-db-parameter-group DBParameterGroupName --parameters "name=value, value=value, method=value" [ --parameters "name=value, value=value, method=value" ...] [General Options]
-
tim peterson almost 12 yearshere's my command that gives me the above error:
./rds-modify-db-parameter-group mygroup -–parameters "name=log_bin_trust_function_creators, value=on, method=immediate" –I="accesskeyxxxxxx" –S="secretkeyxxxxxxxx"
I know it has to be a quotation or double-dash issue i but none of those types of changes is working so far, ugh! -
Ross Smith II almost 12 yearsThe
-f
option will not make the errors go away, it will just allow the non-offending SQL statements in the file to be processed. From what I've read, RDS is choking on stored procedures in the dump file. Try creating a dump file without store procedures and see if that loads OK:mysqldump --routines=0 --triggers=0 --events=0 my_database -u my_username -p
-
tim peterson almost 12 years-@Ross, awesome that worked, my database is there, wow that is an annoying issue, feel like AWS should do something about that.
-
siliconrockstar almost 9 yearsCleaning up definers can also be accomplished with sed: sed -i 's/DEFINER=
OldDefiner
@localhost
/DEFINER=NewDefiner
@localhost
/g' ./TargetSqlFile.sql -
Kaymaz over 6 yearsAt least I can load all the data with the
-f
option. Second phase might be to dump only routines/stred proc, etc separately -
AndrewL about 6 years> what does point 9 do ... you adjust your instance to use the parameter group defined previously. For more detail see this original blog post by Daniel Ferbers: techtavern.wordpress.com/2013/06/17/…
-
Ramratan Gupta about 6 yearsWill this stop RDS Mysql replication ?
-
Pawan Developers over 5 yearshi @arun-r , I go through steps your explained, but parameter 'log_bin_trust_function_creators' is not available in case. I think something is changed in latest AWS RDS. Can you please help me how can I do this now? thanks,
-
arun-r over 5 years@RamratanGupta It wont stop
-
arun-r over 5 years@PawanDevelopers which version you are using?
-
Pawan Developers over 5 years@arun-r thanks for your response. I solved my issue. Actually I found log_bin_trust_function_creators in list. but my issue is solved by contacting AWS support
-
lft93ryt over 5 yearsI have updated the parameter group of the Master node with log_bin_trust_function_creators = 1; I have run a
sed -ie "s/DEFINER=\`a-z0-9A-Z*\`@\`a-z0-9A-Z*\`/DEFINER=CURRENT_USER /g" localsqlfile
; But I am still getting the same error ofAccess denied; you need (at least one of) the SUPER privilege(s) for this operation
andAccess denied for user 'username'@'%' to database 'mysql'
when running with the -f command. -
Zolbayar over 4 yearsYeah, this was it. My dump file was relatively small, so I removed all occurrences of these 2 commands from it.
-
Mike about 2 yearsWish I saw this before the stuff above it ....