MySQL Bugs: #63527: ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'xxxxxxx' in

Bug #63527	ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'xxxxxxx' in
Submitted:	2 Dec 2011 0:59	Modified:	16 Jan 2013 19:50
Reporter:	Wagner Bianchi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.5.18	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I am having problems with MySQL 5.5.18. Actually, this problem starts when customer claimed that databases 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 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> show table status from example_database;
ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'foo01' in table 'tblfoo'

It seems that MySQL has forgotten the current session user GRANTS.

How to repeat:
Install MySQL 5.5.18 using ".rpm" packages, restore or create some tables inside a new database and try those presented commands. The only databases seems to be with no problems is "mysql", "information_schema" and "performance_schema".

Maybe a "REPAIR TABLE mysql.tables_priv;" is required here? and maybe even a server restart

Peter
(not a MySQL person)

Hi Peter! Thanks for your reply. 

Actually, I didn't have the mysqlcheck's results I ran any more, but, I did it first thing after to see this error. I'll try to run REPAIR TABLE next time. For now, I got round this problem doing a downgrade of MySQL version and everything is OK. Thanks once again. 

We'll welcome and encourage all MySQL users who are having the same problem to help us to find the right solution to solve this problem. Happy MySQL'ing...

Thank you for the report.

> For now, I got round this problem doing a downgrade of MySQL version and everything is OK. Thanks once again. 

Had you run mysql_upgrade when did upgrade?

I didn't upgrade MySQL's version, I just installed the MySQL 5.5.18 using ".rpm" packages and I got that error. When I downgraded the version from 5.5.18 to 5.5.17, I formatted the machine first and after, I started a fresh MySQL installation.

Thank you for the feedback.

Do you have backup of mysql database made in version 5.5? I need output of select user, host from mysql.user

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".

Hi. I have the same problem with one of my databases. I am using Windows 7 and have installed Mysql 5.5.20.

This is what I did:

C:\Windows\System32>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.5.20 MySQL Community Server (GPL)
[blablabla...]

mysql> SHOW TABLE STATUS FROM `mydatabasename`;
ERROR 1143 (42000): SELECT command denied to user ''@'192.168.55.%' for column 'method' in table 'sales_flat_order_payment'

But I can do this:

mysql> SELECT entity_id, method FROM sales_flat_order_payment LIMIT 2;
+-----------+--------------+
| entity_id | method       |
+-----------+--------------+
|         1 | adyen_hpp    |
|         2 | adyen_hpp_cc |
+-----------+--------------+
2 rows in set (0.00 sec)

This is really strange.

Christian,

thank you for the feedback. Your issue looks like misconfiguration of user rights and not MySQL bug. Check output of functions USER() and CURRENT_USER(). Ask on public forums or open a SR at My Oracle Support if you have a subscription.

We are not allowed to help with usage errors here.

Hi Sveta.
I do not think that this is a misconfiguration. I have logged in as root user and I have never changed any permission of this user. Here's the output of the functions you told me to execute:

mysql> SHOW TABLE STATUS FROM `mydatabasename`;
ERROR 1143 (42000): SELECT command denied to user ''@'192.168.55.%' for column 'method' in table 'sales_flat_order_payment'
mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of an obvious bug. It sounds like you have an object defined in this particular schema (a view, for example) where the DEFINER is set to the anonymous user, and that user doesn't have the necessary privileges to execute and provide status information. 

I have no clear and working way to repeat the problem you are seeing. It will take a lot of back and forth to track this down, and this is not the proper forum for such help.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

If you can determine a repeatable test case, then please let us know and we'll be happy to try and verify it.

Thank you for your interest in MySQL!

Hi. Thanks for your answer, it was very helpful. When you mentioned that for example a view could result in this error. I analyzed the backup script that I have imported.

I could find some queries like this:
CREATE ALGORITHM=UNDEFINED DEFINER=`username`@`192.168.55.%` SQL SECURITY DEFINER VIEW `warenkorb_durchschnitt` AS select avg(`sfo`.`grand_total`) AS `Umsatz_AVG` from `sales_flat_order` `sfo` where ((`sfo`.`status` <> 'canceled') and (`sfo`.`status` <> 'closed'));

But this is very confusing.
1. Why was it possible to execute this query on my machine at all? Because I have no user with the username specified in the DEFINER clause. I would assume that MySQL would throw an error during the execution of the backup script.

2. The error message that I got:

>> mysql> SHOW TABLE STATUS FROM `mydatabasename`;
>> ERROR 1143 (42000): SELECT command denied to user ''@'192.168.55.%' for
>> column 'method' in table 'sales_flat_order_payment'

is very confusing and did not help me to find the reason why the query failed. It would be really nice if the message could be improved. Maybe something like "You are not allowed to see any information of the view 'xyz'".

> It sounds like you have an object defined in this particular schema (a view, for example) where the DEFINER is set to the anonymous user, and that user doesn't have the necessary privileges to execute and provide status information. 

Thank you for this explanation. It helped a lot, as the error message on itself is misleading.