Question

Managed database (MySQL) - Error while importing database

Posted September 6, 2019 2.2k views
MySQL Databases

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
greenaro
By:
greenaro
edited by bobbyiliev
Add a comment

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
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.

        Reply Report
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
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help