Question

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


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

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.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

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

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