Friday, July 23, 2010

MySQL : "The user specified as a definer ('root'@'%') does not exist"

If you've found following error while using mysql database:
The user specified as a definer ('root'@'%') does not exist
Then you can solve it by using following :
grant all on *.* to 'root'@'%' identified by 'password' with grant option;

58 comments:

Anonymous said...

Would be nice to have something a bit more specific instead of wilcards (*.*).

Asking user to enter host or database name should be an acceptable solution without compromising security.

Anonymous said...

This solution helped me.
Thanks dear.

Anonymous said...

Fixed my issue, thanks man.

Anonymous said...

WOW!!!
Your statement has solved my problem.
Thank you so much!!!

Anonymous said...

error :The user specified as a definer ('root'@'%') does not exist

when i execute this query
grant all on *.* to 'root'@'%' identified by 'password' with grant option;

again coming this error:
Can't find any matching row in the user table

Anonymous said...

Many thanks...

Anonymous said...

I want to update the definer to another user instead of creating the user its looking for... How do I do that?

Anonymous said...

Solved my problem, thanks!

Anonymous said...

Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you. and, yes, i forgot, THANK YOU!

Anonymous said...

great comments. Thanks a lot

Anonymous said...

Thanks a lot!
Laura

Franklin said...

Anyone know why this error is coming ?

nazaq said...

Hatur nuhun pisan lur,
hade pisan euy solusina teh....

ESTE ES MI BLOG said...

Perfect! this worked great!

Anonymous said...

It Helped me

Anonymous said...

Look at all these idiots saying, "Thanks, this really helped me" without realizing you just set your mysql root password to 'password' and didn't really solve the original problem, just masked over it.

Anonymous said...

Thank you!

Anonymous said...

are people really running this?

Anonymous said...

Solved my problem as well. Thank you!

@Anonymous - July 19, 2011 4:33 AM: I did not set my password to 'password', It actually solved my problem.

Anonymous said...

Thank you!!!

i didn't expect that command really work!

Richard Ayotte said...

Thanks, that worked.

Anonymous said...

great it worked :)

Anonymous said...

Thanks a lot, Sir.

Anonymous said...

Thanks a lot..
this worked great!

Anonymous said...

Thanks dear.

Anonymous said...

1. "Can't find any matching row in the user table"

Then you are running with a sql_mode including NO_AUTO_CREATE_USER. So you need to create the user first before creating the GRANT:

CREATE USER 'root'@'%' IDENTIFIED BY 'MySecretPassword';
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

However, I do NOT recommend ever creating 'root'@'%' as the above outlines and the original article writer suggests. It allows anyone an opportunity to login as root from anywhere. It's better to only have 'root'@'localhost', forcing you to remote in the operating system first, and launching a MySQL interactive shell from there.

2. I want to update the definer to another user instead of creating the user its looking for... How do I do that?

Example: If the routine/function/SP is named fooSP in database barDB and you want it to run with the privileges of user 'johndoe'@'localhost':

UPDATE mysql.proc SET definer = 'johndoe@localhost' WHERE name = 'fooSP' AND db = 'barDB';

Anonymous said...

You just saved my ass! Thank you very very much

Anonymous said...

It solved my issue. Thanks!!!

Anonymous said...

love u! ;}

Anonymous said...

Puede ser debido a una vista que se importó de otra base de datos y tiene un definer activo que no existe en esta base de datos.
La solución es exportar la vista, editarla borrando el definer y volver a importarla.

Anonymous said...

GRACIAS.

Anonymous said...

Tak!!

nanangdz said...

Thanks it solve my problem

Anonymous said...

This is grate !!! Perfect ;)

Anonymous said...

Thanks!

Sergio Ruiz said...

tHANKS! Just what I was looking for, it works for me! ;D

Anonymous said...

Duh!!!!!!!! IDIOTS
You have now exposed your db to everone.
GREAT!!!

saravanan said...

great ..

Anonymous said...

Sorted my issue...thanks geniuses

tryfatur said...

It works dude ! :D

Parveen Kumar said...

It is working for me..!
Thanks

ToT said...

Wow...Thanks a lot. It helped me

Anonymous said...

Thanks :) you saved my ass

Hekmat said...

very helpfull man ,, thank you

Rigoberto González Díaz said...

Thanks :D!!! this was helpful

Akash Devaraju said...

Awesome :)...

Webmaster said...

THANK YOUUUUU
THANK YOUUUUU
THANK YOUUUUU
THANK YOUUUUU
THANK YOUUUUU

Anonymous said...

Thank you :) it worked for me

Abhishek Anand said...

Thanks, It worked....

sani said...

thanks for sharing dude...
it saves my time :D

Ivo said...

just works for me with

grant all on *.* to 'root'@'%' identified by 'password' with grant option;

thanks!! :)

Bejo Juga Hebat said...

Thanks 1000X

elkhairi said...

Solved my problem, thanks!

Anonymous said...

not working for me pls help me guys...

Vitor Mello said...
This comment has been removed by the author.
cheranilango said...

works like charm

Anonymous said...

thnkx :)

Andrew said...

Thank you so much!