0

I tired to restore a MySQL database from sql file within the GCP storage,the step was I uploaded my database backup file to the GCP storage,then I imported this sql file to a specified database within the GCP MySQL instance,it took a while,the exception was occured which was

 Import error: exit status 1 ERROR 1227 (42000) at line 10871: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

then I tried to grant super privillege to the root or any else users,but it didn't work anyway,it didn't allow me to grant super privillege though,I have no idea how to resolve this problem,any helps would be much apperciated

Update (Jan 17th 2020): the problem I have resolved

I tried to find the line which occured excption

$ sed -n -e 10870p -e 10871p -e 10872p -e 10873p example.sql

and I found

/*!50013 DEFINER=root@localhost SQL SECURITY DEFINER */

then I used this command to removed the statment which has problem

cat example.sql | sed -e 's/DEFINER=root@localhost//g' > example-CLEANED.sql

then it done

1 Answer 1

1

The CURRENT_USER should have SUPER privileges, but the current DEFINER hasn't. Dump it again with mysqldump --skip-definer (would be the least effort). If this should not be an option, edit the dump and either remove the DEFINER, or replace all of it's occurrences with CURRENT_USER. Certain SET statements in the dump could eventually also cause the issue.

2
  • thank you for answering, I have resolved the problem,you're right,I should have added parameter which was --skip-definer Jan 17, 2020 at 9:03
  • @asjdfkasjkfkasjfksjfk Please accept the answer, since it appears to be correct. Jan 28, 2020 at 21:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .