| Bug #113750 | stats of partitions could be empty after some kind of alter table DDL statements | ||
|---|---|---|---|
| Submitted: | 25 Jan 2024 6:50 | Modified: | 26 Jan 2024 10:12 |
| Reporter: | Brian Yue (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 8.0.36 | OS: | Any (rhel-7.4) |
| Assigned to: | CPU Architecture: | Any (x86-64) | |
| Tags: | partition table, stats | ||
[25 Jan 2024 7:50]
MySQL Verification Team
Hello Brian Yue,
Thank you for the report and test case.
Quickly tried on latest GA build but not seeing any issues. Am I missing anything here? Could you please confirm on 8.0.36? Thank you.
-- 8.0.36
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table test1 (id int auto_increment primary key, c1 char(255) charset utf8mb4 default 'a', c2 char(255) charset utf8mb4 default 'a', c3 char(255) charset utf8mb4 default 'a', c4 char(255) charset utf8mb4 default 'a', vc1 varchar(100) default 'b') partition by range(id) (partition p1 values less than (10000), partition pmax values less than MAXVALUE);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test1 (id) values (NULL),(NULL),(NULL),(NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 64 rows affected (0.01 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 128 rows affected (0.02 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 256 rows affected (0.07 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 512 rows affected (0.01 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 1024 rows affected (0.02 sec)
Records: 1024 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 2048 rows affected (0.04 sec)
Records: 2048 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 4096 rows affected (0.07 sec)
Records: 4096 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;Query OK, 8192 rows affected (0.24 sec)
Records: 8192 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 16384 rows affected (0.25 sec)
Records: 16384 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 32768 rows affected (0.38 sec)
Records: 32768 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 65536 rows affected (2.22 sec)
Records: 65536 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 131072 rows affected (5.92 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 262144 rows affected (10.08 sec)
Records: 262144 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 524288 rows affected (20.86 sec)
Records: 524288 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 1048576 rows affected (42.20 sec)
Records: 1048576 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 2097152 rows affected (1 min 25.67 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
mysql>
mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 4194304 |
+----------+
1 row in set (1.25 sec)
mysql> select * from test1 limit 2;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | vc1 |
+----+------+------+------+------+------+
| 1 | a | a | a | a | b |
| 2 | a | a | a | a | b |
+----+------+------+------+------+------+
2 rows in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats where database_name='stats' and table_name like '%test1%';
Empty set (0.01 sec)
mysql> select * from mysql.innodb_table_stats where database_name='test' and table_name like '%test1%';
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| test | test1#p#p1 | 2024-01-25 08:38:44 | 9152 | 737 | 0 |
| test | test1#p#pmax | 2024-01-25 08:41:42 | 3710694 | 285760 | 0 |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> alter table test1 modify column vc1 varchar(99);
Query OK, 4194304 rows affected (2 min 40.97 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
mysql> select * from mysql.innodb_table_stats where database_name='test' and table_name like '%test1%';
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| test | test1#p#p1 | 2024-01-25 08:46:57 | 9295 | 737 | 0 |
| test | test1#p#pmax | 2024-01-25 08:47:07 | 3885908 | 299264 | 0 |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> select count(*) from test1 partition (p1);
+----------+
| count(*) |
+----------+
| 9999 |
+----------+
1 row in set (0.02 sec)
mysql> explain select count(*) from test1 where id > 1 and id < 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
partitions: p1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 8
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
mysql>
regards,
Umesh
[25 Jan 2024 12:21]
Brian Yue
Hello Umesh,
I tried on MySQL 8.0.36, and the issue can also be reproduced stably:
[yxxdb_8031@localhost ~]$ mysql -uroot -p'db1x@NJ+1' -S bin/mysql1.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database stats;
Query OK, 1 row affected (0.00 sec)
mysql> ^DBye
[yxxdb_8031@localhost ~]$ mysql -uroot -p'db1x@NJ+1' -S bin/mysql1.sock stats
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table test1 (id int auto_increment primary key, c1 char(255) charset utf8mb4 default 'a', c2 char(255) charset utf8mb4 default 'a', c3 char(255) charset utf8mb4 default 'a', c4 char(255) charset utf8mb4 default 'a', vc1 varchar(100) default 'b') partition by range(id) (partition p1 values less than (10000), partition pmax values less than MAXVALUE);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 (id) values (NULL),(NULL),(NULL),(NULL);
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
Query OK, 64 rows affected (0.01 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
insert into test1 (id) select NULL from test1;
Query OK, 128 rows affected (0.00 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 256 rows affected (0.01 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 512 rows affected (0.00 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 1024 rows affected (0.01 sec)
Records: 1024 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 2048 rows affected (0.02 sec)
Records: 2048 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 4096 rows affected (0.03 sec)
Records: 4096 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 8192 rows affected (0.09 sec)
Records: 8192 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 16384 rows affected (0.16 sec)
Records: 16384 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 32768 rows affected (0.31 sec)
Records: 32768 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 65536 rows affected (0.66 sec)
Records: 65536 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 131072 rows affected (1.31 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 262144 rows affected (2.73 sec)
Records: 262144 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 524288 rows affected (5.47 sec)
Records: 524288 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 1048576 rows affected (11.59 sec)
Records: 1048576 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 2097152 rows affected (23.84 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from mysql.innodb_table_stats where database_name='stats' and table_name like '%test1%';
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| stats | test1#p#p1 | 2024-01-25 19:48:30 | 9295 | 737 | 0 |
| stats | test1#p#pmax | 2024-01-25 19:49:09 | 3885908 | 299264 | 0 |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
2 rows in set (0.01 sec)
mysql> alter table test1 modify column vc1 varchar(99);
Query OK, 4194304 rows affected (48.28 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
mysql> select * from mysql.innodb_table_stats where database_name='stats' and table_name like '%test1%';
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| stats | test1#p#p1 | 2024-01-25 19:51:59 | 0 | 1 | 0 |
| stats | test1#p#pmax | 2024-01-25 19:52:47 | 3885908 | 299264 | 0 |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain select * from test1 where id > 1 and id < 30 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
partitions: p1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> select count(*) from test1 partition (p1);
+----------+
| count(*) |
+----------+
| 9999 |
+----------+
1 row in set (0.08 sec)
[25 Jan 2024 12:22]
Brian Yue
By the way, the config file I used to start my mysql server is followed, I hope it will be helpful: [yxxdb_8031@localhost ~]$ ps x | grep mysqld 194484 pts/2 Sl 2:43 mysqld --defaults-file=~/etc/my.cnf 229998 pts/2 S+ 0:00 grep --color=auto mysqld [yxxdb_8031@localhost ~]$ [yxxdb_8031@localhost ~]$ cat ~/etc/my.cnf # Don't change configuration items to other sections [general] instance_num=1 [mysqld] table_open_cache = 1024 max_allowed_packet = 16M sort_buffer_size = 512K read_buffer_size = 256K read_rnd_buffer_size = 512K join_buffer_size = 2M max_connect_errors = 100000 max_connections = 1000 skip-external-locking sql_mode = STRICT_TRANS_TABLES sync_binlog=1 binlog_expire_logs_seconds=259200 open_files_limit = 10000 transaction-isolation = READ-COMMITTED lower_case_table_names = 1 performance_schema = ON innodb_data_file_path = ibdata1:500M:autoextend innodb-file-per-table innodb_buffer_pool_size = 1GB innodb_buffer_pool_instances = 1 innodb_buffer_pool_dump_at_shutdown = 0 innodb_buffer_pool_dump_now = 0 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_load_now = 0 innodb_flush_log_at_trx_commit = 1 innodb_io_capacity = 2000 innodb_io_capacity_max = 10000 innodb_lock_wait_timeout = 600 innodb_log_buffer_size = 8M innodb_log_files_in_group = 2 innodb_log_file_size = 2048M innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_autoinc_lock_mode = 2 innodb_flush_neighbors = 1 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_undo_log_truncate=ON innodb_flush_method=O_DIRECT innodb_adaptive_hash_index = OFF innodb_print_all_deadlocks=ON max_binlog_size = 104857600 binlog_format=ROW group_concat_max_len = 1024 log-slave-updates=1 slave_parallel_workers = 32 slave-parallel-type = LOGICAL_CLOCK slave_preserve_commit_order = 1 relay_log_recovery = 0 mysqlx=0 auto_increment_increment = 1 auto_increment_offset = 1 port = 6632 socket = /home/data_nvme/yxxdb_8031/bin/mysql1.sock bind_address =10.229.31.45 datadir = /home/data_nvme/yxxdb_8031/data/data log-error=/home/data_nvme/yxxdb_8031/log/mysqld1.log pid-file=/home/data_nvme/yxxdb_8031/bin/mysqld1.pid innodb_data_home_dir = /home/data_nvme/yxxdb_8031/data/data innodb_log_group_home_dir = /home/data_nvme/yxxdb_8031/data/redo innodb_undo_directory=/home/data_nvme/yxxdb_8031/data/undo server-id = 1396 basedir=/home/data_nvme/yxxdb_8031 #skip-log-bin log-bin=../binlog/mysql-bin relay-log=../relaylog/relay-bin tmpdir=/home/data_nvme/yxxdb_8031/data/tmp secure-file-priv= gtid_mode = on enforce_gtid_consistency=on skip-name-resolve report_host = 10.229.31.45 report_port = 6632 default-time-zone='+8:00' log_timestamps=SYSTEM master-info-repository=TABLE relay-log-info-repository=TABLE slave_net_timeout = 5 lock_wait_timeout = 5 character_set_server = utf8mb3 collation_server = utf8mb3_bin innodb_page_size=16384 max_prepared_stmt_count = 1000000 log_error_verbosity = 3 # debug="d,info,error,query,general,where:O/home/data_nvme/yxxdb_8031/mysqld.trace" thread_stack = 286720 default_authentication_plugin=mysql_native_password wait_timeout = 88888 temptable_max_ram = 2MB temptable_max_mmap = 300MB innodb_print_ddl_logs = ON slow_query_log = ON long_query_time = 0.005 log_slow_admin_statements = ON temptable_max_ram = 2097152 temptable_max_mmap = 50737418240 skip-log-bin = ON [client] port = 6631 socket = /home/data_nvme/yxxdb_8031/bin/mysql1.sock [mysql] no-auto-rehash
[26 Jan 2024 10:12]
MySQL Verification Team
Thank you, Brian Yue.
Verified as described.
== using provided conf
rm -rf 113750/
bin/mysqld --defaults-file=./my.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/113750 --log-error-verbosity=3
bin/mysqld_safe --defaults-file=./my.cnf --mysqld-version='' --basedir=$PWD --datadir=$PWD/113750 --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/113750/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 &
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
.
.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table test1 (id int auto_increment primary key, c1 char(255) charset utf8mb4 default 'a', c2 char(255) charset utf8mb4 default 'a', c3 char(255) charset utf8mb4 default 'a', c4 char(255) charset utf8mb4 default 'a', vc1 varchar(100) default 'b') partition by range(id) (partition p1 values less than (10000), partition pmax values less than MAXVALUE);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test1 (id) values (NULL),(NULL),(NULL),(NULL);
mysql> insert into test1 (id) select NULL from test1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
.
.
mysql> insert into test1 (id) select NULL from test1;
Query OK, 524288 rows affected (6.42 sec)
Records: 524288 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 1048576 rows affected (23.88 sec)
Records: 1048576 Duplicates: 0 Warnings: 0
mysql> insert into test1 (id) select NULL from test1;
Query OK, 2097152 rows affected (36.33 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 4194304 |
+----------+
1 row in set (42.85 sec)
mysql> select * from test1 limit 2;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | vc1 |
+----+------+------+------+------+------+
| 1 | a | a | a | a | b |
| 2 | a | a | a | a | b |
+----+------+------+------+------+------+
2 rows in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats where database_name='test' and table_name like '%test1%';
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| test | test1#p#p1 | 2024-01-26 18:01:30 | 9295 | 737 | 0 |
| test | test1#p#pmax | 2024-01-26 18:03:40 | 3835130 | 295360 | 0 |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
2 rows in set (0.01 sec)
mysql> alter table test1 modify column vc1 varchar(99);
Query OK, 4194304 rows affected (1 min 15.25 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
mysql> select * from mysql.innodb_table_stats where database_name='test' and table_name like '%test1%';
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
| test | test1#p#p1 | 2024-01-26 18:07:46 | 0 | 1 | 0 |
| test | test1#p#pmax | 2024-01-26 18:09:02 | 3885908 | 299264 | 0 |
+---------------+--------------+---------------------+---------+----------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain select * from test1 where id > 1 and id < 30 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
partitions: p1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> select count(*) from test1 partition (p1);
+----------+
| count(*) |
+----------+
| 9999 |
+----------+
1 row in set (0.49 sec)
mysql>
Sincerely,
Umesh
Description: Hello, Recently we find a case that will cause empty stats (mysql.innodb_table_stats and mysql.innodb_index_stats) info if DDL statements which choose copy algrithm is executed. After that, execution plan of range select statements will choose INDEX SCAN or TABLE SCAN, lead to poor performance. Of course, analyze table can cure this. How to repeat: # create a table with 2 partitions create table test1 (id int auto_increment primary key, c1 char(255) charset utf8mb4 default 'a', c2 char(255) charset utf8mb4 default 'a', c3 char(255) charset utf8mb4 default 'a', c4 char(255) charset utf8mb4 default 'a', vc1 varchar(100) default 'b') partition by range(id) (partition p1 values less than (10000), partition pmax values less than MAXVALUE); # fill the table with 4194304 records (the more record, the easier to reproduce this problem) insert into test1 (id) values (NULL),(NULL),(NULL),(NULL); insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; insert into test1 (id) select NULL from test1; # now check the data mysql> select count(*) from test1; +----------+ | count(*) | +----------+ | 4194304 | +----------+ 1 row in set (9.24 sec) mysql> select * from test1 limit 2; +----+------+------+------+------+------+ | id | c1 | c2 | c3 | c4 | vc1 | +----+------+------+------+------+------+ | 1 | a | a | a | a | b | | 2 | a | a | a | a | b | +----+------+------+------+------+------+ 2 rows in set (0.00 sec) # check the stats info, every thing is OK mysql> select * from mysql.innodb_table_stats where database_name='stats' and table_name like '%test1%'; +---------------+--------------+---------------------+---------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+--------------+---------------------+---------+----------------------+--------------------------+ | stats | test1#p#p1 | 2024-01-25 14:11:08 | 9295 | 737 | 0 | | stats | test1#p#pmax | 2024-01-25 14:12:32 | 3520697 | 278656 | 0 | +---------------+--------------+---------------------+---------+----------------------+--------------------------+ 2 rows in set (0.00 sec) # NOW, let's execute a DDL statment mysql> alter table test1 modify column vc1 varchar(99); Query OK, 4194304 rows affected (48.71 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> select * from mysql.innodb_table_stats where database_name='stats' and table_name like '%test1%'; +---------------+--------------+---------------------+---------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+--------------+---------------------+---------+----------------------+--------------------------+ | stats | test1#p#p1 | 2024-01-25 14:14:01 | 0 | 1 | 0 | | stats | test1#p#pmax | 2024-01-25 14:14:50 | 3885908 | 299264 | 0 | +---------------+--------------+---------------------+---------+----------------------+--------------------------+ 2 rows in set (0.00 sec) mysql> mysql> select count(*) from test1 partition (p1); +----------+ | count(*) | +----------+ | 9999 | +----------+ 1 row in set (0.07 sec) mysql> explain select count(*) from test1 where id > 1 and id < 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1 partitions: p1 type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.01 sec) # See, the stats of the leading partition is cleared ant not update automatically; # If there are many partitions, I suppose that stats info leading partitions will all be cleared after the DDL. # Code about stats and DDL commit reserves same implement, I guess all MySQL 8.0 instances are facing this problem. Suggested fix: Before committing the DDL, update the stats info of partitions firstly.