Mysql Error 1143
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and show view command denied to user mysqldump policies of this site About Us Learn more about Stack Overflow the company mysql select command denied to user ''@' ' for table Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users mysqldump skip views Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a mysql change view definer minute: Sign up mysql forgets who is logged in: command denied to user ''@'%' up vote 13 down vote favorite 4 Running show grants; indicates that I am logged in as a user with all privileges on a database. Running show table status; results in an error. And the error does not show the username I am logged in as! It's
Select Command Denied To User 'root'@'localhost' For Table 'user'
as if, for this command, mysql forgets who I am. Other select statements work fine. Can anyone explain this? How to fix? Thanks. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.13-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show grants; +---------------------------------------------------------------------------------------------------------------------+ | Grants for php@localhost | +---------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'php'@'localhost' IDENTIFIED BY PASSWORD '*8F5FF90079BC601F8EA7C148475658E65A0C029D' | | GRANT ALL PRIVILEGES ON `sunflower_work`.* TO 'php'@'localhost' | | GRANT ALL PRIVILEGES ON `news_demo`.* TO 'php'@'localhost' | | GRANT ALL PRIVILEGES ON `news_base`.* TO 'php'@'localhost' | +---------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> show table status from sunflower_work; ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'uid' in table 'users' mysql> update... as suggested by Tomalak, I deleted the user and recreated with fuller p
2013 19:50 Reporter: Wagner Bianchi Email Updates: Status: Not a
Select Command Denied To User ''@'localhost' For Table 'user'
Bug Impact on me: None Category:MySQL Server: General Severity:S1 (Critical) mysql show views Version:5.5.18 OS:Linux Assigned to: View Add Comment Files Developer Edit Submission View Progress Log mysql grant all privileges Contributions [2 Dec 2011 0:59] Wagner Bianchi Description: I am having problems with MySQL 5.5.18. Actually, this problem starts when customer claimed that databases http://stackoverflow.com/questions/6527599/mysql-forgets-who-is-logged-in-command-denied-to-user tables weren't listed on MySQL Administrator and HeidSQL. The connection with the database was OK but the tables weren't not listed. Examining the problem stated by the HeidSQL and after, consulting the same commands on the command line, I found this bug below: [root@simps ~]# mysql -u https://bugs.mysql.com/bug.php?id=63527 root -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 224 Server version: 5.5.18-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select version(); +------------+ | version() | +------------+ | 5.5.18-log | +------------+ 1 row in set (0.00 sec) mysql> select user(), current_user; +----------------+--------------+ | user() | current_user | +----------------+--------------+ | root@localhost | root@% | +----------------+--------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS; +--------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@% | +--------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*FE7F63AB2417CBEF98841D666AAEE0BE48C22A83' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------+ mysql> sho
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta http://dba.stackexchange.com/questions/63320/problem-with-mysqldump-and-view Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database http://www.faqssys.info/why-mysqldump-is-returning-with-error-1143-couldnt-execute-show-table-status-like-3/ Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database command denied skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Problem with mysqldump and view up vote 4 down vote favorite 1 When using mysqldump to backup MySQL, command denied to I got following error. mysqldump --all-databases --routines >> all.sql mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied to user 'tungbt'@'192.168.12.197' for column 'id' in table 'hdcn_hd' (1143) hdkien is a view CREATE ALGORITHM=UNDEFINED DEFINER=`tungbt`@`192.168.12.197` SQL SECURITY DEFINER VIEW `hdcn`.`hdkien` AS (...striped...) User tungbt@192.168.12.197 has already had the privilege to select on table hdcn_hd and I can select from view hdkien without problem. mysql> select * from hdkien limit 1; +------+-----------+ | id | shd | +------+-----------+ | 876 | ADFADFA1 | +------+-----------+ More info: MySQL version: mysql-community-server-5.5.37-4.el6.x86_64 OS: CentOS 6.5 Why I got the error when running mysqldump and how can I resolve it? Update 1 (2014/04/17) I run mysqldump with user 'root'@'localhost'. mysql> show grants for 'root'@'localhost'; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '...striped...' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ The user 'tungbt'@'192.168.12.197' is the definer of view hdcn.hdkien (and the SQL SECURITY is DEFINER. +-----------------------
-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? mysql, mysql-5.5, mysqldump written by admin Post navigation T-SQL View — How to 'pre-fetch' schema using scalar function, then populate using table queryHow to find p value for trend in a Proportional Hazards Cox regression model? One response to Why mysqldump is returning with error 1143: Couldn't execute 'show table status like answered Dec 4 '14 at 21:40 says: February 15, 2015 at 4:10 am 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