Description:
Checking "DATA_FREE" shows a different value when comparing information_schema.TABLES vs information_schema.PARTITIONS:
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' and TABLE_SCHEMA = 'bugtest';
+--------------+------------+----------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+----------------+-------------+-----------+
| bugtest | temp_table | p0 | 294912 | 0 |
| bugtest | temp_table | p1 | 1589248 | 0 |
+--------------+------------+----------------+-------------+-----------+
mysql> SELECT
-> TABLE_SCHEMA,
-> TABLE_NAME,
-> DATA_LENGTH,
-> DATA_FREE
-> FROM
-> information_schema.TABLES
-> WHERE TABLE_NAME='temp_table' and TABLE_SCHEMA = 'bugtest';
+--------------+------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+-------------+-----------+
| bugtest | temp_table | 1884160 | 4194304 |
+--------------+------------+-------------+-----------+
How to repeat:
create database bugtest;
use bugtest;
drop table if exists bugtest.temp_table;
CREATE TABLE temp_table (
id INT NOT NULL auto_increment primary key,
value INT NOT NULL
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (10000),
PARTITION p1 VALUES LESS THAN MAXVALUE
);
Do data load:
for i in {1..40000}
do
mysql -e "INSERT INTO bugtest.temp_table (id, value) VALUES (null, $i );"
done
Then check with the above queries.
Suggested fix:
DATA_FREE should be consistent in both IS tables
Description: Checking "DATA_FREE" shows a different value when comparing information_schema.TABLES vs information_schema.PARTITIONS: 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' and TABLE_SCHEMA = 'bugtest'; +--------------+------------+----------------+-------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | DATA_LENGTH | DATA_FREE | +--------------+------------+----------------+-------------+-----------+ | bugtest | temp_table | p0 | 294912 | 0 | | bugtest | temp_table | p1 | 1589248 | 0 | +--------------+------------+----------------+-------------+-----------+ mysql> SELECT -> TABLE_SCHEMA, -> TABLE_NAME, -> DATA_LENGTH, -> DATA_FREE -> FROM -> information_schema.TABLES -> WHERE TABLE_NAME='temp_table' and TABLE_SCHEMA = 'bugtest'; +--------------+------------+-------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | DATA_FREE | +--------------+------------+-------------+-----------+ | bugtest | temp_table | 1884160 | 4194304 | +--------------+------------+-------------+-----------+ How to repeat: create database bugtest; use bugtest; drop table if exists bugtest.temp_table; CREATE TABLE temp_table ( id INT NOT NULL auto_increment primary key, value INT NOT NULL ) PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (10000), PARTITION p1 VALUES LESS THAN MAXVALUE ); Do data load: for i in {1..40000} do mysql -e "INSERT INTO bugtest.temp_table (id, value) VALUES (null, $i );" done Then check with the above queries. Suggested fix: DATA_FREE should be consistent in both IS tables