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: | |
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
[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".