MySQL Bugs: #80453: index_length is 0 (show table status) after CREATE INDEX

Bug #80453	index_length is 0 (show table status) after CREATE INDEX
Submitted:	20 Feb 2016 14:51	Modified:	20 Feb 2016 22:00
Reporter:	Su Dylan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=====
mysql> create table tbl(c1 int, c2 int);
'tbl';
Query OK, 0 rows affected (0.01 sec)

mysql> create index i1 on tbl(c1,c2);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'tbl';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| tbl  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2016-02-20 22:49:09 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> drop table tbl;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tbl(c1 int, c2 int, key i1(c1,c2));
show table status like 'tbl';
Query OK, 0 rows affected (0.00 sec)

mysql> show table status like 'tbl';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| tbl  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2016-02-20 22:49:09 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
Index_length should not be 0 after creating index in a separate SQL statement.

How to repeat:
drop table tbl;
create table tbl(c1 int, c2 int);
create index i1 on tbl(c1,c2);
show table status like 'tbl';
drop table tbl;
create table tbl(c1 int, c2 int, key i1(c1,c2));
show table status like 'tbl';

Suggested fix:
Index_length should not be 0 after creating index in a separate SQL statement.

Thank you for the bug version. Version 5.6 and 5.7 affected:

miguel@tikal ~/dbs/5.5 $ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.49-debug Source distribution

Copyright (c) 2000, 2016, 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> create table tbl(c1 int, c2 int);
Query OK, 0 rows affected (0,07 sec)

mysql> create index i1 on tbl(c1,c2);
Query OK, 0 rows affected (0,13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'tbl'\G
*************************** 1. row ***************************
           Name: tbl
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 10485760
 Auto_increment: NULL
    Create_time: 2016-02-20 19:23:20
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0,00 sec)

miguel@tikal ~/dbs $ 5.6/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30-debug Source distribution

Copyright (c) 2000, 2016, 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> create table tbl(c1 int, c2 int);
Query OK, 0 rows affected (0,25 sec)

mysql> create index i1 on tbl(c1,c2);
Query OK, 0 rows affected (0,22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'tbl'\G
*************************** 1. row ***************************
           Name: tbl
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-02-20 19:37:59
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0,00 sec)

Your MySQL connection id is 5
Server version: 5.7.12-debug Source distribution

Copyright (c) 2000, 2016, 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> create table tbl(c1 int, c2 int);
Query OK, 0 rows affected (0,28 sec)

mysql> create index i1 on tbl(c1,c2);
Query OK, 0 rows affected (0,22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'tbl'\G
*************************** 1. row ***************************
           Name: tbl
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-02-20 19:58:08
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0,01 sec)

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.30 Source distribution PULL: 2016-FEB-19

Copyright (c) 2000, 2016, 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 5.6 > use test
Database changed
mysql 5.6 > drop table tbl;
Query OK, 0 rows affected (0.05 sec)

mysql 5.6 > create table tbl(c1 int, c2 int);
Query OK, 0 rows affected (0.48 sec)

mysql 5.6 > create index i1 on tbl(c1,c2);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > ANALYZE TABLE tbl;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.tbl | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.14 sec)

mysql 5.6 > show table status like 'tbl'\G
*************************** 1. row ***************************
           Name: tbl
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-02-22 07:02:06
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.05 sec)

mysql 5.6 >

For the versions that I use right now (5.6.49, 5.7.31, 8.0.22) the Index_length column seems correct for 5.6 and 5.7 but is zero for 8.0