Bug #71280 Misleading error message for ANALYZE against INFORMATION_SCHEMA tables
Submitted: 2 Jan 2014 18:21 Modified: 3 Jan 2014 8:24
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.6.14, 5.6.15, 5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: analyze, information_schema

[2 Jan 2014 18:21] Valeriy Kravchuk
Description:
Similar to Bug #71279, but this time ANALYZE against INFORMATION_SCHEMA table was used:

mysql> analyze table information_schema.processlist;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

I'd like to see what kind of access is denied for SUPER user and why. I can SELECT from this table:

mysql> select * from information_schema.processlist\G
*************************** 1. row ***************************
     ID: 2
   USER: root
   HOST: localhost:60315
     DB: test
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from information_schema.processlist
1 row in set (0.02 sec)

But surely number of rows there is far from reality (it's NULL):

mysql> explain select * from information_schema.processlist\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: processlist
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: NULL
1 row in set (0.01 sec)

So one can only imagine what kind of execution plan may be used with some multiple way join.

As a side note, I do not see anything about ANALYZE being NOT supported for INFORMATION_SCHEMA in the manual (http://dev.mysql.com/doc/refman/5.6/en/information-schema.html).

How to repeat:
analyze table information_schema.processlist;
select * from information_schema.processlist\G
explain select * from information_schema.processlist\G

Check error message for the first statement and number of rows estimated (NULL) for the last one.

Suggested fix:
Change error message to more specific and useful one.

Alternatively, implement engine independent statistics, so that DBA can estimate or set number of rows for this kind of tables.
[3 Jan 2014 8:24] MySQL Verification Team
Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh