Bug #104718 Documentation inaccurate about UPDATE_TIME in I_S.tables
Submitted: 25 Aug 2021 9:59 Modified: 27 Sep 2021 10:02
Reporter: Przemyslaw Malkowski Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.25 OS:Any
Assigned to: CPU Architecture:Any

[25 Aug 2021 9:59] Przemyslaw Malkowski
Description:
In the documentation, we can find:

"Even with file-per-table mode with each InnoDB table in a separate .ibd file, change buffering can delay the write to the data file, so the file modification time is different from the time of the last insert, update, or delete. "
and
"UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. "

However, I don't see that property updated on any DML queries. Only i.e. ANALYZE gets it updated immediately.

How to repeat:
mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 8.0.25    | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql > CREATE TABLE t1 (id int auto_increment primary key, a int);
Query OK, 0 rows affected (0.03 sec)

mysql > CREATE TABLE t2 (id int auto_increment primary key, a int);
Query OK, 0 rows affected (0.04 sec)

mysql > SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.01 sec)

mysql > insert into t1 set a=1;
Query OK, 1 row affected (0.00 sec)

mysql > insert into t2 set a=1;
Query OK, 1 row affected (0.01 sec)

mysql > SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.00 sec)

mysql > select sleep(60);
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

mysql > SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.01 sec)

mysql > select now();
+---------------------+
| now()               |
+---------------------+
| 2021-08-25 11:48:55 |
+---------------------+
1 row in set (0.00 sec)

mysql > update t1 set a=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql > SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.01 sec)

mysql > show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-08-25 11:35:15
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql > select * from mysql.innodb_table_stats where database_name="test";
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | t1         | 2021-08-25 11:35:15 |      0 |                    1 |                        0 |
| test          | t2         | 2021-08-25 11:35:19 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.00 sec)

mysql > ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql > SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+---------------------+
| TABLE_NAME | UPDATE_TIME         |
+------------+---------------------+
| t1         | 2021-08-25 11:49:02 |
| t2         | NULL                |
+------------+---------------------+
2 rows in set (0.00 sec)

Suggested fix:
Update the documentation accordingly or implement the real update metadata.
[27 Aug 2021 10:02] MySQL Verification Team
Hello Przemyslaw Malkowski,

Thank you for the report and feedback.
Imho this is an expected behavior in 8.0because the default setting
for information_schema_stats_expiry=86400 secs (= 1 day). 
I quickly tried your test case and can confirm that changes are reflected instantly. Please see https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc...

--
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql>  CREATE TABLE t1 (id int auto_increment primary key, a int);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (id int auto_increment primary key, a int);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.00 sec)

mysql>  insert into t1 set a=1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 set a=1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.00 sec)

mysql> select sleep(60);
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-08-27 11:50:44 |
+---------------------+
1 row in set (0.00 sec)

mysql>  update t1 set a=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.00 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-08-27 11:46:42
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select * from mysql.innodb_table_stats where database_name="test";
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | t1         | 2021-08-27 11:46:42 |      0 |                    1 |                        0 |
| test          | t2         | 2021-08-27 11:47:04 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.00 sec)

mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.00 sec)

mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+---------------------+
| TABLE_NAME | UPDATE_TIME         |
+------------+---------------------+
| t1         | 2021-08-27 11:50:51 |
| t2         | NULL                |
+------------+---------------------+
2 rows in set (0.00 sec)

mysql> drop table t1,t2;
Query OK, 0 rows affected (0.01 sec)

---- Let us set information_schema_stats_expiry=0

mysql> set information_schema_stats_expiry=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  CREATE TABLE t1 (id int auto_increment primary key, a int);
Query OK, 0 rows affected (0.02 sec)

mysql>  CREATE TABLE t2 (id int auto_increment primary key, a int);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+-------------+
| TABLE_NAME | UPDATE_TIME |
+------------+-------------+
| t1         | NULL        |
| t2         | NULL        |
+------------+-------------+
2 rows in set (0.00 sec)

mysql>  insert into t1 set a=1;
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 set a=1;
Query OK, 1 row affected (0.01 sec)

mysql>  SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';
+------------+---------------------+
| TABLE_NAME | UPDATE_TIME         |
+------------+---------------------+
| t1         | 2021-08-27 11:53:47 |
| t2         | 2021-08-27 11:53:51 |
+------------+---------------------+
2 rows in set (0.00 sec)

--

Imho, this is explained at the beginning, quoting as is "Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. "
 
https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html 

Please let me know if this is sufficient then I'll close this as a !bg. Thank you.

regards,
Umesh
[28 Sep 2021 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".