Bug #71279 Misleading error message for ANALYZE against PERFORMANCE_SCHEMA tables
Submitted: 2 Jan 2014 17:29 Modified: 2 Jan 2014 18:29
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any
Tags: analyze, performance_schema

[2 Jan 2014 17:29] Valeriy Kravchuk
Description:
While working on Bug #71278 I tried to check if there is a way to force re-estimation of the number of rows in PERFORMANCE_SCHEMA.THREADS table, to make it more "realistic":

mysql> analyze table performance_schema.threads;
ERROR 1142 (42000): SELECT, INSERT command denied to user 'root'@'localhost' for
 table 'threads'

Same happens with other tables:

mysql> analyze table performance_schema.setup_instruments;
ERROR 1142 (42000): SELECT, INSERT command denied to user 'root'@'localhost' for
 table 'setup_instruments'

so it seems ANALYZE is just not supported for PERFORMANCE_SCHEMA tables. I'd expect error message to say this.

How to repeat:
Run:

analyze table performance_schema.threads;

and try to find out how error message is related to ANALYZE or SELECT privilege (that user is surely able to SELECT from the table).

Suggested fix:
Make error message more useful. Alternatively, implement ANALYZE for PERFORMANCE_SCHEMA tables :)
[2 Jan 2014 18:29] MySQL Verification Team
Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[3 Jan 2014 11:23] Peter Laursen
See also http://bugs.mysql.com/bug.php?id=70928
[8 Jan 2014 9:32] Marc ALFF
Per the documentation:

http://dev.mysql.com/doc/refman/5.6/en/analyze-table.html

ANALYSE TABLE:
- is supported for only a few engines, and the performance schema is not part of the list
- needs both the SELECT and INSERT privilege to the table.

The implementation of the ANALYSE TABLE statement happens to check privileges first, so he error message seen is about the missing INSERT privilege for the table used (these tables are read only in the performance schema, so no user can possibly be granted the INSERT privilege).

I believe the same error message can be seen with tables from a supported engine, when the user does not have the INSERT privilege.

Accordingly, changing this bug category to privileges:
the error message is listing all the privileges, creating confusion, instead of listing only the privileges that are missing.