CODEWITHSUNDEEP
mysql
Devin Emke
Devin Emke

Reputation: 181

mysql 8 MyISAM "SHOW TABLE STATUS" row count

i recently upgraded from mySQL 5.6.34 -> 8.0.16 (on macOS 10.14.5) and i am noticing very strange behavior with the row counts returned from "SHOW TABLE STATUS" as well as the row counts in the "information_schema" table. consider this simple schema:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test` (`id`, `name`) VALUES
(1, 'one'),
(2, 'two'),
(3, 'three'),
(4, 'four'),
(5, 'five');

when i then run the following query i see the expected output:

SELECT * FROM test;
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
+----+-------+

likewise when i then run the following query i see the expected output:

SELECT COUNT(*) FROM test;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

however when i then run the following query:

    SHOW TABLE STATUS \G
*************************** 1. row ***************************
           Name: test
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-05-30 13:56:46
    Update_time: 2019-05-30 16:02:24
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment:

it appears that there are no rows (even though there are 5). likewise i see the same results when i run:

SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'test';
    +------------+------------+
    | TABLE_NAME | TABLE_ROWS |
    +------------+------------+
    | test       |          0 |
    +------------+------------+

no rows? if i add/delete rows to the table the counts do not change. only after i run:

ANALYZE TABLE `test`

...do i see all of the row counts as correct. i am only seeing this on mySQL 8. everything worked as expected on mySQL 5. i am aware of problems with accurate row counts using InnoDB tables, but these are all MyISAM tables, which should always show the correct row counts. any help is appreciated. thanks.

Upvotes: 1

Views: 979

Answers (1)

Solarflare
Solarflare

Reputation: 11106

The information schema tables underwent significant, incompatible changes in MySQL 8 with the introduction of the global data dictionary:

Previously, INFORMATION_SCHEMA queries for table statistics in the STATISTICS and TABLES tables retrieved statistics directly from storage engines. As of MySQL 8.0, cached table statistics are used by default.

The cache is controlled by the system variable information_schema_stats_expiry:

Some INFORMATION_SCHEMA tables contain columns that provide table statistics:

[...] TABLES.TABLE_ROWS [...]

Those columns represent dynamic table metadata; that is, information that changes as table contents change.

By default, MySQL retrieves cached values for those columns from the mysql.index_stats and mysql.table_stats dictionary tables when the columns are queried, which is more efficient than retrieving statistics directly from the storage engine. If cached statistics are not available or have expired, MySQL retrieves the latest statistics from the storage engine and caches them in the mysql.index_stats and mysql.table_stats dictionary tables. Subsequent queries retrieve the cached statistics until the cached statistics expire.

[...]

To update cached values at any time for a given table, use ANALYZE TABLE.

To always retrieve the latest statistics directly from the storage engine and bypass cached values, set information_schema_stats_expiry to 0.

This is consistent with your behaviour.

You can set information_schema_stats_expiry globally to 0, or per session whenever you need accurate statistics.

Upvotes: 2

Related Questions