Why mysqldump is returning with error 1143: Couldn't execute 'show table status like

Question

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@%                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'%' IDENTIFIED BY PASSWORD '*SOMETHING' |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

What am I doing wrong?

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

SUGGESTION #1

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.

SUGGESTION #2

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.