Empty tables in different TABLESPACE might have large data_free on creation

[xsist10]

When a table is created normally like this:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

it will have an information_schema.TABLES entry with data_free that equals 0.

> select TABLE_NAME, DATA_FREE from information_schema.TABLES WHERE TABLE_SCHEMA="test" AND TABLE_NAME LIKE "user";
+------------+-----------+
| TABLE_NAME | DATA_FREE |
+------------+-----------+
| user       |         0 |
+------------+-----------+

However if you create it in a tablespace:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` int NOT NULL,
  PRIMARY KEY (`id`)
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB

it might have a completely different data_free value which currently will confuse Cadfael into reporting that it is an empty file but has had records previously:

> select TABLE_NAME, DATA_FREE from information_schema.TABLES WHERE TABLE_SCHEMA="test" AND TABLE_NAME LIKE "user";
+------------+-----------+
| TABLE_NAME | DATA_FREE |
+------------+-----------+
| user       |   5242880 |
+------------+-----------+

This data_free value is determined by:

> SELECT TABLESPACE_NAME, DATA_FREE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system';
+-----------------+-----------+
| TABLESPACE_NAME | DATA_FREE |
+-----------------+-----------+
| innodb_system   |   5242880 |
+-----------------+-----------+

[xsist10]

Can't seem to find how to query which tablespace a table is in without resorting to parsing the SHOW CREATE TABLE result statement.

[xsist10]

Thanks to Isotopp for pointing this out:

MySQL 8.0

SELECT * FROM information_schema.innodb_tables AS ita
JOIN information_schema.innodb_tablespaces AS its ON (ita.space = its.space)
WHERE ita.name = "mysql/component";

MySQL 5.6/5.7

SELECT * FROM information_schema.innodb_sys_tables AS ita
JOIN information_schema.innodb_sys_tablespaces AS its ON (ita.space = its.space)
WHERE ita.name = "mysql/component";