Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'?

share|improve this question
4  
Do you have Views? Tables don't have definers I'm sure... Also see dba.stackexchange.com/q/4129/630 –  gbn Dec 16 '11 at 6:18
1  
@gbn +1 you were right - they were views - thx for the link, but I couldn't get it working quite right. However, I was able to modify the views in SQLyog so I could do the dump. –  kfmfe04 Dec 16 '11 at 12:58

4 Answers 4

up vote 13 down vote accepted

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';
share|improve this answer
1  
The link above shows how to do it with ALTER statements which may be less risky... Also, please check my update for the definers in your UPDATE –  gbn Dec 16 '11 at 8:29
    
UPDATE changed the table, but mysqldump doesn't see it: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES. Even after restarting mysql. I'm running mysql-5.5.31. –  x-yuri Apr 11 at 18:37
    
My bad. I had views with a wrong definer either. update mysql.proc approach indeed works. –  x-yuri Apr 11 at 21:17
    
Didn't work.... –  Unnamed Jun 20 at 3:27

Easier to use the --single-transaction switch:

mysqldump --single-transaction -u username -p db > db.sql
share|improve this answer
1  
Sorry, how this addresses the problem desribed in the original question? Looks like you answered the wrong question... –  dezso Nov 22 '12 at 20:06
2  
The structure of the answer is wrong, because --single-transaction needs to be prior to the database name, but the answer is actually correct. Specifying this option works around the issue by changing the locking behavior of mysqldump, so the error in the original question no longer occurs. –  Michael - sqlbot Nov 23 '12 at 3:41
1  
Thank you! That solved my problem very simply. –  Xyon Feb 18 '13 at 11:29
    
i faced the same problem. worked --single-transaction. –  user26135 Jul 18 '13 at 18:27

The quickest solution would just be to re-create the definer so it does exist, as long as it doesn't create any conflicts with existing users.

CREATE USER 'root'@'foobar';

share|improve this answer

Export all the views of the database <DB>:

mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

or:

mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

Edit views.sql and recreate them:

cat views.sql | mysql <DB>

Specify -u and -p switches if necessary.

share|improve this answer

protected by Community Apr 12 at 12:18

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.