Description:
The documentation describes the column DATA_FREE as:
"The number of bytes allocated to the partition or subpartition but not used."
But it always returns 0 value. This value does not correspond to the equivalent value in INFORMATION_SCHEMA.TABLES.
How to repeat:
Reproduce the steps in this session:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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 part_test;
Query OK, 1 row affected (0,00 sec)
mysql> use part_test;
Database changed
mysql> create table part_test (id integer, sample_text varchar(256)) partition by range (id) (partition p0 values less than (1000000), partition p1 values less than maxvalue);
Query OK, 0 rows affected (0,02 sec)
mysql> set @@cte_max_recursion_depth=100000;
Query OK, 0 rows affected (0,00 sec)
mysql> insert into part_test(with recursive base_table as
-> (
-> select 1 id,
-> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' sample_text
-> union all
-> select 1+id,
-> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' sample_text
-> from base_table
-> where id < 100000
-> )
-> select * from base_table);
Query OK, 100000 rows affected (0,67 sec)
Records: 100000 Duplicates: 0 Warnings: 0
mysql> analyze table part_test;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| part_test.part_test | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0,01 sec)
mysql> alter table part_test analyze partition p0;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| part_test.part_test | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0,00 sec)
mysql> select data_length, index_length,data_free from information_schema.tables where table_name = 'part_test' and table_schema='part_test';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 14172160 | 0 | 4194304 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 14172160 | 0 | 0 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p1';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 16384 | 0 | 0 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql> delete from part_test;
Query OK, 100000 rows affected (0,42 sec)
mysql> select data_length, index_length,data_free from information_schema.tables where table_name = 'part_test' and table_schema='part_test';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 14172160 | 0 | 4194304 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 16384 | 0 | 0 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql> analyze table part_test;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| part_test.part_test | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0,00 sec)
mysql> select data_length, index_length,data_free from information_schema.tables where table_name = 'part_test' and table_schema='part_test';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 32768 | 0 | 17825792 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 16384 | 0 | 0 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql> alter table part_test analyze partition p0;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| part_test.part_test | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0,00 sec)
mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 16384 | 0 | 0 |
+-------------+--------------+-----------+
1 row in set (0,01 sec)
mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p1';
+-------------+--------------+-----------+
| DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+-------------+--------------+-----------+
| 16384 | 0 | 0 |
+-------------+--------------+-----------+
1 row in set (0,00 sec)
mysql>
Suggested fix:
Use the same calculation method for PARTITIONS that is being used in TABLES.
Description: The documentation describes the column DATA_FREE as: "The number of bytes allocated to the partition or subpartition but not used." But it always returns 0 value. This value does not correspond to the equivalent value in INFORMATION_SCHEMA.TABLES. How to repeat: Reproduce the steps in this session: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.34 MySQL Community Server - GPL Copyright (c) 2000, 2023, 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 part_test; Query OK, 1 row affected (0,00 sec) mysql> use part_test; Database changed mysql> create table part_test (id integer, sample_text varchar(256)) partition by range (id) (partition p0 values less than (1000000), partition p1 values less than maxvalue); Query OK, 0 rows affected (0,02 sec) mysql> set @@cte_max_recursion_depth=100000; Query OK, 0 rows affected (0,00 sec) mysql> insert into part_test(with recursive base_table as -> ( -> select 1 id, -> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' sample_text -> union all -> select 1+id, -> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' sample_text -> from base_table -> where id < 100000 -> ) -> select * from base_table); Query OK, 100000 rows affected (0,67 sec) Records: 100000 Duplicates: 0 Warnings: 0 mysql> analyze table part_test; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | part_test.part_test | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0,01 sec) mysql> alter table part_test analyze partition p0; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | part_test.part_test | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0,00 sec) mysql> select data_length, index_length,data_free from information_schema.tables where table_name = 'part_test' and table_schema='part_test'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 14172160 | 0 | 4194304 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 14172160 | 0 | 0 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p1'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 16384 | 0 | 0 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> delete from part_test; Query OK, 100000 rows affected (0,42 sec) mysql> select data_length, index_length,data_free from information_schema.tables where table_name = 'part_test' and table_schema='part_test'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 14172160 | 0 | 4194304 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 16384 | 0 | 0 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> analyze table part_test; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | part_test.part_test | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0,00 sec) mysql> select data_length, index_length,data_free from information_schema.tables where table_name = 'part_test' and table_schema='part_test'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 32768 | 0 | 17825792 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 16384 | 0 | 0 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> alter table part_test analyze partition p0; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | part_test.part_test | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0,00 sec) mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p0'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 16384 | 0 | 0 | +-------------+--------------+-----------+ 1 row in set (0,01 sec) mysql> select data_length, index_length,data_free from information_schema.partitions where table_name = 'part_test' and table_schema='part_test' and partition_name='p1'; +-------------+--------------+-----------+ | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +-------------+--------------+-----------+ | 16384 | 0 | 0 | +-------------+--------------+-----------+ 1 row in set (0,00 sec) mysql> Suggested fix: Use the same calculation method for PARTITIONS that is being used in TABLES.