MySQL 8.0 no longer updates information_schema.tables after optimizing a table

Question

We have recently been undertaking a purge of data from our databases, which has left some tables with huge amounts of free space (in some cases hunderd of GB's). So I have started a process of going around and optimizing those tables as and when I can to reclaim that space.

In MySQL 5.7 after the optimize completes, the DATA_FREE value in information_schema.tables is shrunk to reflect the fact that the space has been re-claimed and the DATA_LENGTH + INDEX_LENGTH (roughly) matches the size of the ibd file on the server.

However, I have also been upgrading to MySQL 8.0, and have just completed an optimize on here. But the information_schema.tables.data_free value still shows the same as before the optimize. The actual ibd file has shrunk, so I know it has worked. I'm trying to figure out:

1. is this a new feature of MySQL 8 or a Bug
2. how can I force information_schema.tables to update it's values to be correct.

I have tried looking through release notes and searching the web but can't find any reference to the change in the way table works.

I have also tried running analyze table, optimize table (again), restarting the databases etc, all to no avail.

Has anyone else come across this, or can shed some light on what is happening it would be appreciated.

Answer 1

Apparently, this is all to do with the new information_schema_stats_expiry which is set to 24 hours by default.

These values are now cached rather than updated live, so you either have to set information_schema_stats_expiry = 0 or wait for them to expire.

I can confirm, that after 24 hours, the DATA_FREE value updated itself.

Answer 2

I think there is something else going on with this problem. (running mySQL 8.0.36).

The KEY_COLUMN_USAGE table includes several entries for foreign key definitions that were deleted much longer than 24 hours ago, probably weeks, and is missing entries for some foreign keys that have been in place also for weeks.

I used ANALYZE ON all the tables affected by this, refreshed the KEY_COLUMN_USAGE selection and nothing changed.

Looking at the structure of the actual tables, all the foreign key definitions are as expected, the old ones are no longer there and the new ones are there.

For normal everyday database access, this doesn't cause any problems but I use a utility that compares two databases and creates ALTER, etc commands to make both database structures match and this issue cause major problems.

I have asked my database admin to set information_schema_stats_expiry = 0 and waiting for that to happen but I don;t think that's going to help.