Question 237739 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – master user of AWS RDS does not have privileges to import data, nor to grant the required privileges

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search