35

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

  • 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
  • It work for me. {{ grant all on . to 'root'@'%' identified by 'password' with grant option; }} – Muhammad Azeem Sep 26 '16 at 11:24
32

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';
  • 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 '14 at 18:37
  • My bad. I had views with a wrong definer either. update mysql.proc approach indeed works. – x-yuri Apr 11 '14 at 21:17
  • Didn't work.... – カオナシ Jun 20 '14 at 3:27
  • 3
    This answer works for changing the definers of procedures and functions but does not change the definer of a View. – Anthony Geoghegan Sep 25 '17 at 11:04
35

Easier to use the --single-transaction switch:

mysqldump --single-transaction -u username -p db > db.sql
  • 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
  • 6
    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
  • i faced the same problem. worked --single-transaction. – user26135 Jul 18 '13 at 18:27
  • Not sure what it does, but worked though! Thanks – Sri Harsha Kappala Apr 22 '17 at 4:09
17

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

  • What if the user was 'root'@'%'? I simply deny such thing on a server we're responsible for. – Attila Fulop Sep 21 '16 at 10:04
  • @AttilaFulop Yes, security definitely should be a consideration, but I said this was the "quickest" solution, not the perfect one. Once the import is complete the user could be deleted to mitigate risks of having extra users. – ColinM Sep 22 '16 at 16:40
  • You're right in that. I just wanted to draw attention to this particular aspect as well. My workmates would have added 'root'@'%' and leave it there as long as Chuck Norris is alive ie. forever ;) – Attila Fulop Sep 23 '16 at 6:15
6

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.

  • This is the cleanest answer – Wolfe Jan 11 at 17:41

protected by Community Apr 12 '14 at 12:18

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

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.