Access denied you need at least one of the SUPER privilege s for this operation

Question

So I try to import sql file into rds  1G MEM  1 CPU   The sql file is like 1 4G  mysql -h xxxx rds amazonaws com -u user -ppass --max-allowed-packet 33554432 db  lt  db sql  It got stuck at   ERROR 1227  42000  at line 374  Access denied  you need  at least one of  the SUPER privilege s  for this operation   The actual sql content is      50003 CREATE      50017 DEFINER  another user   1 2 3 4       50003 TRIGGER  change log BINS  BEFORE INSERT ON  change log  FOR EACH ROW IF  NEW created at IS NULL OR NEW created at    00-00-00 00 00 00  OR NEW created at       THEN         SET NEW created at   NOW    END IF        another user is not existed in rds  so I do   GRANT ALL PRIVILEGES ON db   TO another user  localhost     Still no luck

User · Answer

For importing database file in  sql gz format  remove definer and import using below command  zcat path to db to import sql gz   sed -e  s DEFINER                        mysql -u user -p new db name    Earlier  export database in  sql gz format using below command   mysqldump -u user -p old db   gzip -9  gt  path to db exported sql gz  Import that exported database and removing definer using below command   zcat path to db exported sql gz   sed -e  s DEFINER                        mysql -u user -p new db

User · Answer

Answer may only be applicable to MacOS    When trying to import a  sql file into a docker container  I encountered the error message        Access denied  you need  at least one of  the SUPER privilege s  for   this operation   Then while trying some of the other suggestions  I received the below error on my MacOS  osx      sed  RE error  illegal byte sequence   Finally  the following command from this resource resolved my  Access Denied  issue   LC ALL C sed -i old  s  DEFINER                   g  fileName sql   So I could import into the docker database with   docker exec -i dockerContainerName mysql -uuser -ppassword table  lt  importFile sql   Hope this helps

User · Answer

Problem  You re trying to import data  using mysqldump file  to your mysql database  but it seems you don t have permission to perform that operation   Solution  Assuming you data is migrated  seeded and updated in your mysql database  take snapshot using mysqldump and export it to file  mysqldump -u  username  -p  databaseName  --set-gtid-purged OFF  gt   filename  sql      From mysql documentation       GTID -  A global transaction identifier  GTID  is a unique identifier created   and associated with each transaction committed on the server of origin    master   This identifier is unique not only to the server on which it   originated  but is unique across all servers in a given replication   setup  There is a 1-to-1 mapping between all transactions and all   GTIDs       --set-gtid-purged OFF SET   GLOBAL gtid purged is not added to the output  and SET     SESSION sql log bin 0 is not added to the output  For a server where   GTIDs are not in use  use this option or AUTO  Only use this option   for a server where GTIDs are in use if you are sure that the required   GTID set is already present in gtid purged on the target server and   should not be changed  or if you plan to identify and add any missing   GTIDs manually    Afterwards connect to your mysql with user root  give permissions   flush them  and verify that your user privileges were updated correctly   mysql -u root -p UPDATE mysql user SET Super Priv  Y  WHERE user  johnDoe  AND host      FLUSH PRIVILEGES  mysql gt  SHOW GRANTS FOR  johnDoe    ------------------------------------------------------------------    Grants for johnDoe                                                  ------------------------------------------------------------------    GRANT USAGE ON     TO  johnDoe                                       GRANT ALL PRIVILEGES ON  db1    TO  johnDoe                         ------------------------------------------------------------------    now reload the data and the operation should be permitted   mysql -h  host  -u  user  -p pass   db name   lt   mysql dump name  sql

User · Answer

Full Solution All the above solutions are fine  And here I m gonna combine all the solutions so that it should work for all the situations   Fixed DEFINER  For Linux and Mac sed -i old  s  DEFINER                   g  file sql  For Windows download atom or notepad    open your dump sql file with atom or notepad    press Ctrl F search the word DEFINER  and remove the line DEFINER admin    or may be little different for you  from everywhere and save the file  As for example before removing that line  CREATE DEFINER admin   PROCEDURE MyProcedure After removing that line  CREATE PROCEDURE MyProcedure  Remove the 3 lines Remove all these 3 lines from the dump file  You can use sed command or open the file in Atom editor and search for each line and then remove the line  Example  Open Dump2020 sql in Atom  Press ctrl F  search SET   SESSION SQL LOG BIN  0  remove that line   SET   SESSION SQL LOG BIN  0  SET   GLOBAL GTID PURGED    80000           SET   SESSION SQL LOG BIN    MYSQLDUMP TEMP LOG BIN    There an issue with your generated file You might face some issue if your generated dump sql file is not proper  But here  I m not gonna explain how to generate a dump file  But you can ask me

User · Answer

Statement      DEFINER username      is an issue in your backup dump   The solution that you can work around is to remove all the entry from sql dump file and import data from GCP console      cat DUMP FILE NAME sql    sed -e  s DEFINER  lt username gt     g      NEW-CLEANED-DUMP sql   Try importing new file NEW-CLEANED-DUMP sql

User · Answer

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

User · Answer

Just a MacOS extra update for hjpotter92 answer   To make sed recognize the pattern in MacOS  you ll have to add a backslash before the   sign  like this   sed -i old  s  DEFINER                   g  file sql

User · Answer

When you restore backup  Make sure to try with the same username for the old one and the new one

User · Answer

Need to set  quot on quot  server parameter  quot log bin trust function creators quot  on server side  This one you can easily find on left side blade if it is azure maria db

User · Answer

I commented all the lines start with SET in the   sql file and it worked

User · Answer

When we create a new RDS DB instance  the default master user is not the root user  But only gets certain privileges for that DB instance  This permission does not include SET permission  Now if your default master user tries to execute mysql SET commands  then you will face this error  Access denied  you need  at least one of  the SUPER or SYSTEM VARIABLES ADMIN privilege s  for this operation Solution 1 Comment out or remove these lines SET  MYSQLDUMP TEMP LOG BIN     SESSION SQL LOG BIN  SET   SESSION SQL LOG BIN  1  SET   GLOBAL GTID PURGED    80000            Solution 2 You can also ignore the errors by using the -f option to load the rest of the dump file  mysql -f  lt REPLACE DB NAME gt  -u  lt REPLACE DB USER gt  -h  lt DB HOST HERE gt  -p  lt  dumpfile sql

User · Answer

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    not sure about the DEFINER one

User · Answer

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

[mysql] Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Examples related to mysql

Examples related to amazon-web-services

Examples related to amazon-rds