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 | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.25 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any |
[14 Sep 2021 2:52]
d f
[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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.