0

I have made a new AWS RDS MySQL instance and I want to import some existing .sql data generated from an existing database using MySQL Workbench's export tool.

I connected to the database using the "admin" master user, then started the import.

However, I'm getting this error: ERROR 1227 (42000) at line 20: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

I tried granting SUPER to the master user, and also tried creating a new user with SUPER, but got this error: Error changing account admin@%%: Missing GRANT OPTION privilege, even though the master user "admin" does have the GRANT OPTION privilege.

How can I make this work?

Nathan Tew
  • 432
  • 1
  • 5
  • 21

2 Answers2

0

gyanendra giri was partially right, but his answer didn't help with MySQL Workbench. Here's how I solved it eventually:

Select Export to Self-Contained File, and make sure that Create Dump in a Single Transaction is not selected.

Then, click on Advanced Options...:

enter image description here

Set set-gtid-purged to "OFF":

enter image description here

Nathan Tew
  • 432
  • 1
  • 5
  • 21
-1

You may have a definer line for a user which doesn't exist for current RDS database. You have something like /*!50017 DEFINER=`root`@`localhost`*/ at line 20. You may have to remove that part.

To remove:

sed -i -e 's/DEFINER=`root`@`localhost`//g' db_dump_name.sql

Or replace that user to an existing user. In your case may be admin

To find and replace:

sed -i -e 's/DEFINER=`root`@`localhost`/DEFINER=`admin`@`%`/g' db_dump_name.sql

reference

Gyanendra Giri
  • 1
  • 3
  • your answer is for the command line utility mysqldump, I am asking about the desktop app MySQL Workbench... – Nathan Tew Aug 23 '23 at 23:41
  • I'm sorry, I may have understood it wrong. I've checked my .sql dump files, and they do not have a `DEFINER` in any of the files anywhere... – Nathan Tew Aug 23 '23 at 23:50
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 25 '23 at 11:08