Bug #83957 Information_Schema.TABLES table statistics are NULL
Submitted: 24 Nov 2016 16:25 Modified: 3 Oct 18:51
Reporter: Nicolai Plum Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.0 OS:Any
Assigned to:
Tags: data dictionary, information_schema

[24 Nov 2016 16:25] Nicolai Plum
Description:
When using MySQL 8.0.0, SHOW TABLE STATUS LIKE "foo" and the underlying SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME="foo" do not return useful data for the fields Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Update_time. Instead they return NULL.

On MySQL 5.7 they do return data.

A test case is below, we have also observed this behaviour on larger tables with more complex structure.

How to repeat:
Using Server version: 8.0.0-dmr-log MySQL Community Server (GPL): 

root@dba [dba]>  CREATE TABLE istest ( report_id int(10) unsigned NOT NULL, report_date date NOT NULL, PRIMARY KEY (report_id), KEY report_date_idx (report_date))  ENGINE=InnoDB DEFAULT CHARSET=latin1;            
Query OK, 0 rows affected (0.00 sec)

root@dba [dba]> insert into istest values (1, "2016-01-01"), (2, "2016-01-02");                        
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@dba [dba]> select * from Information_schema.tables where table_name = "istest" \G                 
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: dba
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-24 17:22:08
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.00 sec)

Using Server version: 5.7.15-log MySQL Community Server (GPL):

root@dba [dba]> CREATE TABLE istest ( report_id int(10) unsigned NOT NULL, report_date date NOT NULL, PRIMARY KEY (report_id), KEY report_date_idx (report_date))  ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

root@dba [dba]> insert into istest values (1, "2016-01-01"), (2, "2016-01-02");                    
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@dba [dba]> select * from information_schema.tables where table_name = "istest" \G            
 *************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: dba
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 8192
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 16384
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-24 17:17:59
    UPDATE_TIME: 2016-11-24 17:18:04
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.00 sec)

Suggested fix:
Please make the Information_Schema views onto the Data Dictionary in MySQL 8.0.0  do the right thing.
[24 Nov 2016 16:48] Nicolai Plum
Reduce severity to S3
[24 Nov 2016 17:19] Morgan Tocker
Hi Nicolai,

I assume information_schema_stats is set to the default?

See: http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/
[25 Nov 2016 5:27] Umesh Shastry
Hello Nicolai Plum,

Thank you for the report.
As Morgan rightly pointed out in previous note and as explained in http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/

In MySQL 8.0, the dynamic table metadata will default to being cached, and is configurable via the setting information_schema_stats (default cached), and can be changed to information_schema_stats=latest in order to always retrieve the dynamic information directly from the storage engine.

As an alternative, the user can also execute ANALYZE TABLE on the table, to update the cached dynamic statistics.

I quickly checked this and confirm as mentioned in the dev's blog:

== with information_schema_stats= CACHED(which is default)

root@localhost [(none)]> show variables like 'information_schema_stats';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | CACHED |
+--------------------------+--------+
1 row in set (0.01 sec)

root@localhost [(none)]> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test
Database changed
root@localhost [test]> CREATE TABLE istest ( report_id int(10) unsigned NOT NULL, report_date date NOT NULL, PRIMARY KEY (report_id), KEY report_date_idx (report_date))  ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> insert into istest values (1, "2016-01-01"), (2, "2016-01-02");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [test]> select * from Information_schema.tables where table_name = "istest" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-25 06:19:38
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.02 sec)

== with information_schema_stats= LATEST

root@localhost [(none)]> show variables like 'information_schema_stats';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | LATEST |
+--------------------------+--------+
1 row in set (0.01 sec)

root@localhost [test]> CREATE TABLE istest1( report_id int(10) unsigned NOT NULL, report_date date NOT NULL, PRIMARY KEY (report_id), KEY report_date_idx (report_date))  ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> insert into istest1 values (1, "2016-01-01"), (2, "2016-01-02");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [test]> select * from Information_schema.tables where table_name = "istest1"\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: istest1
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 8192
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 16384
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-25 06:21:02
    UPDATE_TIME: 2016-11-25 06:21:21
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

Thanks,
Umesh
[28 Nov 2016 0:06] Nicolai Plum
Morgan: so, it's a feature, and not a bug - I missed that in the release notes, sorry about that. Thanks for the pointer to the blog post with more explanation.

I did have the global variable information_schema_stats=cached.

When I set it to =latest and restart MySQL, I do get statistics on the table.

However, despite it being a variable you can change dynamically, the effect does not seem to be dynamic. Existing table stats are not show dynamically until after a server restart. The table "heartbeat" in this example is updated every second (the update is replicated from a mysql master).

root@db [booking_meta]> show global variables like '%information%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | CACHED |
+--------------------------+--------+
1 row in set (0.00 sec)

root@db [booking_meta]> show table status like "heartbeat" \G
*************************** 1. row ***************************
           Name: heartbeat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2016-11-25 15:27:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

root@db [booking_meta]> set global information_schema_stats=latest;                                                 
Query OK, 0 rows affected (0.00 sec)

root@db [booking_meta]> select sleep(60) /* wait in case of background updates */ ;
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

root@db [booking_meta]> show table status like "heartbeat" \G
*************************** 1. row ***************************
           Name: heartbeat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2016-11-25 15:27:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

even analysing the table does not give me statistics without a server restart:

root@db [booking_meta]> show global variables like '%information%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | LATEST |
+--------------------------+--------+
1 row in set (0.00 sec)

root@db [booking_meta]> analyze table heartbeat;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| booking_meta.heartbeat | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.01 sec)

root@db [booking_meta]> show table status like "heartbeat" \G
*************************** 1. row ***************************
           Name: heartbeat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2016-11-25 15:27:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

nor does analyze table update the statistics even when information_schema_stats=cached

root@db [booking_meta]> set global information_schema_stats=cached;
Query OK, 0 rows affected (0.00 sec)

root@db [booking_meta]> show table status like "heartbeat" \G
*************************** 1. row ***************************
           Name: heartbeat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2016-11-25 15:27:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

root@db [booking_meta]> analyze table heartbeat;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| booking_meta.heartbeat | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.00 sec)

root@db [booking_meta]> show table status like "heartbeat" \G
*************************** 1. row ***************************
           Name: heartbeat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2016-11-25 15:27:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

These are all for an existing table, compared with a newly created table above.

Apparent bugs:

It would be nice if:

the information_schema_stats variable had dynamic effect
ANALYZE table updated the statistics

Related feature request:

It would also be nice if there was an option to have periodic updates of the information_schema_stats, perhaps we could have the ability to set it to a time interval such as 10s or 30m.

Thanks
[28 Nov 2016 8:07] Umesh Shastry
Per manual - If you change a global system variable, the value is remembered and used for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any current client sessions (not even the session within which the SET GLOBAL statement occurred).

In my tests, change was visible in subsequent reconnect and restart was not needed.

root@localhost [test]> show variables like 'information_schema_stats';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | CACHED |
+--------------------------+--------+
1 row in set (0.01 sec)

root@localhost [test]> CREATE TABLE istest ( report_id int(10) unsigned NOT NULL, report_date date NOT NULL, PRIMARY KEY (report_id), KEY report_date_idx (report_date))  ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]>  insert into istest values (1, "2016-01-01"), (2, "2016-01-02");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [test]> select * from Information_schema.tables where table_name = "istest" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-28 09:02:10
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

root@localhost [test]> set global information_schema_stats=latest;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> select * from Information_schema.tables where table_name = "istest" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-28 09:02:10
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

root@localhost [test]> show variables like 'information_schema_stats';                                                                                                                            +--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | CACHED |
+--------------------------+--------+
1 row in set (0.00 sec)

root@localhost [test]> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-advanced-8.0: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.1-dmr-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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.

root@localhost [test]> show variables like 'information_schema_stats';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | LATEST |
+--------------------------+--------+
1 row in set (0.01 sec)

root@localhost [test]> select * from Information_schema.tables where table_name = "istest" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 8192
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 16384
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-28 09:02:10
    UPDATE_TIME: 2016-11-28 09:02:19
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

Also, Analyze table works as expected too.

root@localhost [test]> show variables like 'information_schema_stats';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | CACHED |
+--------------------------+--------+
1 row in set (0.01 sec)

root@localhost [test]> CREATE TABLE istest ( report_id int(10) unsigned NOT NULL, report_date date NOT NULL, PRIMARY KEY (report_id), KEY report_date_idx (report_date))  ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]>  insert into istest values (1, "2016-01-01"), (2, "2016-01-02");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [test]> select * from Information_schema.tables where table_name = "istest" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-28 09:05:18
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

root@localhost [test]> analyze table istest;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| test.istest | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.01 sec)

root@localhost [test]> select * from Information_schema.tables where table_name = "istest" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 8192
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 16384
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-11-28 09:05:18
    UPDATE_TIME: 2016-11-28 09:05:28
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

But, I agree for the feature request for the the ability to set it to a time interval such as 10s or 30m.
[8 Dec 2016 22:15] Nicolai Plum
I'm afraid that ANALYZE TABLE doesn't seem to work as I would like; I can't get statistics on a running connection. For example:

bash-4.1# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1194104
Server version: 8.0.0-dmr-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

root@db [(none)]> show variables like 'information_schema_stats';                       
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| information_schema_stats | CACHED |
+--------------------------+--------+
1 row in set (0.00 sec)

root@db [(none)]> use booking_meta;                                                     
Database changed
root@db [booking_meta]> show table status like "heartbeat" \G                           
*************************** 1. row ***************************
           Name: heartbeat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2016-11-25 15:27:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

root@db [booking_meta]> analyze table heartbeat;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| booking_meta.heartbeat | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.00 sec)

root@db [booking_meta]> show table status like "heartbeat" \G
*************************** 1. row ***************************
           Name: heartbeat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2016-11-25 15:27:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

So it seems to be impossible to get statistics on the table in a running connection.

The data appears not to be cached, when the information_schema_stats is changed from LATEST to CACHED. It is lost, rather than the most recent previous results being stored. Is this intentional?

I understand your response that a global variable does not affect the current connection and that is working as designed, so changing information_schema_stats=latest does not show statistics on the current connection. However, having to re-connect is not ideal; it would be preferable if the change from "CACHED" to "LATEST" would also have effect on the current connection.
[13 Dec 2016 8:08] Umesh Shastry
Sorry for the delayed response.
My quick tests with the earlier table and ANALYZE TABLE seem to work as expected(regardless of value set for information_schema_stats). Could you please provide the SHOW CREATE TABLE of "heartbeat" and conf file(if not default) to confirm again this issue at my end? Please feel free to mark it as private after posting here.

Thanks,
Umesh
[14 Jan 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".
[6 Jun 12:00] Ståle Deraas
We will work on a better user experience for this.
[22 Jun 22:36] Jesper wisborg Krogh
Posted by developer:
 
Note that information_schema_stats can be set for the session to force using the latest data:

mysql> CREATE TABLE istest ( report_id int(10) unsigned NOT NULL,
    -> report_date date NOT NULL, PRIMARY KEY (report_id), KEY report_date_idx
    -> (report_date))  ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into istest values (1, "2016-01-01"), (2,
    -> "2016-01-02");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from Information_schema.tables where table_name =
    -> "istest" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db1
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2017-06-23 08:34:21
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.01 sec)

mysql> SET SESSION information_schema_stats = LATEST;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from Information_schema.tables where table_name = "istest"\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db1
     TABLE_NAME: istest
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 8192
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 16384
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2017-06-23 08:34:21
    UPDATE_TIME: 2017-06-23 08:34:27
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.00 sec)
[20 Sep 12:34] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.3 release, and here's the changelog entry:

The information_schema_stats configuration option, introduced in MySQL
8.0.0, was removed and replaced by information_schema_stats_expiry.

information_schema_stats_expiry defines an expiration
setting for cached INFORMATION_SCHEMA table statistics. For more information, 
see "Optimizing INFORMATION_SCHEMA Queries."

The TABLES_DYNAMIC, STATISTICS_DYNAMIC, and SHOW STATISTICS_DYNAMIC internal
system views were removed. 

Documentation updates:

Optimizing INFORMATION_SCHEMA Queries
https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html

The INFORMATION_SCHEMA TABLES Table
https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

The INFORMATION_SCHEMA STATISTICS Table
https://dev.mysql.com/doc/refman/8.0/en/statistics-table.html

INFORMATION_SCHEMA and Data Dictionary Integration
https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-information-schema.html

information_schema_stats_expiry
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

innodb_read_only
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html

ANALYZE TABLE
https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

What's New
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
[16 Nov 10:07] Erlend Dahl
Bug#87548 Info missing from Show Table Status in 8.02

was marked as a duplicate