Bug #90172 Inaccurate TYPE description in documentation for INNODB_SYS_INDEXES
Submitted: 22 Mar 2018 10:31 Modified: 8 Jun 2018 17:39
Reporter: Alexey Kopytov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6, 5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[22 Mar 2018 10:31] Alexey Kopytov
Description:
The manual provides inaccurate description for the TYPE column in the
INFORMATION_SCHEMA.INNODB_SYS_INDEXES table
(INFORMATION_SCHEMA.INNODB_INDEXES in 8.0):

"
A numeric identifier signifying the kind of index. 0 = Secondary
Index, 1 = Clustered Index, 2 = Unique Index, 3 = Primary Index, ...
"

The TYPE column values are read from the 'type' field of the
dict_index_t structure representing InnoDB's internal descriptor for
each index. It is in fact a bitmap of the following flags defined in
dict0mem.h:

---
/** Type flags of an index: OR'ing of the flags is allowed to define a
combination of types */
/* @{ */
#define DICT_CLUSTERED	1	/*!< clustered index; for other than
				auto-generated clustered indexes,
				also DICT_UNIQUE will be set */
#define DICT_UNIQUE	2	/*!< unique index */
...
---

Which means the user manual is a bit misleading about the values in the
TYPE column. It should say that:

0 means a non-unique secondary index
1 means an automatically generated clustered index (which is always
  named GEN_CLUST_INDEX)
2 means a unique non-clustered index
3 means a clustered index (which may be either PRIMARY KEY, or a unique
  secondary index, depending on table schema)

How to repeat:
Read description of the TYPE column at
https://dev.mysql.com/doc/refman/5.7/en/innodb-sys-indexes-table.html
and https://dev.mysql.com/doc/refman/8.0/en/innodb-indexes-table.html

Compare with the DICT_* flag descriptions in
storage/innobase/dict0mem.h.
[22 Mar 2018 10:44] MySQL Verification Team
Hello Alexey,

Thank you for the report.

Thanks,
Umesh
[4 Jun 2018 18:11] Daniel Price
Posted by developer:
 
The referenced documentation has been updated. The changes should appear online soon.

Thank you for the bug report.
[6 Jun 2018 13:07] Alexey Kopytov
Daniel,

The changes appear to be live already at
https://dev.mysql.com/doc/refman/8.0/en/innodb-indexes-table.html

I see there's a typo: "1 = automatically generated clustered index
(GEN_CLUST_INDEX); unique non-clustered index;" is missing "2 = " before
unique
[8 Jun 2018 17:39] Daniel Price
Posted by developer:
 
Thank you, Alexey. The typo is fixed. The change should appear online soon.