Description:
MySQL 8 dropped the query cache feature which has been available for years - but given MySQL 8's focus on high-concurrency, this is an understandable decision.
However, users which have utilized the query cache are not given any good options for building an alternative which allows the use of MySQL 8 alongside a semi-intelligent query cache.
We are thrown ProxySQL as an alternative: https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/ from the dev team - but that is not a good alternative.
The foundation for any remotely decent query cache is knowing when the data in a table has changed; however, innodb does not reliably update information_schema.TABLES.UPDATE_TIME at all (its usually NULL - and sometimes its updated - in no discernible pattern).
We understand that data consistency guarantees are no longer an option for a query cache utilized outside of MySQL server itself... but even minute level precision for information_schema.TABLES.UPDATE_TIME at least gives us something to build on that would give us something other than dumb caches which are relegated to blind TTL expiration of cached data.
How to repeat:
On any MySQL server with a decent number of tables...
use information_schema;
select TABLE_SCHEMA,TABLE_NAME,UPDATE_TIME from TABLES;
Most of the time that I updated a table, UPDATE_TIME remains NULL. As mentioned above, I have not identified a pattern in which it consistently does/does-not update the value - all I can say is that it usually does not.
Suggested fix:
Keep information_schema.TABLES.UPDATE_TIME updated. We understand that there may be a performance penalty to do so. Perhaps make a system variable that defines a maximum number of seconds of drift between the last time a table was actually updated and the update of information_schema.TABLES.UPDATE_TIME
If we had this... the community can build our own query cache solutions pretty easily - it gives MySQL 5.x users a better upgrade path.
Beyond this preventing better caching solutions, it is also completely unintuitive that this value is not kept somewhat updated for the default table engine.
