Understanding the update_time Field in information_schema.tables and Its Reliability in MySQL 8.0

The update_time column in information_schema.tables records the timestamp of the last DML transaction commit for a table, but its reliability is questionable because the value is derived from in‑memory structures that are not always persisted promptly.

Two key questions are explored:

Where does the recorded last‑modification time come from?

Why can it be unreliable?

1. Preparation

Create a test table and insert data:

USE `test`;
CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
INSERT INTO `t1`(i1) VALUES (10),(20),(30);

Query the view to see the current UPDATE_TIME:

SET information_schema_stats_expiry = 0;
SELECT * FROM information_schema.tables WHERE table_schema='test' AND table_name='t1'\G

The displayed UPDATE_TIME corresponds to the commit time of the transaction that inserted the rows.

2. How the Timestamp Is Set

2.1 Marking Modified Tables

During undo logging, trx_undo_report_row_operation() adds the affected table's dict_table_t* pointer to trx->mod_tables (a std::set).

// storage/innobase/trx/trx0rec.cc
bool is_temp_table = index->table->is_temporary();
if (!is_temp_table) {
    trx->mod_tables.insert(index->table);
}

Only non‑temporary tables are tracked.

2.2 Determining the Modification Time

When a read‑write transaction commits, trx_commit_for_mysql() calls trx_update_mod_tables_timestamp(), which assigns the current system time to each table's dict_table_t::update_time field.

// storage/innobase/trx/trx0trx.cc
static void trx_update_mod_tables_timestamp(trx_t *trx) {
    const auto now = std::chrono::system_clock::from_time_t(time(nullptr));
    for (auto it = trx->mod_tables.begin(); it != trx->mod_tables.end(); ++it) {
        (*it)->update_time = now;
    }
    trx->mod_tables.clear();
}

At this point the timestamp exists only in memory.

2.3 Persistence

The in‑memory update_time is eventually written to the mysql.table_stats table via two paths:

Active persistence : Executing ANALYZE TABLE <tbl> triggers ha_innobase::analyze(), which calls ha_innobase::info_low() to copy dict_table_t::update_time into a ha_statistics structure, then into a Table_stat object, and finally stores it in mysql.table_stats.

Passive persistence : When information_schema.tables is queried, MySQL checks whether the cached statistics are expired (using the system variable information_schema_stats_expiry). If expired, it fetches fresh statistics from InnoDB and writes them back to mysql.table_stats.

Both paths involve the same series of transfers:

// dict_table_t -> ha_statistics -> Table_stat -> mysql.table_stats

3. Why the Value Is Unreliable

Two main reasons:

The timestamp is not persisted immediately; it only reaches mysql.table_stats after an ANALYZE TABLE or after the expiration interval (default 86400 s) triggers passive persistence.

Before persistence, the timestamp lives solely in memory. A server crash, power loss, or eviction of the dict_table_t object from the InnoDB cache (controlled by table_definition_cache) causes the value to be lost.

Consequently, the UPDATE_TIME shown by information_schema.tables may be stale or NULL, which the MySQL developers consider expected behavior rather than a bug.

4. The mysql.table_stats Table

By default users cannot read mysql.table_stats. Enabling the debug session variable skip_dd_table_access_check allows inspection, revealing columns such as schema_name, table_name, update_time, and cached_time.

5. Summary

During DML, InnoDB marks the affected table, and on transaction commit the current time is stored in the table's in‑memory dict_table_t. This value is later persisted to mysql.table_stats either actively (via ANALYZE TABLE) or passively (when statistics expire). Because persistence is not instantaneous and the in‑memory copy can be lost, the UPDATE_TIME reported by information_schema.tables is not always reliable.