I ran the following command to create a mysqldump

mysqldump -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz

I got following error after 60% (estimating checking size of the zip file) of the dump created:

mysqldump: Couldn't execute 'show table status like 'customer\_cohort\_paid'': SELECT command denied to user ''@'%' for column 'customer_id' in table 'sales_order' (1143)

It says of about permission issues for a blank user user ''@'%'. Moreover, grants for backup user is as followed:

mysql> show grants for 'backup'@'%';
| Grants for backup@%                                                                                                                            |
1 row in set (0.00 sec)

What am I doing wrong?

1 Answer 1


There is a bug report on this: mysqldump: Couldn't execute 'show table status': SELECT command denied to user. Surprisingly, it's not a bug at all. here is why:

customer_cohort_paid might be a view. Whatever user created the view is not listed anymore in mysql.user. What this does is make such a view incapable of being dumped.

You could do one of two(2) things


Force the dump with -f

mysqldump -f -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz

If you can force the dump to make views appear in the dump file, you may want to edit it (See my old post Modify DEFINER on Many Views or this other one How do I change the DEFINER of a VIEW in Mysql?)

WARNING: If any one extended INSERT command has a syntax problem or improperly escaped or encapsulated data, the hundreds of rows in that one INSERT may not be loaded.


Find out the original user who made the view, and recreate that user

Run this query : select * from mysql.tables_priv;

This will show all table-level and column-level grants. Look at the first three columns and figure out what GRANT command to make that user again.

  • GRANT CREATE VIEW ON *.* TO 'backup'@'%'; didn't work ;) Commented Dec 5, 2014 at 9:30
  • Any disadvantage of using -f? Commented Dec 5, 2014 at 9:31
  • Added warning to use of -f Commented Dec 5, 2014 at 16:52
  • 2
    Use -f with extreme caution, because it masks errors, by definition, leaving you with an incomplete backup. Last I checked, it incorrectly leaves the exit status of the dump process set to 0, even if errors occurred. Commented Dec 8, 2014 at 1:38
  • @RolandoMySQLDBA: Can I update the user of the VIEW? Commented Jan 5, 2017 at 5:08

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.