| Bug #98231 | show index from a partition table gets a wrong cardinality value | ||
|---|---|---|---|
| Submitted: | 15 Jan 2020 8:27 | Modified: | 17 Jan 2020 3:23 |
| Reporter: | Qingda Hu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Jan 2020 16:57]
MySQL Verification Team
Hi Mr. Hu, Thank you for your bug report. However, we do not quite understand what do you report as a bug. Initial table is empty, so you get maximum value for the cardinality. That is intentional. The other query will actually dive into each index and will come up with an exact value. Can you present your report more clearly and on a table which contains a number of rows ???
[16 Jan 2020 2:55]
Qingda Hu
First, in MySQL 8.0, if we create an empty normal table, we can get 0 for the cardinality on the table. Only for an empty partition table, we will get maximum value for the cardinality. Besides, in Mysql 5.6/5.7, we will get a value closer to 0 on an empty partition table. So, why have the intention to get maximum value for the cardinality on only empty partition tables in 8.0 ?
Second, even though we insert some records into the partition table, we also get the maximum value for the cardinality on the partition table even though we set information_schema_stats_expiry = 0 and the statistics information in mysql.innodb_index_stats has already been updated. Is this also an expected behavior ?
How to repeat
===============================================
mysql> set @@global.information_schema_stats_expiry = 0;
Query OK, 0 rows affected (0.00 sec)
Then, we quit and reconnect;
mysql> show variables like '%expiry%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0 |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `test3` (
-> `id` char(32) NOT NULL DEFAULT '',
-> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `partition_key` int(8) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`,`partition_key`),
-> UNIQUE KEY `id_uniqiue` (`id`,`partition_key`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY RANGE (partition_key)
-> (PARTITION p0 VALUES LESS THAN (20180619) ENGINE = InnoDB,
-> PARTITION p20180619 VALUES LESS THAN (20180620) ENGINE = InnoDB,
-> PARTITION p20180621 VALUES LESS THAN (20180622) ENGINE = InnoDB,
-> PARTITION p20180622 VALUES LESS THAN (20180623) ENGINE = InnoDB,
-> PARTITION p20180623 VALUES LESS THAN (20180624) ENGINE = InnoDB) */;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show index from test3;
+-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test3 | 0 | PRIMARY | 1 | id | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| test3 | 0 | PRIMARY | 2 | partition_key | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| test3 | 0 | id_uniqiue | 1 | id | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| test3 | 0 | id_uniqiue | 2 | partition_key | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
mysql> insert into test3 value (1, now(), 1), (2, now(), 2), (3, now(), 3), (4, now(), 4), (5, now(), 5), (6, now(), 6), (7, now(), 7), (8, now(), 8), (9, now(), 9), (10, now(), 10), (11, now(), 11), (12, now(), 12), (13, now(), 13), (14, now(), 14), (15, now(), 15);
Query OK, 15 rows affected (0.02 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> show index from test3;
+-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test3 | 0 | PRIMARY | 1 | id | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| test3 | 0 | PRIMARY | 2 | partition_key | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| test3 | 0 | id_uniqiue | 1 | id | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| test3 | 0 | id_uniqiue | 2 | partition_key | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats where database_name = 'test' and table_name = 'test3#P#p0';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | test3#P#p0 | PRIMARY | 2020-01-16 10:42:15 | n_diff_pfx01 | 15 | 1 | id |
| test | test3#P#p0 | PRIMARY | 2020-01-16 10:42:15 | n_diff_pfx02 | 15 | 1 | id,partition_key |
| test | test3#P#p0 | PRIMARY | 2020-01-16 10:42:15 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test3#P#p0 | PRIMARY | 2020-01-16 10:42:15 | size | 1 | NULL | Number of pages in the index |
| test | test3#P#p0 | id_uniqiue | 2020-01-16 10:42:15 | n_diff_pfx01 | 15 | 1 | id |
| test | test3#P#p0 | id_uniqiue | 2020-01-16 10:42:15 | n_diff_pfx02 | 15 | 1 | id,partition_key |
| test | test3#P#p0 | id_uniqiue | 2020-01-16 10:42:15 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test3#P#p0 | id_uniqiue | 2020-01-16 10:42:15 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
[16 Jan 2020 12:39]
MySQL Verification Team
Hi Mr. Hu, I have to agree with you. This needs to. be addressed. We shall first see if it is enough to document it. Then, if not, we shall go further. Verified as a documentation bug.
[17 Jan 2020 3:23]
Qingda Hu
Thank you for your quick reply.
[17 Jan 2020 13:17]
MySQL Verification Team
You are truly welcome.
[17 Jan 2020 17:31]
Jimmy Yang
Sinisa， this seems to be a true bug, rather than documentation bug, since innobase_get_table_statistics() is never going to get the table stats with the wrong table/partition name. Thus show index will always get the wrong cardinality. Jimmy
[20 Jan 2020 12:44]
MySQL Verification Team
Hi Jimmy, my friend, I agree with you. However, we are scheduling an internal discussion on this topic and when we come to the conclusions, category will change. There are also a couple of options on how to proceed.
[11 Mar 2020 12:14]
Niksa Skeledzija
Posted by developer: I have analyzed this bug and verified that it is not present in the latest mysql code. However, I wanted to get to the bottom of how and when it was fixed so I found Bug#29870919 which addressed this issue. Basically, Mr. Hu is correct in stating that Table_statistics::read_stat gets a nullptr for partition value. That holds for the latest code too. We would run into problems if we called read_stat_from_SE which does not support partitioned tables. However since the engine_name_ptr is also nullptr after the fix for 29870919, we call read_stat_by_open_table which properly handles this case.
Description: When we create a new partition table and perform 'show index' on the table, we get a wrong cardinality value. Then, we perform "select * from mysql.innodb_index_stats" and find the result is OK. We find 'show index from partition table' may have a bug by reading codes. How to repeat: ====================================== Just create a table like this: mysql> CREATE TABLE `test` ( -> `id` char(32) NOT NULL DEFAULT '', -> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `partition_key` int(8) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`,`partition_key`), -> UNIQUE KEY `id_uniqiue` (`id`,`partition_key`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -> /*!50100 PARTITION BY RANGE (partition_key) -> (PARTITION p0 VALUES LESS THAN (20180619) ENGINE = InnoDB, -> PARTITION p20180619 VALUES LESS THAN (20180620) ENGINE = InnoDB, -> PARTITION p20180621 VALUES LESS THAN (20180622) ENGINE = InnoDB, -> PARTITION p20180622 VALUES LESS THAN (20180623) ENGINE = InnoDB, -> PARTITION p20180623 VALUES LESS THAN (20180624) ENGINE = InnoDB) */; ====================================== Then, perform show index on the table: mysql> show index from test; +-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | test | 0 | PRIMARY | 1 | id | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL | | test | 0 | PRIMARY | 2 | partition_key | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL | | test | 0 | id_uniqiue | 1 | id | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL | | test | 0 | id_uniqiue | 2 | partition_key | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ Suggested fix: =========================================== We found in Item_func_internal_index_column_cardinality::val_int() from sql/item.func.cc , it inputs partition_name = nullptr for Table_statistics::read_stat(). result = thd->lex->m_IS_table_stats.read_stat( thd, *schema_name_ptr, *table_name_ptr, *index_name_ptr, nullptr, *column_name_ptr, index_ordinal_position - 1, column_ordinal_position - 1, *engine_name_ptr, se_private_id, nullptr, nullptr, stat_cardinality, cached_timestamp, dd::info_schema::enum_table_stats_type::INDEX_COLUMN_CARDINALITY); =========================================== In Table_statistics::read_stat(), it relies on partition_name to get statistics. if (!partition_name && hton_implements_get_statistics) result = read_stat_from_SE( thd, schema_name_ptr, table_name_ptr, index_name_ptr, column_name_ptr, index_ordinal_position, column_ordinal_position, se_private_id, ts_se_private_data, tbl_se_private_data, stype, hton); else result = read_stat_by_open_table( thd, schema_name_ptr, table_name_ptr, index_name_ptr, partition_name, column_name_ptr, column_ordinal_position, stype); =========================================== Because partition_name is nullptr, it calls read_stat_from_SE to get the statistics of the partition table. However, the innobase_get_table_statistics() can not find the partition table because the table name is only a logic table name ('test') rather than a physical partition table name (like 'test#P#p0' style)