How do I change the DEFINER of a VIEW in Mysql?

When I run mysqldump, I get an error:

mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES

This makes sense because foobar is a legacy machine that no longer exists.

How do I change the definer of all my tables to 'root'@'localhost'?

What I think is that the database you are trying to dump contains procedures/methods that were defined by a user while logged in as root@'foobar'.

Now the solution is that you have to replace the definer's for that procedures/methods

then you can generate the dump without the error.

you can do this like ..

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='root@foobar'

Be careful, because this will change all the definers for all databases.

Try it....!

UPDATE on 9th Feb 2012

As I saw the link given by @gbn which is an answer given by @Rolando that can also be the Case. Please visit the link

EDIT by @RolandoMySQLDBA 2011-12-16 11:20 EDT

While risky, this answer is good. Just to clarify: You can specify the database in your query like this:

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='root@foobar' AND db='whateverdbyouwant';

StackExchange Database Administrators Q#9249, answer score: 39