# Possible Bug in DATA\_FREE Reporting for Partitioned Tables

**URL:** <https://forums.percona.com/t/possible-bug-in-data-free-reporting-for-partitioned-tables/26745>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [November 23, 2023, 1:34pm UTC](https://forums.percona.com/t/possible-bug-in-data-free-reporting-for-partitioned-tables/26745 "2023-11-23T13:34:05Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![msutic](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/msutic/32/4218_2.png) [@msutic](https://forums.percona.com/u/msutic)\
**Post date:** [November 23, 2023, 1:34pm UTC](https://forums.percona.com/t/possible-bug-in-data-free-reporting-for-partitioned-tables/26745/1 "2023-11-23T13:34:05Z")

</div>

Hello,

I’ve encountered a potential issue with the reporting of the `DATA_FREE` value for partitioned tables in MySQL ( **5.7.42-46** ).

When I run `SHOW TABLE STATUS LIKE 'testtab';` , I get a `DATA_FREE` value of `47555018752` .

However, when I query the `information_schema.PARTITIONS` table with the following command:

```auto
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    PARTITION_NAME,
    DATA_LENGTH,
    DATA_FREE
FROM
    information_schema.PARTITIONS
WHERE
    TABLE_NAME='testtab'
AND DATA_FREE>0;

```

I get an empty set, indicating that there’s no `DATA_FREE` space.

But, when I query the `information_schema.TABLES` table with the following command:

```auto
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    DATA_LENGTH,
    DATA_FREE
FROM
    information_schema.TABLES
WHERE
    TABLE_NAME='testtab'
AND DATA_FREE>0;

```

I get a `DATA_FREE` value of `47555018752`, which matches the value from the `SHOW TABLE STATUS` command.

According to the MySQL documentation, the `DATA_FREE` column in the `PARTITIONS` table should show “The number of bytes allocated to the partition or subpartition but not used.” However, it seems like this value is not being properly reported for partitioned tables.

I’ve tried running `ANALYZE TABLE` to gather fresh statistics, but it didn’t help. It seems like this might be a bug.

Could you please look into this issue and provide some guidance on how to resolve it?

Thank you for your time and assistance.

Best regards,  
Marko

---

<div class="post-metadata">

**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\
**Post date:** [November 23, 2023, 6:36pm UTC](https://forums.percona.com/t/possible-bug-in-data-free-reporting-for-partitioned-tables/26745/2 "2023-11-23T18:36:41Z")

</div>

Hi Marko,

Thanks for reaching us.  
MySQL 5.7 reached end of life last October 2023.  
Percona is in the process of releasing the last 5.7.44 minor patch at the moment and there won’t be anymore official 5.7 patches.

For 5.7 there is no guarantee of a fix but if you provide a reproducible test case there is the possibility that someone might take a look and check what’s wrong and provide some suggestions.

Are you able to reproduce the same issue with MySQL/Percona Server 8.0?

Thanks in advance

---

<div class="post-metadata">

**Author:** ![msutic](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/msutic/32/4218_2.png) [@msutic](https://forums.percona.com/u/msutic)\
**Post date:** [November 24, 2023, 8:37am UTC](https://forums.percona.com/t/possible-bug-in-data-free-reporting-for-partitioned-tables/26745/3 "2023-11-24T08:37:15Z")

</div>

Hello Carlos,

In 5.6 version the problem was not evident. Need to test MySQL 5.8 and maybe MariaDB.

As for 5.7 I am sharing reproducible test case.

```auto
CREATE TABLE temp_table (
    id INT NOT NULL,
    value INT NOT NULL
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (333333),
    PARTITION p1 VALUES LESS THAN (666666),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

DELIMITER //
CREATE PROCEDURE populate_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 1000000 DO
        INSERT INTO temp_table (id, value) VALUES (i, i);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL populate_data();

mysql> select count(*) from temp_table;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (1.13 sec)

mysql> SELECT
    -> TABLE_SCHEMA,
    -> TABLE_NAME,
    -> PARTITION_NAME,
    -> DATA_LENGTH,
    -> DATA_FREE
    -> FROM
    -> information_schema.PARTITIONS
    -> WHERE
    -> PARTITION_NAME IS NOT NULL
    -> AND TABLE_NAME='temp_table';
+--------------+------------+----------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+----------------+-------------+-----------+
| testdb | temp_table | p0 | 12075008 | 0 |
| testdb | temp_table | p1 | 12075008 | 0 |
| testdb | temp_table | p2 | 12075008 | 0 |
+--------------+------------+----------------+-------------+-----------+
3 rows in set (0.44 sec)

mysql> delete from temp_table where id<333333;
Query OK, 333333 rows affected (2.92 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> analyze table temp_table;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| testdb.temp_table | analyze | status | OK |
+-------------------+---------+----------+----------+
1 row in set (0.78 sec)

mysql> SELECT
    -> TABLE_SCHEMA,
    -> TABLE_NAME,
    -> PARTITION_NAME,
    -> DATA_LENGTH,
    -> DATA_FREE
    -> FROM
    -> information_schema.PARTITIONS
    -> WHERE
    -> PARTITION_NAME IS NOT NULL
    -> AND TABLE_NAME='temp_table';
+--------------+------------+----------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+----------------+-------------+-----------+
| testdb | temp_table | p0 | 16384 | 0 |
| testdb | temp_table | p1 | 12075008 | 0 |
| testdb | temp_table | p2 | 12075008 | 0 |
+--------------+------------+----------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT
    -> TABLE_SCHEMA,
    -> TABLE_NAME,
    -> DATA_LENGTH,
    -> DATA_FREE
    -> FROM
    -> information_schema.TABLES
    -> WHERE TABLE_NAME='temp_table';
+--------------+------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+-------------+-----------+
| testdb | temp_table | 24166400 | 24117248 |
+--------------+------------+-------------+-----------+
1 row in set (0.00 sec)

# ls -l /var/lib/mysql/testdb/temp_table*
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:25 /var/lib/mysql/testdb/temp_table#P#p0.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:21 /var/lib/mysql/testdb/temp_table#P#p1.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:22 /var/lib/mysql/testdb/temp_table#P#p2.ibd
-rw-r----- 1 mysql mysql 8588 Nov 24 08:19 /var/lib/mysql/testdb/temp_table.frm

mysql> alter table temp_table rebuild partition p0;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0

# ls -l /var/lib/mysql/testdb/temp_table*
-rw-r----- 1 mysql mysql 98304 Nov 24 08:33 /var/lib/mysql/testdb/temp_table#P#p0.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:21 /var/lib/mysql/testdb/temp_table#P#p1.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:22 /var/lib/mysql/testdb/temp_table#P#p2.ibd
-rw-r----- 1 mysql mysql 8588 Nov 24 08:33 /var/lib/mysql/testdb/temp_table.frm

```

Please let me know if we can do something on 5.7 version to collect DATA\_FREE info.

btw  
MariaDB 11.1.2 is not affected by the issue and Percona/MySQL 5.6.  
If you need verification for MySQL/Percona Server 8.0 please use provided test case.

Thanks

---

<div class="post-metadata">

**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\
**Post date:** [November 28, 2023, 2:21pm UTC](https://forums.percona.com/t/possible-bug-in-data-free-reporting-for-partitioned-tables/26745/4 "2023-11-28T14:21:35Z")

</div>

Hi again!

Thanks for the provided test case.  
I have verified that it also affects 8.0 and I have created the following bug reports:  
[https://bugs.mysql.com/bug.php?id=113256](https://bugs.mysql.com/bug.php?id=113256)  
[https://jira.percona.com/browse/PS-9015](https://jira.percona.com/browse/PS-9015)

You can add yourself to the “watch list” to follow the status of the bug and potential future fixes.

Regards

---

<div class="post-metadata">

**Author:** ![msutic](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/msutic/32/4218_2.png) [@msutic](https://forums.percona.com/u/msutic)\
**Post date:** [November 29, 2023, 12:36pm UTC](https://forums.percona.com/t/possible-bug-in-data-free-reporting-for-partitioned-tables/26745/5 "2023-11-29T12:36:52Z")

</div>

Hi Carlos,

Thank you for sharing info.

Best regards,  
Marko