| Bug #83957 | Information_Schema.TABLES table statistics are NULL | ||
|---|---|---|---|
| Submitted: | 24 Nov 2016 16:25 | Modified: | 3 Oct 2017 18:51 |
| Reporter: | Nicolai Plum | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | data dictionary, information_schema | ||
[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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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 2017 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 2017 12:00]
Ståle Deraas
We will work on a better user experience for this.
[22 Jun 2017 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 2017 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 2017 10:07]
Erlend Dahl
Bug#87548 Info missing from Show Table Status in 8.02 was marked as a duplicate
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.