Description:
When information schema stats expire, it looks we return bad stats for partitioned tables.
It looks like there are a few issues preventing partitions from getting stats:
In innobase_get_index_column_cardinality, we call row_search_index_stats to fetch persistent stats in mysql.innodb_index_stats. However, the table name is stored with the partitioned number (eg. t#P#p0) whereas we're doing lookups with just the table name (eg. t).
But even if we don't have persistent stats, there's fallback code to calculate it on the fly. We first acquire the table DD object with dd_table_open_on_name and then call dict_stats_init on it to populate stats. However, the implementation there is also incorrect inside dd_table_open_on_name because we have this code:
if (dd_table->se_private_id() == dd::INVALID_OBJECT_ID) {
...
if (strlen(part_buf) != 0) {
} else {
/* FIXME: Once FK functions will not open
partitioned table in current improper way,
just assert this false */
table = nullptr;
}
}
In this case, part_buf == 0 because we do not pass the partitioned name, so table is set to nullptr. This leads to innodb skipping the call to dict_stats_init to populate stats.
How to repeat:
CREATE TABLE t (a int(11) NOT NULL AUTO_INCREMENT,
b int(11) NOT NULL,
c int(11) DEFAULT NULL,
PRIMARY KEY (a,`b`),
KEY b (b,`c`)) ENGINE=InnoDB
PARTITION BY RANGE (b)
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
INSERT INTO t VALUES (NULL, 0, 0);
INSERT INTO t SELECT NULL, 0, 0 FROM t;
INSERT INTO t SELECT NULL, 0, 0 FROM t a, t b, t c, t d, t e, t f, t g;
ANALYZE TABLE t;
set information_schema_stats_expiry = 1;
select sleep(1);
SHOW INDEXES IN t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t | 0 | PRIMARY | 1 | a | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| t | 0 | PRIMARY | 2 | b | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| t | 1 | b | 1 | b | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL |
| t | 1 | b | 2 | c | A | 4294967295 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Suggested fix:
Make this work, for both persistent/transient innodb stats.
Description: When information schema stats expire, it looks we return bad stats for partitioned tables. It looks like there are a few issues preventing partitions from getting stats: In innobase_get_index_column_cardinality, we call row_search_index_stats to fetch persistent stats in mysql.innodb_index_stats. However, the table name is stored with the partitioned number (eg. t#P#p0) whereas we're doing lookups with just the table name (eg. t). But even if we don't have persistent stats, there's fallback code to calculate it on the fly. We first acquire the table DD object with dd_table_open_on_name and then call dict_stats_init on it to populate stats. However, the implementation there is also incorrect inside dd_table_open_on_name because we have this code: if (dd_table->se_private_id() == dd::INVALID_OBJECT_ID) { ... if (strlen(part_buf) != 0) { } else { /* FIXME: Once FK functions will not open partitioned table in current improper way, just assert this false */ table = nullptr; } } In this case, part_buf == 0 because we do not pass the partitioned name, so table is set to nullptr. This leads to innodb skipping the call to dict_stats_init to populate stats. How to repeat: CREATE TABLE t (a int(11) NOT NULL AUTO_INCREMENT, b int(11) NOT NULL, c int(11) DEFAULT NULL, PRIMARY KEY (a,`b`), KEY b (b,`c`)) ENGINE=InnoDB PARTITION BY RANGE (b) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN (MAXVALUE)); INSERT INTO t VALUES (NULL, 0, 0); INSERT INTO t SELECT NULL, 0, 0 FROM t; INSERT INTO t SELECT NULL, 0, 0 FROM t a, t b, t c, t d, t e, t f, t g; ANALYZE TABLE t; set information_schema_stats_expiry = 1; select sleep(1); SHOW INDEXES IN t; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t | 0 | PRIMARY | 1 | a | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL | | t | 0 | PRIMARY | 2 | b | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL | | t | 1 | b | 1 | b | A | 4294967295 | NULL | NULL | | BTREE | | | YES | NULL | | t | 1 | b | 2 | c | A | 4294967295 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ Suggested fix: Make this work, for both persistent/transient innodb stats.