Mysqldump couldn't excute show fields

Question

i am trying to create a mysql dump file but i am having major issues:

firstly i tried:

mysqldump --user=root --password=**** --host=localhost system_db > /var/www/system_db/site/resources/backup/file.sql

Here i got the message:

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

Then i tried to update the user:

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

And ran the statement again but without luck.

Then i tried:

mysqldump --single-transaction -u root -p system_*** > db.sql

This worked but now i get the following error:

mysqldump: Couldn't execute 'SHOW FIELDS FROM `v_user_stats`': SELECT command denied to user ''@'%' for column 'user_id' in table 'test_score' (1143)

Can anyone tell me whats going on? and how i can fix it?

Solution

Just use SHOW OPEN TABLES; if you find out In_use is greater than 0 then that table is locked ....

I think the command to unlock is UNLOCK TABLES

Comments

[VB_]

Doesn't work for me. The table is opened (in_use = 0), but still can't execute SHOW FIELDS. In the same time I can execute SHOW CREATE VIEW, and have ALL PRIVILEGES ON .