TiDB can not update stats correctly after drop a partition

[Rustin170506]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Create a table: create table pt(a int, b varchar(10), index idx_b (b)) partition by range(a) (partition p0 values less than (10), partition p1 values less than (20), partition p2 values less than (30));
2. Insert some data: insert into pt values (1, "a"), (11, "b"), (21, "c");
3. Check the meta:

mysql> select * from mysql.stats_meta;
+--------------------+----------+--------------+-------+----------+
| version            | table_id | modify_count | count | snapshot |
+--------------------+----------+--------------+-------+----------+
| 445337735984316422 |      103 |            1 |     1 |        0 |
| 445337735984316431 |      104 |            1 |     1 |        0 |
| 445337735984316436 |      105 |            1 |     1 |        0 |
| 445337735984316436 |      102 |            3 |     3 |        0 |
+--------------------+----------+--------------+-------+----------+
4 rows in set (0.01 sec)

4. Drop a partition: alter table pt drop partition p0;
5. Check the data: select * from pt;

mysql> select * from pt;
+------+------+
| a    | b    |
+------+------+
|   21 | c    |
|   11 | b    |
+------+------+
2 rows in set, 1 warning (0.00 sec)

6. Check the meta again:

mysql> select * from mysql.stats_meta;
+--------------------+----------+--------------+-------+----------+
| version            | table_id | modify_count | count | snapshot |
+--------------------+----------+--------------+-------+----------+
| 445337751765385233 |      103 |            1 |     1 |        0 |
| 445337735984316431 |      104 |            1 |     1 |        0 |
| 445337735984316436 |      105 |            1 |     1 |        0 |
| 445337735984316436 |      102 |            3 |     3 |        0 |
+--------------------+----------+--------------+-------+----------+
4 rows in set (0.00 sec)

2. What did you expect to see? (Required)

mysql> select * from mysql.stats_meta;
+--------------------+----------+--------------+-------+----------+
| version            | table_id | modify_count | count | snapshot |
+--------------------+----------+--------------+-------+----------+
+| 445337751765385233 |      103 |            0 |     0 |        0 |
| 445337735984316431 |      104 |            1 |     1 |        0 |
| 445337735984316436 |      105 |            1 |     1 |        0 |
+| 445337735984316436 |      102 |            2 |     2 |        0 |
+--------------------+----------+--------------+-------+----------+
5 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> show stats_meta;
mysql> select * from mysql.stats_meta;
+--------------------+----------+--------------+-------+----------+
| version            | table_id | modify_count | count | snapshot |
+--------------------+----------+--------------+-------+----------+
| 445337751765385233 |      103 |            1 |     1 |        0 |
| 445337735984316431 |      104 |            1 |     1 |        0 |
| 445337735984316436 |      105 |            1 |     1 |        0 |
| 445337735984316436 |      102 |            3 |     3 |        0 |
+--------------------+----------+--------------+-------+----------+
4 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.6.0-alpha-69-gbf8c728934
Edition: Community
Git Commit Hash: bf8c728934c7376765ddd943fd302fcb65af4bf0
Git Branch: master
UTC Build Time: 2023-11-01 08:29:41
GoVersion: go1.21.1
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Rustin170506]

After 5 minutes:
We can see the GC background worker deleted the old partition meta. But the global partition stats still are wrong.

mysql> select * from mysql.stats_meta;
+--------------------+----------+--------------+-------+----------+
| version            | table_id | modify_count | count | snapshot |
+--------------------+----------+--------------+-------+----------+
| 445337974970777613 |      104 |            1 |     1 |        0 |
| 445337974983884805 |      105 |            1 |     1 |        0 |
| 445337974983884818 |      102 |            3 |     3 |        0 |
+--------------------+----------+--------------+-------+----------+
3 rows in set (0.00 sec)

[Rustin170506]

I dug into the code and there are some more details about this issue:

1. After we drop a partition, if we haven't analyzed the table, even if we tried to update the global stats here:

   tidb/pkg/statistics/handle/ddl/ddl.go

   Line 125 in 9cf638f

   if err := h.globalStatsHandler.UpdateGlobalStats(globalTableInfo); err != nil {

   
   we could not update it because we missing the histogram of its partitions, so we terminated the whole update process here:

   tidb/pkg/statistics/handle/globalstats/global_stats.go

   Line 223 in 3543275

   continue

2. If you try the following steps, you will get the right result:
   1. Create a table: create table pt(a int, b varchar(10), index idx_b (b)) partition by range(a) (partition p0 values less than (10), partition p1 values less than (20), partition p2 values less than (30));
   2. Insert some data: insert into pt values (1, "a"), (11, "b"), (21, "c");
   3. Check the meta, and wait for the stats meta to be dumped.
   4. Analyze table pt: analyze table pt;
   5. Drop a partition: alter table pt drop partition p0;
   6. Check the meta again, you will get a correct count from global stats.

[Rustin170506]

Based on this design doc, we will only update the count and modfiy_count for it. Then it will use the count and modify_count to determine if we need to trigger an auto-analysis task.