July 14, 2021 . 4 MIN READ
aws aurora ERROR 1227 (42000) at line 9311: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
https://stackoverflow.com/questions/44015692/access-denied-you-need-at-least-one-of-the-super-privileges-for-this-operat
**Error**: *ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation*
If your dump file doesn’t have DEFINER, make sure these lines below are also removed if they’re there, or commented-out with –:
At the start:
— SET @@SESSION.SQL_LOG_BIN= 0;
— SET @@GLOBAL.GTID_PURGED=/*!80000 ‘+’*/ ”;
At the end:
— SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
You can prevent these by adding –set-gtid-purged=OFF to your mysqldump command. Found here: stackoverflow.com/a/56251925 – Illya Moskvin Dec 27 ’19 at 7:13
Another useful trick is to invoke mysqldump with the option –set-gtid-purged=OFF which does not write the following lines to the output file:
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 ‘+’*/ ”;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
Either remove the DEFINER=.. statement from your sqldump file, or replace the user values with CURRENT_USER.
The MySQL server provided by RDS does not allow a DEFINER syntax for another user (in my experience).
You can use a sed script to remove them from the file:
sed ‘s/\sDEFINER=`[^`]*`@`[^`]*`//g’ -i oldfile.sql
https://www.digitalocean.com/community/questions/managed-database-mysql-error-while-importing-database
I’m getting a user privilege error for doadmin while importing a database via command line client on to the newly created database cluster. Importing databases
**Error**: *ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation*
How do I grant doadmin Super_priv?
Thanks,
GA
Add a comment
Subscribe
Share
Report
greenaro
edited by bobbyiliev
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
×
2 answers
6
arimus December 10, 2019
Btw, for anyone that is manually dumping from a MySql instance (e.g. another Digital Ocean db), here is what I needed to do to get a dump that would cleanly import without errors. Note, this would seem to be related to trying to import a dump that requires perms (which you do not have) due to the way Digital Ocean is set up to manage good things like replication on your behalf without you messing things up 😉
mysqldump -h your-db-host-here.db.ondigitalocean.com -P 25060 -u doadmin -p –ssl-ca=./certs/ca.crt –set-gtid-purged=OFF source-db > dump.sql
Note the set-git-purged flag, which is the important bit here. This will disable the SQL_LOG_BIN lines above and related from being generated in the dump.
Then you can import as expected. I’ve included the ssl flag for the mysql client here too, which took a bit to get sorted. Feels like the docs for DB management could use a little sprucing up. For completeness:
mysql -u doadmin -p -h your-db-host-here.db.ondigitalocean.com -P 25060 –ssl-ca=./certs/ca.crt target-db < dump.sql
Reply Report
partnersf511c532042fdf525d January 20, 2020
BAM!
This did the trick if you’re working on DO Cloud DB’s.
Thanks!
Reply Report
1
bobbyiliev September 6, 2019
Hello,
Could you share with us what exactly do you have on line 18 in your sql file that you are trying to import? It most likely can be adjusted so that the import could work without the need of super privileges.
Regarding your second question, you can not grant super privileges on the doadmin user as this is a managed service.
Regards,
Bobby
Reply Report
greenaro September 6, 2019
Hi,
The file is mysqldumb file of an existing db on AWS RDS. Here is line 18 of that …
SET @@SESSION.SQL_LOG_BIN= 0;
which would require atleast system variables privileges
Reply Report
bobbyiliev September 6, 2019
I would suggest to try and delete this line from the sql dump, it is not part of your data and would not affect the information that you have in the file. It’s main use is as a SESSION variable to prevent statements from the current session being written to the binary log. Once you remove this line the import should go through as normal.
You would not have the rights to change the value for the managed databases because if this variable is set to OFF it temporarily disables binary logging, so no changes that are made to the master would be replicated to the slave. This would essentially break any read replicas that you have.
Reply Report
KevinEaton September 23, 2019
To add some additional data, I did a mysqldump of a running cluster and tried to restore it. I had the same exact problem. The mysqldump command is inserting those admin-level calls into the dump file. Hopefully that helps.
