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:
None 
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
Description:
After adding records to a MyISAM table the count of table_rows in the INFORMATION_SCHEMA is not updated. I have to perform an ANALYZE TABLE before I can get the correct figure.

How to repeat:
Here is a test set:

CREATE TABLE `accesslog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `pageid` VARCHAR(255) 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;

SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; <= shows zero (correct)

REPLACE INTO `accesslog` VALUES(7734996, 'page1', '213.86.233.158', '2020-03-18 13:02:53');
REPLACE INTO `accesslog` VALUES(7734997, 'page2', '41.57.111.192', '2020-03-18 13:05:02');
REPLACE INTO `accesslog` VALUES(7734998, 'page3', '122.164.65.186', '2020-03-18 13:05:34');
REPLACE INTO `accesslog` VALUES(7734999, 'page4', '197.57.218.172', '2020-03-18 13:11:30');
REPLACE INTO `accesslog` VALUES(7735000, 'page5', '182.75.146.14', '2020-03-18 13:14:09');

SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; <= shows zero (incorrect)

ANALYZE TABLE `accesslog`;

SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_name = 'accesslog'; <= shows 5 (correct)

Suggested fix:
Update table_rows after every insert/replace
[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