Description:
There is a paragraph for field update_time of information_schema.tables from official document, the url is: https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html
The description for field update_time as follows:
-------------------------------------------------
When the data file was last updated. For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its system tablespace and the data file timestamp does not apply. 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. For MyISAM, the data file timestamp is used; however, on Windows the timestamp is not updated by updates, so the value is inaccurate.
-------------------------------------------------
There are to incorrect descriptions:
1. When the data file was last updated
2. For example, InnoDB stores multiple tables in its system tablespace and the data file timestamp does not apply
How to repeat:
1. Set [innodb_file_per_table=0], [information_schema_stats_expiry=0] in my.cnf
2. Initialize data directory by executing [mysqld --initialize-insecure]
3. Login to mysql
4. Execute SQL: create database test default charset utf8
5. Execute SQL: use test
6. Execute SQL: create table t1(id int auto_increment primary key, str1 varchar(64)) engine = InnoDB default charset utf8
7. Execute SQL: create table t1_1 like t1
8. Execute SQL: insert into t1(str1) values('s1')
9. wait a momemnt, several seconds is enough
10. Execute SQL: insert into t1_1(str1) values('s1_1')
11. Execute SQL: select * from information_schema.tables where table_schema = 'test' and table_name in ('t1', 't1_1')\G
We can get result like follows:
***************************[ 1. row ]***************************
TABLE_CATALOG | def
TABLE_SCHEMA | test
TABLE_NAME | t1
TABLE_TYPE | BASE TABLE
ENGINE | InnoDB
VERSION | 10
ROW_FORMAT | Dynamic
TABLE_ROWS | 1
AVG_ROW_LENGTH | 16384
DATA_LENGTH | 16384
MAX_DATA_LENGTH | 0
INDEX_LENGTH | 0
DATA_FREE | 6291456
AUTO_INCREMENT | 2
CREATE_TIME | 2023-06-18 10:33:40
UPDATE_TIME | 2023-06-18 10:33:51
CHECK_TIME | <null>
TABLE_COLLATION | utf8mb3_general_ci
CHECKSUM | <null>
CREATE_OPTIONS |
TABLE_COMMENT |
***************************[ 2. row ]***************************
TABLE_CATALOG | def
TABLE_SCHEMA | test
TABLE_NAME | t1_1
TABLE_TYPE | BASE TABLE
ENGINE | InnoDB
VERSION | 10
ROW_FORMAT | Dynamic
TABLE_ROWS | 1
AVG_ROW_LENGTH | 16384
DATA_LENGTH | 16384
MAX_DATA_LENGTH | 0
INDEX_LENGTH | 0
DATA_FREE | 6291456
AUTO_INCREMENT | 2
CREATE_TIME | 2023-06-18 10:33:44
UPDATE_TIME | 2023-06-18 10:34:05
CHECK_TIME | <null>
TABLE_COLLATION | utf8mb3_general_ci
CHECKSUM | <null>
CREATE_OPTIONS |
TABLE_COMMENT |
The value of update_time for table [t1] is [2023-06-18 10:33:51].
The value of update_time for table [t1_1] is [2023-06-18 10:34:05].
We could conclude that:
1. the field update_time of information_schema.tables is meaning when the table was last updated
2. When InnoDB stores multiple tables in its system tablespace, the update_time does also apply
Suggested fix:
1. change the sentence [When the data file was last updated] to [When the table was last updated]
2. remove the sentence [For example, InnoDB stores multiple tables in its system tablespace and the data file timestamp does not apply], or give another example.
