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: | |
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
[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.