Bug #99022 | table_rows not being updated after inserting into a MyISAM table | ||
---|---|---|---|
Submitted: | 22 Mar 2020 10:45 | Modified: | 23 Mar 2020 10:31 |
Reporter: | Anthony Marston | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
Version: | 8.0.19 | OS: | Windows (Windows 10) |
Assigned to: | CPU Architecture: | Any | |
Tags: | myisam, table_rows |
[22 Mar 2020 10:45]
Anthony Marston
[23 Mar 2020 10:31]
MySQL Verification Team
Hello Anthony, Thank you for the report. Quoting from Dev's response in Bug#91038, this is an expected behavior in 8.0 - based on the default setting for information_schema_stats_expiry=86400 secs (= 1 day). This is introduced in 8.0 and not present in 5.7. The expectation stated in bug page can be observed with setting information_schema_stats_expiry=0. Please refer following page for more info, https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_in formation_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 7 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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> use test Database changed mysql> CREATE TABLE `accesslog` ( -> `id` INT(11) NOT NULL AUTO_INCREMENT, -> `pageid` VARCHAR(25) NULL DEFAULT NULL, -> `ip_address` VARCHAR(40) NULL DEFAULT NULL, -> `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -> PRIMARY KEY (`id`), -> INDEX `pageid` (`pageid`, `ip_address`) -> ) ENGINE=MyISAM; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> SHOW VARIABLES like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 86400 | +---------------------------------+-------+ 1 row in set (0.01 sec) mysql> SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; -- shows zero (correct) +--------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | +--------------+------------+------------+ | test | accesslog | 0 | +--------------+------------+------------+ 1 row in set (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734996, 'page1', '213.86.233.158', '2020-03-18 13:02:53'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734997, 'page2', '41.57.111.192', '2020-03-18 13:05:02'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734998, 'page3', '122.164.65.186', '2020-03-18 13:05:34'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734999, 'page4', '197.57.218.172', '2020-03-18 13:11:30'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7735000, 'page5', '182.75.146.14', '2020-03-18 13:14:09'); Query OK, 1 row affected (0.00 sec) mysql> SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; -- shows zero (incorrect) +--------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | +--------------+------------+------------+ | test | accesslog | 0 | +--------------+------------+------------+ 1 row in set (0.00 sec) mysql> ANALYZE TABLE `accesslog`; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | test.accesslog | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; -- shows 5 (correct) +--------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | +--------------+------------+------------+ | test | accesslog | 5 | +--------------+------------+------------+ 1 row in set (0.00 sec) mysql> drop table if exists accesslog; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `accesslog` ( -> `id` INT(11) NOT NULL AUTO_INCREMENT, -> `pageid` VARCHAR(25) NULL DEFAULT NULL, -> `ip_address` VARCHAR(40) NULL DEFAULT NULL, -> `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -> PRIMARY KEY (`id`), -> INDEX `pageid` (`pageid`, `ip_address`) -> ) ENGINE=MyISAM; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> SET information_schema_stats_expiry=0; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 0 | +---------------------------------+-------+ 1 row in set (0.00 sec) mysql> SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; -- shows zero (correct) +--------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | +--------------+------------+------------+ | test | accesslog | 0 | +--------------+------------+------------+ 1 row in set (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734996, 'page1', '213.86.233.158', '2020-03-18 13:02:53'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734997, 'page2', '41.57.111.192', '2020-03-18 13:05:02'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734998, 'page3', '122.164.65.186', '2020-03-18 13:05:34'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7734999, 'page4', '197.57.218.172', '2020-03-18 13:11:30'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO `accesslog` VALUES(7735000, 'page5', '182.75.146.14', '2020-03-18 13:14:09'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; -- shows five (correct) +--------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | +--------------+------------+------------+ | test | accesslog | 5 | +--------------+------------+------------+ 1 row in set (0.00 sec) regards, Umesh