Bug #119276 ALTER TABLE ANALYZE PARTITION statement causes incorrect results in the mysql.table_table_stats

• Submitted: 31 Oct 6:05
• Reporter: Ruyi Zhang (OCA)
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any
• Tags: table_stats

[31 Oct 6:05] Ruyi Zhang

Description:
In our recent tests, we found that executing `ALTER TABLE ANALYZE PARTITION` causes incorrect results in `mysql.table_stats` — specifically, `table_rows`, `data_length`, and `index_length` do not include data from other partitions, leading to significant deviations in the statistical results.

mysql> select count(1) from p;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> explain select count(1) from p;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | p     | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show table status like 'p';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| p    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2025-10-31 11:30:12 | NULL        | NULL       | utf8mb4_general_ci |     NULL | partitioned    |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

How to repeat:
create table p(a int) partition by hash(a) partitions 2;
insert into p values (1);
insert into p values (1);
insert into p values (1);
insert into p values (1);
insert into p values (1);
analyze table p;
show table status like 'p';

alter table p analyze partition p0; -- cause wrong results in mysql.table_stats

show table status like 'p';

Suggested fix:
diff --git a/storage/innobase/handler/ha_innopart.cc b/storage/innobase/handler/ha_innopart.cc
index 1a712955c77..bb1edcaef4e 100644
--- a/storage/innobase/handler/ha_innopart.cc
+++ b/storage/innobase/handler/ha_innopart.cc
@@ -3509,6 +3509,8 @@ int ha_innopart::info_low(uint flag, bool is_analyze) {
 
   ut_ad(m_part_share->get_table_part(0)->n_ref_count > 0);
 
+  ut_ad(!is_analyze || bitmap_is_set_all(&m_part_info->read_partitions));
+
   if ((flag & HA_STATUS_TIME) != 0) {
     stats.update_time = 0;
 
@@ -3562,6 +3564,12 @@ int ha_innopart::info_low(uint flag, bool is_analyze) {
       stats.delete_length = 0;
     }
 
+    if (is_analyze) {
+      // All partitions must be included when calculating the stats if analyzing
+      // partitions.
+      bitmap_set_all(&m_part_info->read_partitions);
+    }
+
     for (uint i = m_part_info->get_first_used_partition(); i < m_tot_parts;
          i = m_part_info->get_next_used_partition(i)) {
       ib_table = m_part_share->get_table_part(i);