Bug #109069 incorrect index_length on information_schema.tables after add a index on table.

• Submitted: 12 Nov 2022 8:59
• Modified: 15 Nov 2022 13:13
• Reporter: biao li
• Status: Not a Bug
• Category: MySQL Server: Storage Engines
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[12 Nov 2022 8:59] biao li

Description:
After I add a index on table, the index_length info on information_schema.tables didn't updated automatically.

How to repeat:
1. set information_schema_stats_expiry to 0:

mysql> show variables like "information_schema_stats_expiry";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0     |
+---------------------------------+-------+

2. create table like follows:

mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
and insert some rows.

3. Now, index_length equals to 0,because there is no index on table t1.

mysql> select DATA_LENGTH,INDEX_LENGTH from information_schema.tables where table_name='t1';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|     1589248 |            0 |
+-------------+--------------+
1 row in set (0.00 sec)

4. Add index on table t1, but the index_length still equals to 0;

mysql> alter table t1 add index idx_name(name);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select DATA_LENGTH,INDEX_LENGTH from information_schema.tables where table_name='t1';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|     1589248 |            0 |
+-------------+--------------+
1 row in set (0.01 sec)

Now, index_length on information_schema.tables will never be updated，unless we do others DMLs on the table t1 or analyze table t1.

Suggested fix:
Seems innodb didn't update the new added index's size statistics.

[14 Nov 2022 13:19] MySQL Verification Team

Hi Mr. li,

Thank you for your bug report.

However, that table is pretty large. Hence, it is possible that it was created by the default option, which is INSTANT. Try setting the option to COPY, wait for it to finish and then run I_S schema query ......

Waiting on your feedback.

[15 Nov 2022 1:46] biao li

hi, the table has just only 2047 rows, and you can insert the table with following steps:

insert into t1 values(0,repeat("a",100));

repeat `insert into t1 select * from t1;` 10 times.

so, I don't think the table is pretty large.

The default algorithm of adding index is `inplace`. Of course, if I add index with `copy` algorithm, the index_length in i_s.tables will not be zero, because it will run normal inserting step in innodb and finally it will call `row_update_statistics_if_needed`.

[15 Nov 2022 1:56] biao li

The complete repeatable steps:

create table t1(id int, name varchar(100));
insert into t1 values(0,repeat("a",100));
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
insert into t1 select * from t;
alter table t1 add index idx_name(name);

select * from information_schema.tables where table_name = "t1";

[15 Nov 2022 8:10] huahua xu

Hi Biao Li,

The `inplace` algorithm just calculates new estimates for changed index statistics when committing the ddl made during, but the result of your query comes from the table statistics.

For more details, you can see the implementation of `commit_inplace_alter_table` in the InnoDB: `ha_innobase::commit_inplace_alter_table_impl`

[15 Nov 2022 13:13] MySQL Verification Team

Hi,

Yes, this is exactly the expected behaviour with INPLACE and COPY algorithms .....

Not a bug.