Description:
The innodb_indexes table does not display the indexes if the table is partitioned.
I just used the hash partition type for testing. I'm not sure if it fails for the other types.
How to repeat:
mysql> create database partition_bug;
Query OK, 1 row affected (0,01 sec)
mysql> use partition_bug
Database changed
mysql> create table non_partitioned (id integer primary key, field1 integer, field2 integer, key ifield1(field1), key ifield2(field2));
Query OK, 0 rows affected (0,02 sec)
mysql> create table partitioned (id integer primary key, field1 integer, field2 integer, key ifield1(field1), key ifield2(field2)) partition by hash(id);
Query OK, 0 rows affected (0,01 sec)
mysql> show create table non_partitioned\G
*************************** 1. row ***************************
Table: non_partitioned
Create Table: CREATE TABLE `non_partitioned` (
`id` int NOT NULL,
`field1` int DEFAULT NULL,
`field2` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ifield1` (`field1`),
KEY `ifield2` (`field2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0,00 sec)
mysql> show create table partitioned\G
*************************** 1. row ***************************
Table: partitioned
Create Table: CREATE TABLE `partitioned` (
`id` int NOT NULL,
`field1` int DEFAULT NULL,
`field2` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ifield1` (`field1`),
KEY `ifield2` (`field2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`id`) */
1 row in set (0,01 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select innodb_tables.name, innodb_indexes.name from innodb_tables left join innodb_indexes using (table_id) where innodb_tables.name like 'partition_bug/partitioned%';
+--------------------------------+------+
| name | name |
+--------------------------------+------+
| partition_bug/partitioned#p#p0 | NULL |
+--------------------------------+------+
1 row in set (0,01 sec)
mysql> select innodb_tables.name, innodb_indexes.name from innodb_tables left join innodb_indexes using (table_id) where innodb_tables.name like 'partition_bug/non_partitioned%';
+-------------------------------+---------+
| name | name |
+-------------------------------+---------+
| partition_bug/non_partitioned | ifield2 |
| partition_bug/non_partitioned | ifield1 |
| partition_bug/non_partitioned | PRIMARY |
+-------------------------------+---------+
3 rows in set (0,00 sec)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.34 |
+-----------+
1 row in set (0,00 sec)
mysql>
Suggested fix:
List the indexes for the partitioned table.
Description: The innodb_indexes table does not display the indexes if the table is partitioned. I just used the hash partition type for testing. I'm not sure if it fails for the other types. How to repeat: mysql> create database partition_bug; Query OK, 1 row affected (0,01 sec) mysql> use partition_bug Database changed mysql> create table non_partitioned (id integer primary key, field1 integer, field2 integer, key ifield1(field1), key ifield2(field2)); Query OK, 0 rows affected (0,02 sec) mysql> create table partitioned (id integer primary key, field1 integer, field2 integer, key ifield1(field1), key ifield2(field2)) partition by hash(id); Query OK, 0 rows affected (0,01 sec) mysql> show create table non_partitioned\G *************************** 1. row *************************** Table: non_partitioned Create Table: CREATE TABLE `non_partitioned` ( `id` int NOT NULL, `field1` int DEFAULT NULL, `field2` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `ifield1` (`field1`), KEY `ifield2` (`field2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0,00 sec) mysql> show create table partitioned\G *************************** 1. row *************************** Table: partitioned Create Table: CREATE TABLE `partitioned` ( `id` int NOT NULL, `field1` int DEFAULT NULL, `field2` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `ifield1` (`field1`), KEY `ifield2` (`field2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`id`) */ 1 row in set (0,01 sec) mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select innodb_tables.name, innodb_indexes.name from innodb_tables left join innodb_indexes using (table_id) where innodb_tables.name like 'partition_bug/partitioned%'; +--------------------------------+------+ | name | name | +--------------------------------+------+ | partition_bug/partitioned#p#p0 | NULL | +--------------------------------+------+ 1 row in set (0,01 sec) mysql> select innodb_tables.name, innodb_indexes.name from innodb_tables left join innodb_indexes using (table_id) where innodb_tables.name like 'partition_bug/non_partitioned%'; +-------------------------------+---------+ | name | name | +-------------------------------+---------+ | partition_bug/non_partitioned | ifield2 | | partition_bug/non_partitioned | ifield1 | | partition_bug/non_partitioned | PRIMARY | +-------------------------------+---------+ 3 rows in set (0,00 sec) mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.34 | +-----------+ 1 row in set (0,00 sec) mysql> Suggested fix: List the indexes for the partitioned table.