44

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

Improve this question
2
  • 4
    Do you have Views? Tables don't have definers I'm sure... Also see dba.stackexchange.com/q/4129/630
    – gbn
    Commented Dec 16, 2011 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
    Commented Dec 16, 2011 at 12:58

4 Answers 4

Reset to default
39

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';
Improve this answer
1
  • 7
    This answer works for changing the definers of procedures and functions but does not change the definer of a View.
    – Anthony Geoghegan
    Commented Sep 25, 2017 at 11:04
37

Easier to use the --single-transaction switch:

mysqldump --single-transaction -u username -p db > db.sql
Improve this answer
2
  • 1
    Sorry, how this addresses the problem desribed in the original question? Looks like you answered the wrong question...
    – András Váczi
    Commented Nov 22, 2012 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
    Commented Nov 23, 2012 at 3:41
18

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

Improve this answer
3
  • What if the user was 'root'@'%'? I simply deny such thing on a server we're responsible for.
    – Attila Fulop
    Commented Sep 21, 2016 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
    Commented Sep 22, 2016 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
    Commented Sep 23, 2016 at 6:15
7

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.

Improve this answer
0

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