Bug #104933 Execute analyze table during online DDL, which may cause index failure

• Submitted: 14 Sep 2021 2:52
• Modified: 15 Sep 2021 12:15
• Reporter: d f
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.25
• OS: Linux
• CPU Architecture: Any

[14 Sep 2021 2:52] d f

Description:
When executing online DDL, executing analyze table on the table will cause the statistical information of the table to be lost.Which in turn affects the choice of index

How to repeat:
 CREATE TABLE `test_analzye` (
  `id1` int NOT NULL,
  `id2` int NOT NULL,
  `id3` int DEFAULT NULL,
  PRIMARY KEY (id1,id2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
DELIMITER //  
CREATE PROCEDURE insert_in(in parameter int)  
   begin 
   declare var int;  
   set var=0;  
   while var<parameter do  
   replace into test_analzye values(var%10000 , var , var);  
   set var=var+1;  
   end while;  
   end;  
   //  

DELIMITER ;
call insert_in(10000000);
alter table test_analzye engine  innodb;

Another session：
 SET optimizer_trace="enabled=on";
 explain select * from test_analzye where id1 = 10 ;
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_analzye | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const | 1000 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+

 select * from information_schema.optimizer_trace\G
 analyze table test_analzye;
 explain select * from test_analzye where id1 = 10;
 select * from information_schema.optimizer_trace\G
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_analzye | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+

The execution plan becomes a full table scan.

Suggested fix:
Can judge whether dict_stats_analyze_index is executed successfully,if execution failed do not update statistics

[14 Sep 2021 12:32] MySQL Verification Team

Hi Mr. f,

Thank you for your bug report.

However, it seems that there is a slight mistake in your report.

After you run ANALYSE, can you please run only the following two commands:

SET optimizer_trace="enabled=on";
explain select * from test_analzye where id1 = 10 ;

and send us the output.

Thanks in advance.

[14 Sep 2021 13:20] d f

Hi：
  This is the report you need.
  
analyze table test_analzye;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| test.test_analzye | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from test_analzye where id1 = 10 ;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_analzye | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

[14 Sep 2021 13:26] d f

Some information in the information_schema.optimizer_trace has changed
before analyze:
"rows_estimation": [
              {
                "table": "`test_analzye`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 9747565,
                    "cost": 980965
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id1",
                        "id2"
                      ]
                    }
                  ],
after analyze:
            "rows_estimation": [
              {
                "table": "`test_analzye`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1,
                    "cost": 2.45
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id1",
                        "id2"
                      ]
                    }
                  ],
I think analzye caused some information to be lost when altering the table.

[14 Sep 2021 15:42] Øystein Grøvlen

Is this a duplicate of Bug#98132 ?

[14 Sep 2021 15:52] d f

yes ,thanks

[15 Sep 2021 12:15] MySQL Verification Team

Thank you Mr. f and Oystein.

This bug is indeed a duplicate of the bug :

https://bugs.mysql.com/bug.php?id=98132

Since we have a number of bugs in this category, we are increasing the severity of the original bug.