When using mysqldump to backup MySQL, I got following error.

mysqldump --all-databases --routines >> all.sql
mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied to user 'tungbt'@'' for column 'id' in table 'hdcn_hd' (1143)

hdkien is a view


User tungbt@ has already had the privilege to select on table hdcn_hd and I can select from view hdkien without problem.

mysql> select * from hdkien limit 1;
| id   | shd       |
|  876 | ADFADFA1  |

More info:

  • MySQL version: mysql-community-server-5.5.37-4.el6.x86_64
  • OS: CentOS 6.5

Why I got the error when running mysqldump and how can I resolve it?

Update 1 (2014/04/17)

I run mysqldump with user 'root'@'localhost'.

mysql> show grants for 'root'@'localhost';
| Grants for root@localhost                                                                                                              |
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '...striped...' WITH GRANT OPTION                             |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |

The user 'tungbt'@'' is the definer of view hdcn.hdkien (and the SQL SECURITY is DEFINER.

| Grants for [email protected]                                                                                   |
| GRANT USAGE ON *.* TO 'tungbt'@'' IDENTIFIED BY PASSWORD '...striped...'                             |
| GRANT ALL PRIVILEGES ON `hdcn`.* TO 'tungbt'@''                                                      |

Update 2

| root@localhost | root@localhost |

Update 3

mysql> SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
| MySQLGrantsCount | MySQLVersion |
|               42 | 5.5.37-log   |
  • 1
    Does tungbt@ still exist?
    – dwjv
    Apr 16, 2014 at 11:17
  • @dwjv yes, for sure.
    – favadi
    Apr 16, 2014 at 15:46
  • I was able to dump my views after I changed their 'definer' to the current user, "dev@%" (with suitable privileges, of course).
    – marklark
    Jul 31, 2014 at 20:27

4 Answers 4


You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema?

In that post I show these minimum privileges for a mysqldump

You should run this command:

SHOW GRANTS FOR [email protected];

If SHOW VIEW is not there, that's the reason why.

UPDATE 2014-04-16 23:06 EDT

When you did this

mysqldump --all-databases --routines >> all.sql

I see you did not specify the user and password. That being the case, you were not logged in as root@localhost. You will have to be explicit in specifying the root user

mysqldump -uroot -p --all-databases --routines >> all.sql

You will see the password prompt. Enter the root@localhost password and you are off and running.

You could also specify the password too

mysqldump -uroot -ppassword --all-databases --routines >> all.sql

Give it a Try !!!


If you are using .~/my.cnf and still getting an error, you might be hitting this situation in Bug #70907 mysqldump: Couldn't execute 'show table status': SELECT command denied to user '

If the config file is .~/my.cnf is really /root/.my.cnf, perhaps you are not logged in as Linux root. You may have to run sudo.

Please run this command


If you do not see root@localhost twice, then you are not authenticating correctly.

In .my.cnf you need to make sure that user and password are under the [client] section


not under the [mysql] section.

UPDATE 2014-04-17 13:53 EDT

I cannot help look at that bug report and wonder the following: Since you have DEFINER=tungbt@, it is possible that root@localhost is behaving like tungbt@ ? I say this because according to the MySQL Documentation on CREATE VIEW: At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition.

You could change the definer of the view to root@localhost and try the mysqldump again

  • See my update. The user root@localhost and [email protected] both have had the SHOW VIEW privilege already.
    – favadi
    Apr 17, 2014 at 2:21
  • That's not the case. I specify my username and password in ~/.my.cnf. I try with -u and -p for mysqldump as you said above and have the same error.
    – favadi
    Apr 17, 2014 at 3:50
  • 1
    Added wild suggestions to help you look closer. Apr 17, 2014 at 4:11
  • See my update. I surely run mysqldump with root@localhost. I have the same problem as the reporter in bug:70907. I have upgraded MySQL from 5.1 to 5.5 one month ago. How to know if I have run mysql_upgrade properly or not?
    – favadi
    Apr 17, 2014 at 4:33
  • 1
    Ouch, you ran mysql_upgrade. Please run this query SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; What number and version does it output ??? Apr 17, 2014 at 4:58

I had a similar problem with not being able to do a mysqldump on my view as root:

mysqldump: Couldn't execute 'show create table `v_view01`': View 'my_db.v_view01' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

In my case, it was because my underlying schema changed, so the view that relied on it was no longer valid. Even though, I was executing the dump as root, it still says "or invoker of view lacks rights...". The solution was simple:

drop view v_view01

Since the view was already outdated, I just dropped it, then the mysqldump proceeded like normal.


Mine couldn't show create view on a view that didn't exist - so job just quit. So I did these two things:

create or replace view v_view01 as select * from any_table;
drop view v_view01;

Bigger problem is mysqldump defaults to stop on an error - horrible idea!!!

If you need all other good tables backed up then add option --force and options like these...

mysqldump -u root -p${MP} --all-databases --lock-tables --debug-info --comments --dump-date --force --events --routines --add-drop-table --add-locks --log-error=/somewhere/mysqldump.err >all_db.`date "+%Y%m%d%H%M"`.sql

In my case the issue is that my view was corrupted/broken as one of the object (table) it used didn't exist anymore.

The solution was to drop the view and recreate it with the correct SQL syntax.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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