MySQL Bugs: #110108: DATA_FREE from information

Bug #110108	DATA_FREE from information_schema.tables is inaccurate
Submitted:	17 Feb 2023 12:17	Modified:	2 Jun 2023 5:30
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.7.40, 5.7.42	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
DATA_FREE from information_schema.tables is inaccurate, it is consistent with the file "ibtmp1". Sometimes, it's confusing.

===========================result===========================
SELECT DATA_FREE, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY DATA_FREE DESC LIMIT 10;
DATA_FREE	TABLE_SCHEMA	TABLE_NAME
75497472	information_schema	COLUMNS
75497472	information_schema	EVENTS
75497472	information_schema	OPTIMIZER_TRACE
75497472	information_schema	PARAMETERS
75497472	information_schema	PARTITIONS
75497472	information_schema	PLUGINS
75497472	information_schema	PROCESSLIST
75497472	information_schema	ROUTINES
75497472	information_schema	TRIGGERS
75497472	information_schema	VIEWS

SELECT DATA_FREE, FILE_NAME, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';
DATA_FREE	FILE_NAME	ENGINE
75497472	./ibtmp1	InnoDB

How to repeat:
Execute the following test by MTR:

--echo # 1. Create table.
CREATE DATABASE `sbtest`;
USE `sbtest`;
CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_10` (`k`)
);
--echo

--echo # 2. Insert data into table.
DELIMITER //;
CREATE PROCEDURE insertdata()
BEGIN
DECLARE id int;
SET id = 0;
WHILE id < 20000 DO
  INSERT INTO `sbtest1`(`k`, `c`, `pad`)
  VALUES(11111, "59630305640-99001561578-28826938779-74890888165-31062640740-71545880473-84428380466-78201253367-75757963743-98499429951", "45032138164-15935426517-63891022310-08234213932-58403969668");
  SET id=id+1;
END WHILE;
END//
DELIMITER ;//
CALL insertdata();
--echo

--echo # 3. Expand ibtmp1 file.
INSERT INTO `sbtest1`(`k`, `c`, `pad`) SELECT `k`, `c`, `pad` from `sbtest1`;
INSERT INTO `sbtest1`(`k`, `c`, `pad`) SELECT `k`, `c`, `pad` from `sbtest1`;
--echo

--echo # 4. Show DATA_FREE.
SELECT DATA_FREE, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY DATA_FREE DESC LIMIT 10;
--echo
SELECT DATA_FREE, FILE_NAME, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';
--echo

--echo # 5. Cleanup
DROP PROCEDURE insertdata;
DROP DATABASE `sbtest`;

Suggested fix:
DATA_FREE is from "file" for the file of those tables(like COLUMNS) is ibtmp1(ENIGNE is InnoDB).

The easiest way is to set it to 0 or port from MySQL 8.0.

#sql/sql_show.cc
-table->field[12]->store((longlong) file->stats.delete_length, TRUE);
+table->field[12]->store(0, TRUE);

Hi Mr. Song,

Thank you for your bug report.

We have repeated your test case and got the following result:

SELECT DATA_FREE, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY DATA_FREE DESC LIMIT 10;
DATA_FREE	TABLE_SCHEMA	TABLE_NAME
4194304	sbtest	sbtest1
3145728	mysql	innodb_table_stats
3145728	mysql	innodb_index_stats
3145728	mysql	password_history
3145728	mysql	role_edges
3145728	mysql	plugin
3145728	mysql	default_roles
3145728	mysql	func
3145728	mysql	global_grants
3145728	mysql	user

SELECT DATA_FREE, FILE_NAME, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';
DATA_FREE	FILE_NAME	ENGINE
6291456	./ibtmp1	InnoDB

You wanted free space in the tablespace "innodb_temporary". You have got that value.

So, we have got what we asked for. 

We do not see what is confusing in that ......

Hi, I just build and tried again with mysql-server 5.7.41(Tag:mysql-5.7.41). And I got the same result.

In the result you provided, the database is mysql, but 5.7 should be information_schema (although it is a view).Is the version correct?

What I mean is that the temporary table (InnoDB engine) in i_s share
DATA_FREE of innodb_temporary. But for non-innodb engine tables in I_S, the value is 0(found by limit 20).

========result in 5.7.41===========

SELECT DATA_FREE, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY DATA_FREE DESC LIMIT 20;
DATA_FREE	TABLE_SCHEMA	TABLE_NAME
75497472	information_schema	COLUMNS
75497472	information_schema	EVENTS
75497472	information_schema	OPTIMIZER_TRACE
75497472	information_schema	PARAMETERS
75497472	information_schema	PARTITIONS
75497472	information_schema	PLUGINS
75497472	information_schema	PROCESSLIST
75497472	information_schema	ROUTINES
75497472	information_schema	TRIGGERS
75497472	information_schema	VIEWS
4194304	sbtest	sbtest1
84	mysql	user
0	information_schema	CHARACTER_SETS
0	information_schema	COLLATIONS
0	information_schema	COLLATION_CHARACTER_SET_APPLICABILITY
0	information_schema	COLUMN_PRIVILEGES
0	information_schema	ENGINES
0	information_schema	FILES
0	information_schema	GLOBAL_STATUS
0	information_schema	GLOBAL_VARIABLES

By the way, 8.0 mtr got the similar result like you.
======result in 8.0========
SELECT DATA_FREE, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY DATA_FREE DESC LIMIT 10;
DATA_FREE	TABLE_SCHEMA	TABLE_NAME
7340032	mysql	innodb_table_stats
7340032	mysql	innodb_index_stats
7340032	mysql	password_history
7340032	mysql	role_edges
7340032	mysql	plugin
7340032	mysql	default_roles
7340032	mysql	func
7340032	mysql	global_grants
7340032	mysql	user
7340032	mysql	db

SELECT DATA_FREE, FILE_NAME, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';
DATA_FREE	FILE_NAME	ENGINE
6291456	./ibtmp1	InnoDB

In 8.0 DATA_FREE is from database "mysql", I think it is OK. However，in 5.7, DATA_FREE is from I_S and the tables are view， not base table.

as above

Thank you Huaxiong for the feedback.
I'll get back to you if anything further needed on this.

Sincerely,
Umesh

Hello Huaxiong,

Thank you for the feedback.
Verified as described.

regards,
Umesh