Bug #70907 mysqldump: Couldn't execute 'show table status': SELECT command denied to user '
Submitted: 14 Nov 2013 11:57 Modified: 25 Dec 2017 22:44
Reporter: Rafal W Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:10.13 OS:MacOS (10.9)
Assigned to: MySQL Verification Team CPU Architecture:Any

[14 Nov 2013 11:57] Rafal W
Description:
I'm trying to backup my database by the following command:
$ mysqldump --no-autocommit --single-transaction --opt -Q my_db

But I've the following error (last 3 lines):
/*!40000 ALTER TABLE `uc_order_products` ENABLE KEYS */;
UNLOCK TABLES;
commit;
mysqldump: Couldn't execute 'show table status like 'uc\_order\_products\_pair\_vw'': SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products' (1143)
And it stops in half way of dumping the database.
Any suggestions?

My version:
mysqldump  Ver 10.13 Distrib 5.5.33, for osx10.6 (i386)

My configuration file:
$ cat ~/.my.cnf 
[client]
# The following password will be sent to all standard MySQL clients
user=root
password=root

[mysql]
no-auto-rehash
connect_timeout=2

How to repeat:
Repeat the same command.
$ mysqldump --no-autocommit --single-transaction --opt -Q my_db

The same with:
$ mysqldump --no-autocommit --single-transaction my_db
mysqldump: Couldn't execute 'show table status like 'uc\_order\_products\_pair\_vw'': SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products' (1143)

$ mysqldump --single-transaction my_db
mysqldump: Couldn't execute 'show table status like 'uc\_order\_products\_pair\_vw'': SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products' (1143)

Other errors when removing single-transaction:
$ mysqldump my_db > current.sql 
mysqldump: Got error: 1449: The user specified as a definer ('drupal_new'@'localhost') does not exist when using LOCK TABLES

Suggested fix:
Use -f
[14 Nov 2013 17:57] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Error message clearly explain why this happens:

The same with:
$ mysqldump --no-autocommit --single-transaction my_db
mysqldump: Couldn't execute 'show table status like 'uc\_order\_products\_pair\_vw'': SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products' (1143)

$ mysqldump --single-transaction my_db
mysqldump: Couldn't execute 'show table status like 'uc\_order\_products\_pair\_vw'': SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products' (1143)

You connect at ''@'localhost' instead of 'root'@'localhost'

Try passing mysqldup --defaults-file=~/.my.cnf [other options]. If this does not help, check part about MySQL access privilege system of the official MySQL reference manual.
[14 Nov 2013 22:24] Rafal W
I know what the message says, but it's not the case as I didn't specify that user to connect. I'm also sure that my ~/.my.cnf is correct, as it works for different commands.
If it's not a bug, how it's possible that 80% of the file was dumped correctly using the right credentials?
How it's possible that half way through mysqldump started using some other imaginable empty user whilst dumping directly from the database? And similar test on the other Linux machine it works fine.
I've seen this problem reported many on different sites.
[14 Nov 2013 22:41] Sveta Smirnova
Thank you for the feedback.

Please try to run mysqldump --defaults-file=~/.my.cnf [other options] and inform us about results.
[15 Nov 2013 9:47] Rafal W
The same:
$ mysqldump --defaults-file=~/.my.cnf --single-transaction my_db > current.sql
mysqldump: Couldn't execute 'show table status like 'uc\_order\_products\_pair\_vw'': SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products' (1143)

$ tail -n2 current.sql 
UNLOCK TABLES;
commit;

$ cat ~/.my.cnf
[client]
# The following password will be sent to all standard MySQL clients
user=root
password=root

Similar issue:
http://forums.mysql.com/read.php?10,599275,599275
[15 Nov 2013 9:49] Rafal W
Here is something very similar:
https://bugs.launchpad.net/chive/+bug/929905

```
When you've a VIEW and you use mysqldump, this one add a security command like that

/*!50013 DEFINER=`user_a`@`localhost` SQL SECURITY DEFINER */
```

Still I don't get why mysqldump is trying to use different user for dumping the db.
[15 Nov 2013 17:32] Sveta Smirnova
Thank you for the feedback.

Please connect with mysql command line client, again, using ~/.my.cnf and not specifying connection on the command line and send us output of SELECT USER(), CURRENT_USER()
[15 Nov 2013 17:43] Rafal W
$ mysql
mysql> SELECT USER() \G
*************************** 1. row ***************************
USER(): root@localhost

mysql> SELECT CURRENT_USER() \G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost

And it's the same as above for the following command:
$ mysql --defaults-file=~/.my.cnf
[15 Nov 2013 17:54] Sveta Smirnova
Thank you for the feedback.

Please try two more tests:

1. Run mysqldump --user=root --password=root and check if it solves the issue

2. Turn general query log to ON. Run mysqldump, so it reads ~/.my.cnf, then send us content of the general query log.
[19 Nov 2013 14:07] Rafal W
$ mysqldump --user=root --password=root --single-transaction drupal > current.sql
mysqldump: Couldn't execute 'show table status like 'uc\_order\_products\_pair\_vw'': SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products' (1143)

Last lines from mysql.log:
3 Query	use `drupal`
3 Query	select @@collation_database
3 Query	SHOW TRIGGERS LIKE 'uc\_order\_line\_items'
3 Query	SET SESSION character_set_results = 'utf8'
3 Query	show table status like 'uc\_order\_log'
3 Query	SET OPTION SQL_QUOTE_SHOW_CREATE=1
3 Query	SET SESSION character_set_results = 'binary'
3 Query	show create table `uc_order_log`
3 Query	SET SESSION character_set_results = 'utf8'
3 Query	show fields from `uc_order_log`
3 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `uc_order_log`
3 Query	SET SESSION character_set_results = 'binary'
3 Query	use `drupal`
3 Query	select @@collation_database
3 Query	SHOW TRIGGERS LIKE 'uc\_order\_log'
3 Query	SET SESSION character_set_results = 'utf8'
3 Query	show table status like 'uc\_order\_products'
3 Query	SET OPTION SQL_QUOTE_SHOW_CREATE=1
3 Query	SET SESSION character_set_results = 'binary'
3 Query	show create table `uc_order_products`
3 Query	SET SESSION character_set_results = 'utf8'
3 Query	show fields from `uc_order_products`
3 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `uc_order_products`
3 Query	SET SESSION character_set_results = 'binary'
3 Query	use `drupal`
3 Query	select @@collation_database
3 Query	SHOW TRIGGERS LIKE 'uc\_order\_products'
3 Query	SET SESSION character_set_results = 'utf8'
3 Query	show table status like 'uc\_order\_products\_pair\_vw'
3 Quit
[19 Nov 2013 18:41] Sveta Smirnova
Thank you for the feedback.

Please grep your general query log for row Connect: it should be something like "3 Connect   root@localhost on" and I want to check if it was changed during mysqldump run.

Please also check:

1. If you don't run MySQL server with option --skip-grant-tables
2. If you have column privileges on table 'uc\_order\_products\_pair\_vw':

SELECT * FROM mysql.columns_priv

3. If you can run show table status like 'uc\_order\_products\_pair\_vw' using MySQL command line client with same credentials.
[25 Nov 2013 13:07] Rafal W
The only connect credentials are for root@localhost when this error occurs:
131125 12:57:56	    2 Connect	root@localhost on 
131125 12:58:06	    3 Connect	root@localhost on 

1. I don't run mysql with --skip-grant-tables
My full cmd args are:
/Applications/MAMP/Library/bin/mysqld --defaults-file=/Applications/MAMP/tmp/mysql/my.cnf --basedir=/Applications/MAMP/Library --datadir=/Library/Application Support/appsolute/MAMP PRO/db/mysql --plugin-dir=/Applications/MAMP/Library/lib/plugin --tmpdir=/Applications/MAMP/tmp/mysql/tmpdir --log-error=/Applications/MAMP/logs/mysql_error_log.err --pid-file=/Applications/MAMP/tmp/mysql/mysql.pid --socket=/Applications/MAMP/tmp/mysql/mysql.sock --port=3306
/Applications/MAMP/Library/bin/mysqld_safe --defaults-file=/Applications/MAMP/tmp/mysql/my.cnf --port=3306 --socket=/Applications/MAMP/tmp/mysql/mysql.sock --user=kenorb --pid-file=/Applications/MAMP/tmp/mysql/mysql.pid --log-error=/Applications/MAMP/logs/mysql_error_log.err --tmpdir=/Applications/MAMP/tmp/mysql/tmpdir --datadir=/Library/Application Support/appsolute/MAMP PRO/db/mysql
2. I don't have anything in mysql.columns_priv, it's empty.

3.

mysql> SELECT USER() \G
*************************** 1. row ***************************
USER(): root@localhost
1 row in set (0.00 sec)

mysql> SELECT CURRENT_USER() \G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost
1 row in set (0.00 sec)

mysql> show table status like 'uc\_order\_products\_pair\_vw';
ERROR 1143 (42000): SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products'
mysql> show table status like 'uc_order_products_pair_vw';
ERROR 1143 (42000): SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products'
mysql> DESC uc_order_products_pair_vw;
ERROR 1143 (42000): SELECT command denied to user ''@'localhost' for column 'nid' in table 'uc_order_products'
mysql> show tables like 'uc_order_products_pair_vw';
+-----------------------------------------------+
| Tables_in_opsview (uc_order_products_pair_vw) |
+-----------------------------------------------+
| uc_order_products_pair_vw                     |
+-----------------------------------------------+
1 row in set (0.00 sec)

Also I did the check via mysqlcheck, but the tables looks ok.
[25 Nov 2013 17:25] Sveta Smirnova
Thank you for the feedback.

So this is not mysqldump only issue. Did you upgrade recently? Have you run mysql_upgrade after it?

Please also send output of select user, host from mysql.user and full error log file.
[26 Dec 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 May 2014 6:41] Alexander Shalin
I have the same problem. What to do? Error log is clear.

It was revealed this morning, the database had worked from 2007 till now.
[14 May 2014 5:37] Alexander Shalin
Grants for that user account:

GRANT USAGE ON *.* TO 'some_user'@'%' IDENTIFIED BY PASSWORD 'HashBlaBlaBlah'

GRANT ALL PRIVILEGES ON `cutedb`.* TO 'some_user'@'%'

Server version: 5.6.17-log MySQL Community Server (GPL)
OS: CentOS 6.5
[11 Jul 2014 17:00] Sveta Smirnova
Alexander,

thank you for the feedback. Please provide answers on questions from comment "[19 Nov 2013 18:41] Sveta Smirnova".
[12 Aug 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 Oct 2015 9:25] Julian Ladisch
Complete test case:

CREATE USER mysqluser1;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqluser1;
CREATE TABLE t1 (i INT);
CREATE DEFINER=mysqluser1@localhost SQL SECURITY DEFINER
  VIEW v1 AS (select i from t1 group by i);

When invoking mysqldump with root privileges by doing
mysqldump -u root -p test
the dump stops leaving an incomplete dump with this error message:
mysqldump: Couldn't execute 'show table status like 'v1'': SELECT command denied to user 'mysqluser1'@'localhost' for column 'i' in table 't1' (1143)

This breaks backup skripts.  It should dump v1 because root has privileges to successfully run "SHOW CREATE VIEW v1;".

Version: mysqldump  Ver 10.13 Distrib 5.6.19, for debian-linux-gnu (x86_64)
[25 Nov 2017 22:44] MySQL Verification Team
Please try with latest version. Thanks.
[26 Dec 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[1 Mar 2019 7:54] Valeriy Kravchuk
At least this kind of message:

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

is really easy to reproduce. Create a user with all privileges on test database that has one table, t:

mysql> create user u2@localhost identified by 'u2';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on test.* to u2@localhost;
Query OK, 0 rows affected (0.00 sec)

Then connect as this user and create a view:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

mysql> create view v1 as select * from t;
Query OK, 0 rows affected (0.04 sec)

mysql> show create view v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`u2`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t`.`id` AS `id`,`t`.`c1` AS `c1`,`t`.`c2` AS `c2` from `t`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

Then drop the user who is a definer of the view:

[openxs@fc23 p5.7]$ bin/mysql -uroot --host=127.0.0.1 --port=3308 -e'drop user u2@localhost';

Now you can dump the database when --single-transaction is added:

[openxs@fc23 p5.7]$ bin/mysqldump -uroot --host=127.0.0.1 --port=3308 --events --triggers --routines --single-transaction test > /tmp/test.sql

but get the error without it:

[openxs@fc23 p5.7]$ bin/mysqldump -uroot --host=127.0.0.1 --port=3308 --events --triggers --routines test > /tmp/test.sql
mysqldump: Got error: 1449: The user specified as a definer ('u2'@'localhost') does not exist when using LOCK TABLES

This error message is widely discussed by the users:

https://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-defi...

I think we should try to be more specific in the message (if possible), to refer to the view or trigger or whatever may have wrong definer, and the --single-transaction "workaround" should be described in the manual:

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

as a note, somewhere.