| Bug #95407 | Information schema: update_time not working | ||
|---|---|---|---|
| Submitted: | 17 May 2019 10:44 | Modified: | 20 May 2019 6:34 |
| Reporter: | Gianluca Ghinassi | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 8.0.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 May 2019 6:34]
MySQL Verification Team
Hello Gianluca Ghinassi, Thank you for the report. Imho this is an expected behavior in 8.0 as default value of information_schema_stats_expiry is 86400 secs (= 1 day) - https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc.... This is introduced in 8.0 and not present in 5.7. - 8.0.16 mysql> show variables like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 86400 | +---------------------------------+-------+ 1 row in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE `tbl` (`a` int(11) NOT NULL DEFAULT '0') ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> select * from information_schema.tables where table_schema = 'test' AND TABLE_NAME like 'tbl%'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: tbl TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2019-05-20 08:28:24 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8mb4_0900_ai_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.02 sec) mysql> insert into tbl values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.tables where table_schema = 'test' AND TABLE_NAME like 'tbl%'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: tbl TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2019-05-20 08:28:24 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8mb4_0900_ai_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.00 sec) mysql> update tbl set a=100; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from information_schema.tables where table_schema = 'test' AND TABLE_NAME like 'tbl%'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: tbl TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2019-05-20 08:28:24 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8mb4_0900_ai_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.00 sec) mysql> set global information_schema_stats_expiry=0; Query OK, 0 rows affected (0.00 sec) - reconnect mysql> select * from information_schema.tables where table_schema = 'test' AND TABLE_NAME like 'tbl%'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: tbl TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 3 AVG_ROW_LENGTH: 5461 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2019-05-20 08:28:24 UPDATE_TIME: 2019-05-20 08:28:24 CHECK_TIME: NULL TABLE_COLLATION: utf8mb4_0900_ai_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.01 sec) mysql> show variables like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 0 | +---------------------------------+-------+ 1 row in set (0.01 sec) regards, Umesh
[20 May 2019 6:37]
MySQL Verification Team
Also related, see Bug #83957, Bug #91038 (and Bug #14374)
[20 May 2019 6:39]
MySQL Verification Team
- 5.7 (not seen post Bug #14374 but 8.0.x introduced information_schema_stats_expiry) bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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'; Empty set (0.01 sec) mysql> show variables like 'information_schema_stats_expiry'; Empty set (0.01 sec) mysql> mysql> show variables like 'information_schema_stats_expiry'; Empty set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE `tbl` (`a` int(11) NOT NULL DEFAULT '0') ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> select * from information_schema.tables where table_schema = 'test' AND TABLE_NAME like 'tbl%'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: tbl TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2019-05-20 08:26:40 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.00 sec) mysql> insert into tbl values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.tables where table_schema = 'test' AND TABLE_NAME like 'tbl%'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: tbl TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 3 AVG_ROW_LENGTH: 5461 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2019-05-20 08:26:40 UPDATE_TIME: 2019-05-20 08:26:40 CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.00 sec) mysql> update tbl set a=100; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from information_schema.tables where table_schema = 'test' AND TABLE_NAME like 'tbl%'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: tbl TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 3 AVG_ROW_LENGTH: 5461 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: 2019-05-20 08:26:40 UPDATE_TIME: 2019-05-20 08:26:40 CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.00 sec)
Description: SELECT `tables`.`TABLE_NAME` AS `table_name`, `tables`.`UPDATE_TIME` AS `update_time` FROM `information_schema`.`TABLES` WHERE (`tables`.`TABLE_NAME` IN ('mytable1','mytable2')) mytable1 and mytable2 are standard tables with no partitions. If I update a record in one of those tables, I would except the update_time to be set correctly with the time the table was updated. That was working in MySQL Server 5.7.25 but now in MySQL server 8.0.16 it doesn't seem to. How to repeat: 1) Run the query above 2) Update a record in one of those tables 3) Run the query again and check the update_time