0

I do understand that this has been asked before, however, I've had trouble figuring out how to fix this issue for MySQL 8.0.22 (all the suggested fixes do not seem to work as the mysql.proc table does not exist in MySQL 8.0.22).

I am able to do any CRUD operations with my user but as soon as I run:

mysql -u myuser -ppassword < data.sql

I get

ERROR 1449 (HY000) at line 19: The user specified as a definer ('root'@'%') does not exist

I have checked that I have been granted all permissions. How can I change the DEFINER to 'myuser'@'localhost' rather than 'root'@'%'?

Matthew Knill
  • 252
  • 2
  • 7
  • Neither ALTER FUNCTION nor ALTER PROCEDURE allows to change the definer. So simply edit your .SQL file and replace the definer account with one you need, then import. – Akina Dec 09 '20 at 07:40
  • The SQL file that I am using has no definer in it, it only has some insert statements. Are you saying I should add the definer? – Matthew Knill Dec 09 '20 at 07:46
  • If so then there exists some compound statement (function or trigger) defined by removed account. Re-create them. Recommendation: create mentioned account, get compound ststements codes, create separate account with enough privileges but without login privilege and recreate these statements using this account. – Akina Dec 09 '20 at 08:08
  • Ahh yep there was some stray tiggers that didn't allow the prevented some of my inserts - once fixed all seems good. Cheers! – Matthew Knill Dec 09 '20 at 10:24

1 Answers1

0
 GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
Nanhe Kumar
  • 15,498
  • 5
  • 79
  • 71