5

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?

6

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.

  • GRANT CREATE VIEW ON *.* TO 'backup'@'%'; didn't work ;) – dragosrsupercool Dec 5 '14 at 9:30
  • Any disadvantage of using -f? – dragosrsupercool Dec 5 '14 at 9:31
  • Added warning to use of -f – RolandoMySQLDBA Dec 5 '14 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. – Michael - sqlbot Dec 8 '14 at 1:38
  • @RolandoMySQLDBA: Can I update the user of the VIEW? – dragosrsupercool Jan 5 '17 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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