From a3148ca909ba6d9fd9c943f00e792479cdaafcc7 Mon Sep 17 00:00:00 2001 From: Ayush Pandey Date: Fri, 22 May 2026 03:59:04 +0000 Subject: [PATCH] Fix stale partition statistics after ALTER TABLE REORGANIZE PARTITION InnoDB partition statistics are never recalculated after ALTER TABLE REORGANIZE PARTITION (or COALESCE/REBUILD PARTITION). The inplace ALTER path copies data into new partitions but does not call dict_stats_update() on them, leaving both mysql.innodb_table_stats and mysql.innodb_index_stats with stale entries (n_rows=0). This causes the query optimizer to generate suboptimal plans since it believes the new partitions are empty. The only workaround is a manual ANALYZE TABLE after every reorganize. This commit fixes the bug https://bugs.mysql.com/bug.php?id=120318 This contribution is under the OCA signed by Amazon and covering submissions to the MySQL project. --- .../include/partition_reorganize_stats.inc | 678 ++++++++++++++++++ .../r/partition_reorganize_stats.result | 583 +++++++++++++++ ...artition_reorganize_stats_transient.result | 583 +++++++++++++++ .../innodb/t/partition_reorganize_stats.test | 62 ++ .../partition_reorganize_stats_transient.test | 27 + storage/innobase/handler/handler0alter.cc | 40 ++ 6 files changed, 1973 insertions(+) create mode 100644 mysql-test/suite/innodb/include/partition_reorganize_stats.inc create mode 100644 mysql-test/suite/innodb/r/partition_reorganize_stats.result create mode 100644 mysql-test/suite/innodb/r/partition_reorganize_stats_transient.result create mode 100644 mysql-test/suite/innodb/t/partition_reorganize_stats.test create mode 100644 mysql-test/suite/innodb/t/partition_reorganize_stats_transient.test diff --git a/mysql-test/suite/innodb/include/partition_reorganize_stats.inc b/mysql-test/suite/innodb/include/partition_reorganize_stats.inc new file mode 100644 index 000000000000..475d6eebb335 --- /dev/null +++ b/mysql-test/suite/innodb/include/partition_reorganize_stats.inc @@ -0,0 +1,678 @@ +# Common test logic for partition reorganize statistics. +# This file is sourced by both persistent and transient stats tests. +# +# Expected variables set by caller: +# $use_persistent_stats - 1 for persistent stats, 0 for transient stats +# +# Persistent stats verification: mysql.innodb_table_stats (n_rows column) +# Transient stats verification: INFORMATION_SCHEMA.PARTITIONS (TABLE_ROWS column) + +--echo # +--echo # Test 1: REORGANIZE PARTITION updates statistics correctly +--echo # + +CREATE TABLE sales_data ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + region VARCHAR(50), + status VARCHAR(20), + PRIMARY KEY (id, sale_date), + KEY idx_status (status) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023), + PARTITION p2023 VALUES LESS THAN (2024) +); + +--echo # Insert test data +INSERT INTO sales_data (sale_date, amount, region, status) VALUES +('2020-01-15', 1000.00, 'North', 'completed'), +('2020-06-20', 1500.00, 'South', 'completed'), +('2020-12-10', 2000.00, 'East', 'pending'), +('2021-03-05', 2500.00, 'West', 'completed'), +('2021-08-15', 3000.00, 'North', 'pending'), +('2021-11-25', 3500.00, 'South', 'completed'), +('2022-02-14', 4000.00, 'East', 'pending'), +('2022-07-04', 4500.00, 'West', 'completed'), +('2022-10-31', 5000.00, 'North', 'pending'), +('2023-01-20', 5500.00, 'South', 'completed'), +('2023-05-12', 6000.00, 'East', 'pending'), +('2023-09-08', 6500.00, 'West', 'completed'); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_data; + +--echo # Check statistics before REORGANIZE PARTITION +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_data%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_data' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # Verify actual row counts match statistics +SELECT + 'p2020' as partition_name, + COUNT(*) as actual_rows +FROM sales_data PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_data PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_data PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_data PARTITION (p2023) +ORDER BY partition_name; + +--echo # Perform REORGANIZE PARTITION (split p2022 and p2023 into smaller partitions) +ALTER TABLE sales_data REORGANIZE PARTITION p2022, p2023 INTO ( + PARTITION p2022 VALUES LESS THAN (2023), + PARTITION p2023 VALUES LESS THAN (2024), + PARTITION p2024 VALUES LESS THAN (2025) +); + +--echo # Check statistics AFTER REORGANIZE PARTITION (should show correct row counts) +--echo # Without the fix, the reorganized partitions (p2022, p2023) and the new +--echo # partition (p2024) would report n_rows=0 here, because their statistics +--echo # are never recalculated after the data is copied. With the fix, p2022 and +--echo # p2023 correctly show n_rows=3 and p2024 shows n_rows=0 (it is empty). +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_data%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_data' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # Verify actual row counts in ALL partitions (including untouched ones) +SELECT + 'p2020' as partition_name, + COUNT(*) as actual_rows +FROM sales_data PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_data PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_data PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_data PARTITION (p2023) +UNION ALL +SELECT 'p2024', COUNT(*) FROM sales_data PARTITION (p2024) +ORDER BY partition_name; + +--echo # Verify optimizer uses index statistics for query planning +EXPLAIN SELECT * FROM sales_data WHERE status = 'pending'; + +DROP TABLE sales_data; + +--echo # +--echo # Test 2: Subpartitioned tables with HASH subpartitions +--echo # +--echo # Test with subpartitioned table +CREATE TABLE sales_subpart ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + region VARCHAR(50), + PRIMARY KEY (id, sale_date, region) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022) +); + +--echo # Insert data into subpartitioned table +INSERT INTO sales_subpart (sale_date, amount, region) VALUES +('2020-01-15', 1000.00, 'North'), +('2020-06-20', 1500.00, 'South'), +('2020-12-10', 2000.00, 'East'), +('2021-03-05', 2500.00, 'West'), +('2021-08-15', 3000.00, 'North'), +('2021-11-25', 3500.00, 'South'); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_subpart; + +--echo # Check statistics before REORGANIZE on subpartitioned table +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_subpart%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_subpart' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +} + +--echo # REORGANIZE subpartitioned table (add new partition) +ALTER TABLE sales_subpart REORGANIZE PARTITION p2021 INTO ( + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023) +); + +--echo # Check statistics AFTER REORGANIZE on subpartitioned table +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_subpart%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_subpart' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +} + +--echo # +--echo # Test 3: REORGANIZE with MERGE (multiple partitions into one) +--echo # +CREATE TABLE sales_merge ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023) +); + +INSERT INTO sales_merge (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_merge; + +--echo # Check stats before REORGANIZE (merge) +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_merge%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_merge' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # Merge p2020 and p2021 into p2020_2021 +ALTER TABLE sales_merge REORGANIZE PARTITION p2020, p2021 INTO ( + PARTITION p2020_2021 VALUES LESS THAN (2022) +); + +--echo # Verify merged partition has correct statistics +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_merge%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_merge' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # Verify actual row count in merged partition +SELECT COUNT(*) as actual_rows FROM sales_merge PARTITION (p2020_2021); + +DROP TABLE sales_merge; +DROP TABLE sales_subpart; + +--echo # +--echo # Test 4: COALESCE PARTITION (HASH partitions) - PART_REORGED_DROPPED +--echo # +CREATE TABLE sales_coalesce ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY HASH(YEAR(sale_date)) +PARTITIONS 4; + +INSERT INTO sales_coalesce (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00), +('2023-01-20', 5500.00), +('2023-05-12', 6000.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_coalesce; + +--echo # Check stats before COALESCE +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_coalesce%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_coalesce' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # COALESCE PARTITION (reduce from 4 to 2 partitions) +ALTER TABLE sales_coalesce COALESCE PARTITION 2; + +--echo # Check stats after COALESCE (should show correct row counts) +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_coalesce%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_coalesce' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # Verify actual row counts +SELECT + 'p0' as partition_name, + COUNT(*) as actual_rows +FROM sales_coalesce PARTITION (p0) +UNION ALL +SELECT 'p1', COUNT(*) FROM sales_coalesce PARTITION (p1) +ORDER BY partition_name; + +DROP TABLE sales_coalesce; + +--echo # +--echo # Test 5: REORGANIZE with subpartitions +--echo # +CREATE TABLE sales_reorganize_sub ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + region VARCHAR(50), + PRIMARY KEY (id, sale_date, region) +) ENGINE=InnoDB +PARTITION BY RANGE(YEAR(sale_date)) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 +( + PARTITION p0 VALUES LESS THAN (2021), + PARTITION p1 VALUES LESS THAN (2022), + PARTITION p2 VALUES LESS THAN (2023), + PARTITION p3 VALUES LESS THAN MAXVALUE +); + +INSERT INTO sales_reorganize_sub (sale_date, amount, region) VALUES +('2020-01-15', 1000.00, 'North'), +('2020-06-20', 1500.00, 'South'), +('2021-03-05', 2500.00, 'West'), +('2021-08-15', 3000.00, 'East'), +('2022-02-14', 4000.00, 'North'), +('2022-07-04', 4500.00, 'South'); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_reorganize_sub; + +--echo # Check stats before REORGANIZE +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_reorganize_sub%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_reorganize_sub' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +} + +--echo # REORGANIZE subpartitioned table (merge p1 and p2) +ALTER TABLE sales_reorganize_sub REORGANIZE PARTITION p1, p2 INTO ( + PARTITION p1_2 VALUES LESS THAN (2023) +); + +--echo # Check stats after REORGANIZE on subpartitioned table +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_reorganize_sub%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_reorganize_sub' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +} + +DROP TABLE sales_reorganize_sub; + +--echo # +--echo # Test 6: ADD PARTITION (simple add without reorganize) +--echo # +CREATE TABLE sales_add ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022) +); + +INSERT INTO sales_add (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_add; + +--echo # Check stats before ADD PARTITION +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_add%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_add' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # ADD PARTITION (not reorganize) +ALTER TABLE sales_add ADD PARTITION ( + PARTITION p2022 VALUES LESS THAN (2023) +); + +INSERT INTO sales_add (sale_date, amount) VALUES +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); + +--echo # Ensure statistics are populated after insert +ANALYZE TABLE sales_add; + +--echo # Check stats after ADD PARTITION +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_add%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_add' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +DROP TABLE sales_add; + +--echo # +--echo # Test 7: REBUILD PARTITION +--echo # +CREATE TABLE sales_rebuild ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023) +); + +INSERT INTO sales_rebuild (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_rebuild; + +--echo # Check stats before REBUILD +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_rebuild%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_rebuild' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # REBUILD specific partition +ALTER TABLE sales_rebuild REBUILD PARTITION p2021; + +--echo # Check stats after REBUILD +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_rebuild%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_rebuild' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +DROP TABLE sales_rebuild; + +--echo # +--echo # Test 8: AUTO_INCREMENT with COALESCE PARTITION +--echo # +CREATE TABLE sales_autoinc ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id) +) ENGINE=InnoDB +PARTITION BY HASH(id) +PARTITIONS 4; + +INSERT INTO sales_autoinc (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_autoinc; + +--echo # Check stats before COALESCE +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_autoinc%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_autoinc' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # COALESCE with AUTO_INCREMENT +ALTER TABLE sales_autoinc COALESCE PARTITION 2; + +--echo # Check stats after COALESCE with AUTO_INCREMENT +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_autoinc%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_autoinc' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +DROP TABLE sales_autoinc; + +--echo # +--echo # Test 9: REORGANIZE with empty partitions +--echo # +CREATE TABLE sales_empty ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023) +); + +--echo # Insert data only in p2020, leave others empty +INSERT INTO sales_empty (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_empty; + +--echo # Check stats before REORGANIZE +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_empty%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_empty' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # REORGANIZE including empty partitions +ALTER TABLE sales_empty REORGANIZE PARTITION p2021, p2022 INTO ( + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023), + PARTITION p2023 VALUES LESS THAN (2024) +); + +--echo # Check stats after REORGANIZE with empty partitions +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_empty%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_empty' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +DROP TABLE sales_empty; + +--echo # +--echo # Test 10: Multiple operations with PART_NORMAL partitions +--echo # +CREATE TABLE sales_mixed ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023), + PARTITION p2023 VALUES LESS THAN (2024) +); + +INSERT INTO sales_mixed (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2023-01-20', 5500.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_mixed; + +--echo # Check stats before REORGANIZE +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_mixed%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_mixed' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # REORGANIZE only p2022 and p2023, leaving p2020 and p2021 as PART_NORMAL +ALTER TABLE sales_mixed REORGANIZE PARTITION p2022, p2023 INTO ( + PARTITION p2022 VALUES LESS THAN (2023), + PARTITION p2023 VALUES LESS THAN (2024), + PARTITION p2024 VALUES LESS THAN (2025) +); + +--echo # Check stats - PART_NORMAL partitions should retain their stats +if ($use_persistent_stats) +{ + --replace_column 3 # + SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_mixed%'; +} +if (!$use_persistent_stats) +{ +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_mixed' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +} + +--echo # Verify actual row counts in all partitions +SELECT + 'p2020' as partition_name, + COUNT(*) as actual_rows +FROM sales_mixed PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_mixed PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_mixed PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_mixed PARTITION (p2023) +UNION ALL +SELECT 'p2024', COUNT(*) FROM sales_mixed PARTITION (p2024) +ORDER BY partition_name; + +DROP TABLE sales_mixed; diff --git a/mysql-test/suite/innodb/r/partition_reorganize_stats.result b/mysql-test/suite/innodb/r/partition_reorganize_stats.result new file mode 100644 index 000000000000..e0248dc9cd10 --- /dev/null +++ b/mysql-test/suite/innodb/r/partition_reorganize_stats.result @@ -0,0 +1,583 @@ +SET @saved_innodb_stats_persistent = @@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent = 1; +# +# Test 1: REORGANIZE PARTITION updates statistics correctly +# +CREATE TABLE sales_data ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +region VARCHAR(50), +status VARCHAR(20), +PRIMARY KEY (id, sale_date), +KEY idx_status (status) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024) +); +# Insert test data +INSERT INTO sales_data (sale_date, amount, region, status) VALUES +('2020-01-15', 1000.00, 'North', 'completed'), +('2020-06-20', 1500.00, 'South', 'completed'), +('2020-12-10', 2000.00, 'East', 'pending'), +('2021-03-05', 2500.00, 'West', 'completed'), +('2021-08-15', 3000.00, 'North', 'pending'), +('2021-11-25', 3500.00, 'South', 'completed'), +('2022-02-14', 4000.00, 'East', 'pending'), +('2022-07-04', 4500.00, 'West', 'completed'), +('2022-10-31', 5000.00, 'North', 'pending'), +('2023-01-20', 5500.00, 'South', 'completed'), +('2023-05-12', 6000.00, 'East', 'pending'), +('2023-09-08', 6500.00, 'West', 'completed'); +# Ensure statistics are populated +ANALYZE TABLE sales_data; +Table Op Msg_type Msg_text +test.sales_data analyze status OK +# Check statistics before REORGANIZE PARTITION +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_data%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_data#p#p2020 # 3 1 1 +test sales_data#p#p2021 # 3 1 1 +test sales_data#p#p2022 # 3 1 1 +test sales_data#p#p2023 # 3 1 1 +# Verify actual row counts match statistics +SELECT +'p2020' as partition_name, +COUNT(*) as actual_rows +FROM sales_data PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_data PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_data PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_data PARTITION (p2023) +ORDER BY partition_name; +partition_name actual_rows +p2020 3 +p2021 3 +p2022 3 +p2023 3 +# Perform REORGANIZE PARTITION (split p2022 and p2023 into smaller partitions) +ALTER TABLE sales_data REORGANIZE PARTITION p2022, p2023 INTO ( +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024), +PARTITION p2024 VALUES LESS THAN (2025) +); +# Check statistics AFTER REORGANIZE PARTITION (should show correct row counts) +# Without the fix, the reorganized partitions (p2022, p2023) and the new +# partition (p2024) would report n_rows=0 here, because their statistics +# are never recalculated after the data is copied. With the fix, p2022 and +# p2023 correctly show n_rows=3 and p2024 shows n_rows=0 (it is empty). +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_data%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_data#p#p2020 # 3 1 1 +test sales_data#p#p2021 # 3 1 1 +test sales_data#p#p2022 # 3 1 1 +test sales_data#p#p2023 # 3 1 1 +test sales_data#p#p2024 # 0 1 1 +# Verify actual row counts in ALL partitions (including untouched ones) +SELECT +'p2020' as partition_name, +COUNT(*) as actual_rows +FROM sales_data PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_data PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_data PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_data PARTITION (p2023) +UNION ALL +SELECT 'p2024', COUNT(*) FROM sales_data PARTITION (p2024) +ORDER BY partition_name; +partition_name actual_rows +p2020 3 +p2021 3 +p2022 3 +p2023 3 +p2024 0 +# Verify optimizer uses index statistics for query planning +EXPLAIN SELECT * FROM sales_data WHERE status = 'pending'; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE sales_data p2020,p2021,p2022,p2023,p2024 ref idx_status idx_status 83 const 5 100.00 NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`sales_data`.`id` AS `id`,`test`.`sales_data`.`sale_date` AS `sale_date`,`test`.`sales_data`.`amount` AS `amount`,`test`.`sales_data`.`region` AS `region`,`test`.`sales_data`.`status` AS `status` from `test`.`sales_data` where (`test`.`sales_data`.`status` = 'pending') +DROP TABLE sales_data; +# +# Test 2: Subpartitioned tables with HASH subpartitions +# +# Test with subpartitioned table +CREATE TABLE sales_subpart ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +region VARCHAR(50), +PRIMARY KEY (id, sale_date, region) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022) +); +# Insert data into subpartitioned table +INSERT INTO sales_subpart (sale_date, amount, region) VALUES +('2020-01-15', 1000.00, 'North'), +('2020-06-20', 1500.00, 'South'), +('2020-12-10', 2000.00, 'East'), +('2021-03-05', 2500.00, 'West'), +('2021-08-15', 3000.00, 'North'), +('2021-11-25', 3500.00, 'South'); +# Ensure statistics are populated +ANALYZE TABLE sales_subpart; +Table Op Msg_type Msg_text +test.sales_subpart analyze status OK +# Check statistics before REORGANIZE on subpartitioned table +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_subpart%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_subpart#p#p2020#sp#p2020sp0 # 1 1 0 +test sales_subpart#p#p2020#sp#p2020sp1 # 2 1 0 +test sales_subpart#p#p2021#sp#p2021sp0 # 2 1 0 +test sales_subpart#p#p2021#sp#p2021sp1 # 1 1 0 +# REORGANIZE subpartitioned table (add new partition) +ALTER TABLE sales_subpart REORGANIZE PARTITION p2021 INTO ( +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +# Check statistics AFTER REORGANIZE on subpartitioned table +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_subpart%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_subpart#p#p2020#sp#p2020sp0 # 1 1 0 +test sales_subpart#p#p2020#sp#p2020sp1 # 2 1 0 +test sales_subpart#p#p2021#sp#p2021sp0 # 2 1 0 +test sales_subpart#p#p2021#sp#p2021sp1 # 1 1 0 +test sales_subpart#p#p2022#sp#p2022sp0 # 0 1 0 +test sales_subpart#p#p2022#sp#p2022sp1 # 0 1 0 +# +# Test 3: REORGANIZE with MERGE (multiple partitions into one) +# +CREATE TABLE sales_merge ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +INSERT INTO sales_merge (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_merge; +Table Op Msg_type Msg_text +test.sales_merge analyze status OK +# Check stats before REORGANIZE (merge) +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_merge%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_merge#p#p2020 # 2 1 0 +test sales_merge#p#p2021 # 2 1 0 +test sales_merge#p#p2022 # 2 1 0 +# Merge p2020 and p2021 into p2020_2021 +ALTER TABLE sales_merge REORGANIZE PARTITION p2020, p2021 INTO ( +PARTITION p2020_2021 VALUES LESS THAN (2022) +); +# Verify merged partition has correct statistics +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_merge%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_merge#p#p2020_2021 # 4 1 0 +test sales_merge#p#p2022 # 2 1 0 +# Verify actual row count in merged partition +SELECT COUNT(*) as actual_rows FROM sales_merge PARTITION (p2020_2021); +actual_rows +4 +DROP TABLE sales_merge; +DROP TABLE sales_subpart; +# +# Test 4: COALESCE PARTITION (HASH partitions) - PART_REORGED_DROPPED +# +CREATE TABLE sales_coalesce ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY HASH(YEAR(sale_date)) +PARTITIONS 4; +INSERT INTO sales_coalesce (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00), +('2023-01-20', 5500.00), +('2023-05-12', 6000.00); +# Ensure statistics are populated +ANALYZE TABLE sales_coalesce; +Table Op Msg_type Msg_text +test.sales_coalesce analyze status OK +# Check stats before COALESCE +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_coalesce%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_coalesce#p#p0 # 2 1 0 +test sales_coalesce#p#p1 # 2 1 0 +test sales_coalesce#p#p2 # 2 1 0 +test sales_coalesce#p#p3 # 2 1 0 +# COALESCE PARTITION (reduce from 4 to 2 partitions) +ALTER TABLE sales_coalesce COALESCE PARTITION 2; +# Check stats after COALESCE (should show correct row counts) +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_coalesce%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_coalesce#p#p0 # 4 1 0 +test sales_coalesce#p#p1 # 4 1 0 +# Verify actual row counts +SELECT +'p0' as partition_name, +COUNT(*) as actual_rows +FROM sales_coalesce PARTITION (p0) +UNION ALL +SELECT 'p1', COUNT(*) FROM sales_coalesce PARTITION (p1) +ORDER BY partition_name; +partition_name actual_rows +p0 4 +p1 4 +DROP TABLE sales_coalesce; +# +# Test 5: REORGANIZE with subpartitions +# +CREATE TABLE sales_reorganize_sub ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +region VARCHAR(50), +PRIMARY KEY (id, sale_date, region) +) ENGINE=InnoDB +PARTITION BY RANGE(YEAR(sale_date)) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 +( +PARTITION p0 VALUES LESS THAN (2021), +PARTITION p1 VALUES LESS THAN (2022), +PARTITION p2 VALUES LESS THAN (2023), +PARTITION p3 VALUES LESS THAN MAXVALUE +); +INSERT INTO sales_reorganize_sub (sale_date, amount, region) VALUES +('2020-01-15', 1000.00, 'North'), +('2020-06-20', 1500.00, 'South'), +('2021-03-05', 2500.00, 'West'), +('2021-08-15', 3000.00, 'East'), +('2022-02-14', 4000.00, 'North'), +('2022-07-04', 4500.00, 'South'); +# Ensure statistics are populated +ANALYZE TABLE sales_reorganize_sub; +Table Op Msg_type Msg_text +test.sales_reorganize_sub analyze status OK +# Check stats before REORGANIZE +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_reorganize_sub%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_reorganize_sub#p#p0#sp#p0sp0 # 1 1 0 +test sales_reorganize_sub#p#p0#sp#p0sp1 # 1 1 0 +test sales_reorganize_sub#p#p1#sp#p1sp0 # 1 1 0 +test sales_reorganize_sub#p#p1#sp#p1sp1 # 1 1 0 +test sales_reorganize_sub#p#p2#sp#p2sp0 # 1 1 0 +test sales_reorganize_sub#p#p2#sp#p2sp1 # 1 1 0 +test sales_reorganize_sub#p#p3#sp#p3sp0 # 0 1 0 +test sales_reorganize_sub#p#p3#sp#p3sp1 # 0 1 0 +# REORGANIZE subpartitioned table (merge p1 and p2) +ALTER TABLE sales_reorganize_sub REORGANIZE PARTITION p1, p2 INTO ( +PARTITION p1_2 VALUES LESS THAN (2023) +); +# Check stats after REORGANIZE on subpartitioned table +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_reorganize_sub%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_reorganize_sub#p#p0#sp#p0sp0 # 1 1 0 +test sales_reorganize_sub#p#p0#sp#p0sp1 # 1 1 0 +test sales_reorganize_sub#p#p1_2#sp#p1_2sp0 # 2 1 0 +test sales_reorganize_sub#p#p1_2#sp#p1_2sp1 # 2 1 0 +test sales_reorganize_sub#p#p3#sp#p3sp0 # 0 1 0 +test sales_reorganize_sub#p#p3#sp#p3sp1 # 0 1 0 +DROP TABLE sales_reorganize_sub; +# +# Test 6: ADD PARTITION (simple add without reorganize) +# +CREATE TABLE sales_add ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022) +); +INSERT INTO sales_add (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00); +# Ensure statistics are populated +ANALYZE TABLE sales_add; +Table Op Msg_type Msg_text +test.sales_add analyze status OK +# Check stats before ADD PARTITION +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_add%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_add#p#p2020 # 2 1 0 +test sales_add#p#p2021 # 2 1 0 +# ADD PARTITION (not reorganize) +ALTER TABLE sales_add ADD PARTITION ( +PARTITION p2022 VALUES LESS THAN (2023) +); +INSERT INTO sales_add (sale_date, amount) VALUES +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); +# Ensure statistics are populated after insert +ANALYZE TABLE sales_add; +Table Op Msg_type Msg_text +test.sales_add analyze status OK +# Check stats after ADD PARTITION +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_add%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_add#p#p2020 # 2 1 0 +test sales_add#p#p2021 # 2 1 0 +test sales_add#p#p2022 # 2 1 0 +DROP TABLE sales_add; +# +# Test 7: REBUILD PARTITION +# +CREATE TABLE sales_rebuild ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +INSERT INTO sales_rebuild (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00); +# Ensure statistics are populated +ANALYZE TABLE sales_rebuild; +Table Op Msg_type Msg_text +test.sales_rebuild analyze status OK +# Check stats before REBUILD +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_rebuild%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_rebuild#p#p2020 # 2 1 0 +test sales_rebuild#p#p2021 # 2 1 0 +test sales_rebuild#p#p2022 # 1 1 0 +# REBUILD specific partition +ALTER TABLE sales_rebuild REBUILD PARTITION p2021; +# Check stats after REBUILD +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_rebuild%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_rebuild#p#p2020 # 2 1 0 +test sales_rebuild#p#p2021 # 2 1 0 +test sales_rebuild#p#p2022 # 1 1 0 +DROP TABLE sales_rebuild; +# +# Test 8: AUTO_INCREMENT with COALESCE PARTITION +# +CREATE TABLE sales_autoinc ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id) +) ENGINE=InnoDB +PARTITION BY HASH(id) +PARTITIONS 4; +INSERT INTO sales_autoinc (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_autoinc; +Table Op Msg_type Msg_text +test.sales_autoinc analyze status OK +# Check stats before COALESCE +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_autoinc%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_autoinc#p#p0 # 1 1 0 +test sales_autoinc#p#p1 # 2 1 0 +test sales_autoinc#p#p2 # 2 1 0 +test sales_autoinc#p#p3 # 1 1 0 +# COALESCE with AUTO_INCREMENT +ALTER TABLE sales_autoinc COALESCE PARTITION 2; +# Check stats after COALESCE with AUTO_INCREMENT +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_autoinc%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_autoinc#p#p0 # 3 1 0 +test sales_autoinc#p#p1 # 3 1 0 +DROP TABLE sales_autoinc; +# +# Test 9: REORGANIZE with empty partitions +# +CREATE TABLE sales_empty ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +# Insert data only in p2020, leave others empty +INSERT INTO sales_empty (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_empty; +Table Op Msg_type Msg_text +test.sales_empty analyze status OK +# Check stats before REORGANIZE +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_empty%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_empty#p#p2020 # 2 1 0 +test sales_empty#p#p2021 # 0 1 0 +test sales_empty#p#p2022 # 0 1 0 +# REORGANIZE including empty partitions +ALTER TABLE sales_empty REORGANIZE PARTITION p2021, p2022 INTO ( +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024) +); +# Check stats after REORGANIZE with empty partitions +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_empty%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_empty#p#p2020 # 2 1 0 +test sales_empty#p#p2021 # 0 1 0 +test sales_empty#p#p2022 # 0 1 0 +test sales_empty#p#p2023 # 0 1 0 +DROP TABLE sales_empty; +# +# Test 10: Multiple operations with PART_NORMAL partitions +# +CREATE TABLE sales_mixed ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024) +); +INSERT INTO sales_mixed (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2023-01-20', 5500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_mixed; +Table Op Msg_type Msg_text +test.sales_mixed analyze status OK +# Check stats before REORGANIZE +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_mixed%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_mixed#p#p2020 # 2 1 0 +test sales_mixed#p#p2021 # 2 1 0 +test sales_mixed#p#p2022 # 1 1 0 +test sales_mixed#p#p2023 # 1 1 0 +# REORGANIZE only p2022 and p2023, leaving p2020 and p2021 as PART_NORMAL +ALTER TABLE sales_mixed REORGANIZE PARTITION p2022, p2023 INTO ( +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024), +PARTITION p2024 VALUES LESS THAN (2025) +); +# Check stats - PART_NORMAL partitions should retain their stats +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_mixed%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_mixed#p#p2020 # 2 1 0 +test sales_mixed#p#p2021 # 2 1 0 +test sales_mixed#p#p2022 # 1 1 0 +test sales_mixed#p#p2023 # 1 1 0 +test sales_mixed#p#p2024 # 0 1 0 +# Verify actual row counts in all partitions +SELECT +'p2020' as partition_name, +COUNT(*) as actual_rows +FROM sales_mixed PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_mixed PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_mixed PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_mixed PARTITION (p2023) +UNION ALL +SELECT 'p2024', COUNT(*) FROM sales_mixed PARTITION (p2024) +ORDER BY partition_name; +partition_name actual_rows +p2020 2 +p2021 2 +p2022 1 +p2023 1 +p2024 0 +DROP TABLE sales_mixed; +# +# Test 11: Statistics persistence after server restart +# +CREATE TABLE sales_restart ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022) +); +INSERT INTO sales_restart (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2020-12-10', 2000.00), +('2021-03-05', 2500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_restart; +Table Op Msg_type Msg_text +test.sales_restart analyze status OK +ALTER TABLE sales_restart REORGANIZE PARTITION p2021 INTO ( +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +# Check stats before restart +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_restart%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_restart#p#p2020 # 3 1 0 +test sales_restart#p#p2021 # 1 1 0 +test sales_restart#p#p2022 # 0 1 0 +SET GLOBAL innodb_stats_persistent = @saved_innodb_stats_persistent; +# restart +# Check stats after restart (should still be correct) +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_restart%'; +database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes +test sales_restart#p#p2020 # 3 1 0 +test sales_restart#p#p2021 # 1 1 0 +test sales_restart#p#p2022 # 0 1 0 +DROP TABLE sales_restart; +# +# End of test +# diff --git a/mysql-test/suite/innodb/r/partition_reorganize_stats_transient.result b/mysql-test/suite/innodb/r/partition_reorganize_stats_transient.result new file mode 100644 index 000000000000..4d56741eaa23 --- /dev/null +++ b/mysql-test/suite/innodb/r/partition_reorganize_stats_transient.result @@ -0,0 +1,583 @@ +SET @saved_innodb_stats_persistent = @@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent = 0; +# +# Test 1: REORGANIZE PARTITION updates statistics correctly +# +CREATE TABLE sales_data ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +region VARCHAR(50), +status VARCHAR(20), +PRIMARY KEY (id, sale_date), +KEY idx_status (status) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024) +); +# Insert test data +INSERT INTO sales_data (sale_date, amount, region, status) VALUES +('2020-01-15', 1000.00, 'North', 'completed'), +('2020-06-20', 1500.00, 'South', 'completed'), +('2020-12-10', 2000.00, 'East', 'pending'), +('2021-03-05', 2500.00, 'West', 'completed'), +('2021-08-15', 3000.00, 'North', 'pending'), +('2021-11-25', 3500.00, 'South', 'completed'), +('2022-02-14', 4000.00, 'East', 'pending'), +('2022-07-04', 4500.00, 'West', 'completed'), +('2022-10-31', 5000.00, 'North', 'pending'), +('2023-01-20', 5500.00, 'South', 'completed'), +('2023-05-12', 6000.00, 'East', 'pending'), +('2023-09-08', 6500.00, 'West', 'completed'); +# Ensure statistics are populated +ANALYZE TABLE sales_data; +Table Op Msg_type Msg_text +test.sales_data analyze status OK +# Check statistics before REORGANIZE PARTITION +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_data' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 3 +p2021 3 +p2022 3 +p2023 3 +# Verify actual row counts match statistics +SELECT +'p2020' as partition_name, +COUNT(*) as actual_rows +FROM sales_data PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_data PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_data PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_data PARTITION (p2023) +ORDER BY partition_name; +partition_name actual_rows +p2020 3 +p2021 3 +p2022 3 +p2023 3 +# Perform REORGANIZE PARTITION (split p2022 and p2023 into smaller partitions) +ALTER TABLE sales_data REORGANIZE PARTITION p2022, p2023 INTO ( +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024), +PARTITION p2024 VALUES LESS THAN (2025) +); +# Check statistics AFTER REORGANIZE PARTITION (should show correct row counts) +# Without the fix, the reorganized partitions (p2022, p2023) and the new +# partition (p2024) would report n_rows=0 here, because their statistics +# are never recalculated after the data is copied. With the fix, p2022 and +# p2023 correctly show n_rows=3 and p2024 shows n_rows=0 (it is empty). +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_data' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 3 +p2021 3 +p2022 3 +p2023 3 +p2024 0 +# Verify actual row counts in ALL partitions (including untouched ones) +SELECT +'p2020' as partition_name, +COUNT(*) as actual_rows +FROM sales_data PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_data PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_data PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_data PARTITION (p2023) +UNION ALL +SELECT 'p2024', COUNT(*) FROM sales_data PARTITION (p2024) +ORDER BY partition_name; +partition_name actual_rows +p2020 3 +p2021 3 +p2022 3 +p2023 3 +p2024 0 +# Verify optimizer uses index statistics for query planning +EXPLAIN SELECT * FROM sales_data WHERE status = 'pending'; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE sales_data p2020,p2021,p2022,p2023,p2024 ref idx_status idx_status 83 const 5 100.00 NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`sales_data`.`id` AS `id`,`test`.`sales_data`.`sale_date` AS `sale_date`,`test`.`sales_data`.`amount` AS `amount`,`test`.`sales_data`.`region` AS `region`,`test`.`sales_data`.`status` AS `status` from `test`.`sales_data` where (`test`.`sales_data`.`status` = 'pending') +DROP TABLE sales_data; +# +# Test 2: Subpartitioned tables with HASH subpartitions +# +# Test with subpartitioned table +CREATE TABLE sales_subpart ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +region VARCHAR(50), +PRIMARY KEY (id, sale_date, region) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022) +); +# Insert data into subpartitioned table +INSERT INTO sales_subpart (sale_date, amount, region) VALUES +('2020-01-15', 1000.00, 'North'), +('2020-06-20', 1500.00, 'South'), +('2020-12-10', 2000.00, 'East'), +('2021-03-05', 2500.00, 'West'), +('2021-08-15', 3000.00, 'North'), +('2021-11-25', 3500.00, 'South'); +# Ensure statistics are populated +ANALYZE TABLE sales_subpart; +Table Op Msg_type Msg_text +test.sales_subpart analyze status OK +# Check statistics before REORGANIZE on subpartitioned table +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_subpart' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS +p2020 p2020sp0 1 +p2020 p2020sp1 2 +p2021 p2021sp0 2 +p2021 p2021sp1 1 +# REORGANIZE subpartitioned table (add new partition) +ALTER TABLE sales_subpart REORGANIZE PARTITION p2021 INTO ( +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +# Check statistics AFTER REORGANIZE on subpartitioned table +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_subpart' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS +p2020 p2020sp0 1 +p2020 p2020sp1 2 +p2021 p2021sp0 2 +p2021 p2021sp1 1 +p2022 p2022sp0 0 +p2022 p2022sp1 0 +# +# Test 3: REORGANIZE with MERGE (multiple partitions into one) +# +CREATE TABLE sales_merge ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +INSERT INTO sales_merge (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_merge; +Table Op Msg_type Msg_text +test.sales_merge analyze status OK +# Check stats before REORGANIZE (merge) +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_merge' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 2 +p2022 2 +# Merge p2020 and p2021 into p2020_2021 +ALTER TABLE sales_merge REORGANIZE PARTITION p2020, p2021 INTO ( +PARTITION p2020_2021 VALUES LESS THAN (2022) +); +# Verify merged partition has correct statistics +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_merge' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020_2021 4 +p2022 2 +# Verify actual row count in merged partition +SELECT COUNT(*) as actual_rows FROM sales_merge PARTITION (p2020_2021); +actual_rows +4 +DROP TABLE sales_merge; +DROP TABLE sales_subpart; +# +# Test 4: COALESCE PARTITION (HASH partitions) - PART_REORGED_DROPPED +# +CREATE TABLE sales_coalesce ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY HASH(YEAR(sale_date)) +PARTITIONS 4; +INSERT INTO sales_coalesce (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00), +('2023-01-20', 5500.00), +('2023-05-12', 6000.00); +# Ensure statistics are populated +ANALYZE TABLE sales_coalesce; +Table Op Msg_type Msg_text +test.sales_coalesce analyze status OK +# Check stats before COALESCE +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_coalesce' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p0 2 +p1 2 +p2 2 +p3 2 +# COALESCE PARTITION (reduce from 4 to 2 partitions) +ALTER TABLE sales_coalesce COALESCE PARTITION 2; +# Check stats after COALESCE (should show correct row counts) +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_coalesce' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p0 4 +p1 4 +# Verify actual row counts +SELECT +'p0' as partition_name, +COUNT(*) as actual_rows +FROM sales_coalesce PARTITION (p0) +UNION ALL +SELECT 'p1', COUNT(*) FROM sales_coalesce PARTITION (p1) +ORDER BY partition_name; +partition_name actual_rows +p0 4 +p1 4 +DROP TABLE sales_coalesce; +# +# Test 5: REORGANIZE with subpartitions +# +CREATE TABLE sales_reorganize_sub ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +region VARCHAR(50), +PRIMARY KEY (id, sale_date, region) +) ENGINE=InnoDB +PARTITION BY RANGE(YEAR(sale_date)) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 +( +PARTITION p0 VALUES LESS THAN (2021), +PARTITION p1 VALUES LESS THAN (2022), +PARTITION p2 VALUES LESS THAN (2023), +PARTITION p3 VALUES LESS THAN MAXVALUE +); +INSERT INTO sales_reorganize_sub (sale_date, amount, region) VALUES +('2020-01-15', 1000.00, 'North'), +('2020-06-20', 1500.00, 'South'), +('2021-03-05', 2500.00, 'West'), +('2021-08-15', 3000.00, 'East'), +('2022-02-14', 4000.00, 'North'), +('2022-07-04', 4500.00, 'South'); +# Ensure statistics are populated +ANALYZE TABLE sales_reorganize_sub; +Table Op Msg_type Msg_text +test.sales_reorganize_sub analyze status OK +# Check stats before REORGANIZE +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_reorganize_sub' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS +p0 p0sp0 1 +p0 p0sp1 1 +p1 p1sp0 1 +p1 p1sp1 1 +p2 p2sp0 1 +p2 p2sp1 1 +p3 p3sp0 0 +p3 p3sp1 0 +# REORGANIZE subpartitioned table (merge p1 and p2) +ALTER TABLE sales_reorganize_sub REORGANIZE PARTITION p1, p2 INTO ( +PARTITION p1_2 VALUES LESS THAN (2023) +); +# Check stats after REORGANIZE on subpartitioned table +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_reorganize_sub' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME, SUBPARTITION_NAME; +PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS +p0 p0sp0 1 +p0 p0sp1 1 +p1_2 p1_2sp0 2 +p1_2 p1_2sp1 2 +p3 p3sp0 0 +p3 p3sp1 0 +DROP TABLE sales_reorganize_sub; +# +# Test 6: ADD PARTITION (simple add without reorganize) +# +CREATE TABLE sales_add ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022) +); +INSERT INTO sales_add (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00); +# Ensure statistics are populated +ANALYZE TABLE sales_add; +Table Op Msg_type Msg_text +test.sales_add analyze status OK +# Check stats before ADD PARTITION +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_add' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 2 +# ADD PARTITION (not reorganize) +ALTER TABLE sales_add ADD PARTITION ( +PARTITION p2022 VALUES LESS THAN (2023) +); +INSERT INTO sales_add (sale_date, amount) VALUES +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); +# Ensure statistics are populated after insert +ANALYZE TABLE sales_add; +Table Op Msg_type Msg_text +test.sales_add analyze status OK +# Check stats after ADD PARTITION +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_add' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 2 +p2022 2 +DROP TABLE sales_add; +# +# Test 7: REBUILD PARTITION +# +CREATE TABLE sales_rebuild ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +INSERT INTO sales_rebuild (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00); +# Ensure statistics are populated +ANALYZE TABLE sales_rebuild; +Table Op Msg_type Msg_text +test.sales_rebuild analyze status OK +# Check stats before REBUILD +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_rebuild' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 2 +p2022 1 +# REBUILD specific partition +ALTER TABLE sales_rebuild REBUILD PARTITION p2021; +# Check stats after REBUILD +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_rebuild' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 2 +p2022 1 +DROP TABLE sales_rebuild; +# +# Test 8: AUTO_INCREMENT with COALESCE PARTITION +# +CREATE TABLE sales_autoinc ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id) +) ENGINE=InnoDB +PARTITION BY HASH(id) +PARTITIONS 4; +INSERT INTO sales_autoinc (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2022-07-04', 4500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_autoinc; +Table Op Msg_type Msg_text +test.sales_autoinc analyze status OK +# Check stats before COALESCE +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_autoinc' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p0 1 +p1 2 +p2 2 +p3 1 +# COALESCE with AUTO_INCREMENT +ALTER TABLE sales_autoinc COALESCE PARTITION 2; +# Check stats after COALESCE with AUTO_INCREMENT +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_autoinc' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p0 3 +p1 3 +DROP TABLE sales_autoinc; +# +# Test 9: REORGANIZE with empty partitions +# +CREATE TABLE sales_empty ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023) +); +# Insert data only in p2020, leave others empty +INSERT INTO sales_empty (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_empty; +Table Op Msg_type Msg_text +test.sales_empty analyze status OK +# Check stats before REORGANIZE +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_empty' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 0 +p2022 0 +# REORGANIZE including empty partitions +ALTER TABLE sales_empty REORGANIZE PARTITION p2021, p2022 INTO ( +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024) +); +# Check stats after REORGANIZE with empty partitions +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_empty' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 0 +p2022 0 +p2023 0 +DROP TABLE sales_empty; +# +# Test 10: Multiple operations with PART_NORMAL partitions +# +CREATE TABLE sales_mixed ( +id INT AUTO_INCREMENT, +sale_date DATE NOT NULL, +amount DECIMAL(10,2), +PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( +PARTITION p2020 VALUES LESS THAN (2021), +PARTITION p2021 VALUES LESS THAN (2022), +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024) +); +INSERT INTO sales_mixed (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2021-03-05', 2500.00), +('2021-08-15', 3000.00), +('2022-02-14', 4000.00), +('2023-01-20', 5500.00); +# Ensure statistics are populated +ANALYZE TABLE sales_mixed; +Table Op Msg_type Msg_text +test.sales_mixed analyze status OK +# Check stats before REORGANIZE +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_mixed' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 2 +p2022 1 +p2023 1 +# REORGANIZE only p2022 and p2023, leaving p2020 and p2021 as PART_NORMAL +ALTER TABLE sales_mixed REORGANIZE PARTITION p2022, p2023 INTO ( +PARTITION p2022 VALUES LESS THAN (2023), +PARTITION p2023 VALUES LESS THAN (2024), +PARTITION p2024 VALUES LESS THAN (2025) +); +# Check stats - PART_NORMAL partitions should retain their stats +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 'sales_mixed' AND TABLE_SCHEMA = 'test' +ORDER BY PARTITION_NAME; +PARTITION_NAME TABLE_ROWS +p2020 2 +p2021 2 +p2022 1 +p2023 1 +p2024 0 +# Verify actual row counts in all partitions +SELECT +'p2020' as partition_name, +COUNT(*) as actual_rows +FROM sales_mixed PARTITION (p2020) +UNION ALL +SELECT 'p2021', COUNT(*) FROM sales_mixed PARTITION (p2021) +UNION ALL +SELECT 'p2022', COUNT(*) FROM sales_mixed PARTITION (p2022) +UNION ALL +SELECT 'p2023', COUNT(*) FROM sales_mixed PARTITION (p2023) +UNION ALL +SELECT 'p2024', COUNT(*) FROM sales_mixed PARTITION (p2024) +ORDER BY partition_name; +partition_name actual_rows +p2020 2 +p2021 2 +p2022 1 +p2023 1 +p2024 0 +DROP TABLE sales_mixed; +SET GLOBAL innodb_stats_persistent = @saved_innodb_stats_persistent; +# +# End of test +# diff --git a/mysql-test/suite/innodb/t/partition_reorganize_stats.test b/mysql-test/suite/innodb/t/partition_reorganize_stats.test new file mode 100644 index 000000000000..97339d7b2a12 --- /dev/null +++ b/mysql-test/suite/innodb/t/partition_reorganize_stats.test @@ -0,0 +1,62 @@ +# Test for Bug: ALTER TABLE REORGANIZE PARTITION does not update partition statistics +# After REORGANIZE PARTITION, partition statistics should reflect actual row counts +# without requiring manual ANALYZE TABLE +# +# This test verifies persistent stats mode (innodb_stats_persistent=ON). +# Common test cases are in partition_reorganize_stats.inc. +# Additional persistent-stats-specific test (restart) is inline below. + +--source include/have_innodb_max_16k.inc + +# Ensure we're using persistent stats +SET @saved_innodb_stats_persistent = @@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent = 1; + +# Source common tests (1-10) +--let $use_persistent_stats = 1 +--source suite/innodb/include/partition_reorganize_stats.inc + +--echo # +--echo # Test 11: Statistics persistence after server restart +--echo # +CREATE TABLE sales_restart ( + id INT AUTO_INCREMENT, + sale_date DATE NOT NULL, + amount DECIMAL(10,2), + PRIMARY KEY (id, sale_date) +) ENGINE=InnoDB +PARTITION BY RANGE (YEAR(sale_date)) ( + PARTITION p2020 VALUES LESS THAN (2021), + PARTITION p2021 VALUES LESS THAN (2022) +); + +INSERT INTO sales_restart (sale_date, amount) VALUES +('2020-01-15', 1000.00), +('2020-06-20', 1500.00), +('2020-12-10', 2000.00), +('2021-03-05', 2500.00); + +--echo # Ensure statistics are populated +ANALYZE TABLE sales_restart; + +ALTER TABLE sales_restart REORGANIZE PARTITION p2021 INTO ( + PARTITION p2021 VALUES LESS THAN (2022), + PARTITION p2022 VALUES LESS THAN (2023) +); + +--echo # Check stats before restart +--replace_column 3 # +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_restart%'; + +SET GLOBAL innodb_stats_persistent = @saved_innodb_stats_persistent; +--source include/restart_mysqld.inc + +--echo # Check stats after restart (should still be correct) +--replace_column 3 # +SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE '%sales_restart%'; + +DROP TABLE sales_restart; + +--echo # +--echo # End of test +--echo # diff --git a/mysql-test/suite/innodb/t/partition_reorganize_stats_transient.test b/mysql-test/suite/innodb/t/partition_reorganize_stats_transient.test new file mode 100644 index 000000000000..f9fd52722c47 --- /dev/null +++ b/mysql-test/suite/innodb/t/partition_reorganize_stats_transient.test @@ -0,0 +1,27 @@ +# Test for Bug: ALTER TABLE REORGANIZE PARTITION does not update partition statistics +# Transient stats mode (innodb_stats_persistent=OFF) +# +# This test verifies that after REORGANIZE PARTITION, partition statistics +# are automatically recalculated via the transient stats path +# (DICT_STATS_RECALC_TRANSIENT) when innodb_stats_persistent is disabled. +# +# Verification uses INFORMATION_SCHEMA.PARTITIONS (TABLE_ROWS) since +# transient stats are only held in memory and not written to +# mysql.innodb_table_stats. + +--source include/have_innodb_max_16k.inc + +# Use transient stats +SET @saved_innodb_stats_persistent = @@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent = 0; + +# Source common tests (1-10) +--let $use_persistent_stats = 0 +--source suite/innodb/include/partition_reorganize_stats.inc + +# Restore +SET GLOBAL innodb_stats_persistent = @saved_innodb_stats_persistent; + +--echo # +--echo # End of test +--echo # diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 59c45dc2bf32..fdc46a550203 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -10786,6 +10786,46 @@ bool ha_innopart::inplace_alter_partition(Alter_inplace_info *ha_alter_info) { if (res > 0) { print_error(res, MYF(res != ER_OUTOFMEMORY ? 0 : ME_FATALERROR)); + } else { + ut_a(m_new_partitions); + + /* After copying data to new partitions, recalculate statistics + for each modified partition so the optimizer has accurate stats. */ + + /* Lambda to recalculate stats for a single partition and warn on + failure. Deduplicates the logic shared between the regular and + subpartitioned code paths. */ + auto recalc_partition_stats = [&](uint part_idx) { + dict_table_t *part_table = m_new_partitions->part(part_idx); + /* m_new_partitions is initialized for all old partitions (num_parts * + num_subparts) but only changed/added partitions are populated + in it through set_part() call. That means, unchanged (PART_NORMAL) + and dropped (PART_REORGED_DROPPED) needs to be skipped. */ + if (part_table != nullptr) { + dberr_t ret = dict_stats_update( + part_table, dict_stats_is_persistent_enabled(part_table) + ? DICT_STATS_RECALC_PERSISTENT + : DICT_STATS_RECALC_TRANSIENT); + if (ret != DB_SUCCESS) { + push_warning_printf( + ha_thd(), Sql_condition::SL_WARNING, ER_ALTER_INFO, + "Error updating stats for partition '%s'" + " after reorganize: %s", + part_table->name.m_name, ut_strerr(ret)); + } + } + }; + + /* Compute total partition count, accounting for subpartitions. */ + partition_info *part_info = ha_alter_info->modified_part_info; + uint total_parts = part_info->num_parts; + if (part_info->is_sub_partitioned()) { + total_parts *= part_info->num_subparts; + } + + for (uint i = 0; i < total_parts; i++) { + recalc_partition_stats(i); + } } return (res);