Bug #103916 information_schema.tables not reflected after truncate table
Submitted: 4 Jun 2021 18:59 Modified: 6 Jun 2021 15:45
Reporter: HyunHo JUNG Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: information_schema.tables, truncate table

[4 Jun 2021 18:59] HyunHo JUNG 
Description:
The tested version is 8.0.23.
However, it is the also same in 8.0.23 earlier versions.

####### Test - 8.0.23

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 3082751  |
+----------+

mysql> select table_name,table_rows,avg_row_length
       from information_schema.TABLES
       where table_name='sbtest1';
+------------+------------+----------------+
| TABLE_NAME | TABLE_ROWS | AVG_ROW_LENGTH |
+------------+------------+----------------+
| sbtest1         |    2971870   |                    215 |
+------------+------------+----------------+
1 row in set (0.00 sec)

mysql> select table_name,n_rows,clustered_index_size,
       sum_of_other_index_sizes
       from mysql.innodb_table_stats
       where table_name='sbtest1';
+------------+---------+----------------------+--------------------------+
| table_name | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+------------+---------+----------------------+--------------------------+
| sbtest1        | 2737323 |                  39168         |                        0              |
+------------+---------+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql> select table_name,index_name,stat_name,stat_value
     from mysql.innodb_index_stats
     where table_name='sbtest1';
+------------+------------+--------------+------------+
| table_name | index_name | stat_name    | stat_value |
+------------+------------+--------------+------------+
| sbtest1    | PRIMARY    | n_diff_pfx01 |    2737323 |
| sbtest1    | PRIMARY    | n_leaf_pages |      39021 |
| sbtest1    | PRIMARY    | size         |      39168 |
+------------+------------+--------------+------------+
7 rows in set (0.01 sec)

mysql> truncate table sbtest1;
Query OK, 0 rows affected (0.32 sec)

mysql> select table_name,table_rows,avg_row_length
       from information_schema.tables
       where table_name='sbtest1';

+------------+------------+----------------+
| TABLE_NAME | TABLE_ROWS | AVG_ROW_LENGTH |
+------------+------------+----------------+
| sbtest1    |    2971870 |            215 |
+------------+------------+----------------+

<---- Problem!!!!!

mysql> select table_name,n_rows,clustered_index_size,
       sum_of_other_index_sizes
       from mysql.innodb_table_stats
       where table_name='sbtest1';  
+------------+--------+----------------------+--------------------------+
| table_name | n_rows | clustered_index_size | sum_of_other_index_sizes |
+------------+--------+----------------------+--------------------------+
| sbtest1        |        0    |                             1      |                                    0     |
+------------+--------+----------------------+--------------------------+

mysql> select table_name,n_rows,clustered_index_size,
       sum_of_other_index_sizes
       from mysql.innodb_table_stats
       where table_name='sbtest1';
+------------+--------+----------------------+--------------------------+
| table_name | n_rows | clustered_index_size | sum_of_other_index_sizes |
+------------+--------+----------------------+--------------------------+
| sbtest1         |         0 |                               1       |                                     0   |
+------------+--------+----------------------+--------------------------+

####### Test - 5.7.18

mysql> select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+

mysql> select table_name,table_rows,avg_row_length
       from information_schema.TABLES
       where table_name='sbtest9';
+------------+------------+----------------+
| table_name | table_rows | avg_row_length |
+------------+------------+----------------+
| sbtest9   |    9609638  |           233  |
+------------+------------+----------------+
1 row in set (0.00 sec)

mysql> select table_name,n_rows,clustered_index_size,
       sum_of_other_index_sizes
       from mysql.innodb_table_stats
       where table_name='sbtest9';
+------------+---------+----------------------+--------------------------+
| table_name | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+------------+---------+----------------------+--------------------------+
| sbtest9    | 9609638 |               137152 |                    14102 |
+------------+---------+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql> select table_name,index_name,stat_name,stat_value
     from mysql.innodb_index_stats
     where table_name='sbtest9';
+------------+------------+--------------+------------+
| table_name | index_name | stat_name    | stat_value |
+------------+------------+--------------+------------+
| sbtest9    | PRIMARY    | n_diff_pfx01 |    9609638 |
| sbtest9    | PRIMARY    | n_leaf_pages |     136987 |
| sbtest9    | PRIMARY    | size         |     137152 |
| sbtest9    | k_9        | n_diff_pfx01 |    5787319 |
| sbtest9    | k_9        | n_diff_pfx02 |    9942726 |
| sbtest9    | k_9        | n_leaf_pages |      12247 |
| sbtest9    | k_9        | size         |      14102 |
+------------+------------+--------------+------------+
7 rows in set (0.01 sec)

mysql> truncate table sbtest9;
Query OK, 0 rows affected (0.32 sec)

mysql> select table_name,table_rows,avg_row_length
       from information_schema.TABLES
       where table_name='sbtest9';

+------------+------------+----------------+
| table_name | table_rows | avg_row_length |
+------------+------------+----------------+
| sbtest9    |          0 |              0 |
+------------+------------+----------------+

<--- it's normal !!!

In version 8.0, I do not understand why it is not reflected in information_schema.tables after truncate table.
In version 5.7, as expected, information_schema.tables after truncate table is reflected normally.

Thanks

How to repeat:
1) Create table
2) Insert data
3) check information_schema.TABLES

4) truncate table 

5) check information_schema.TABLES
[5 Jun 2021 11:29] Tsubasa Tanaka 
What about your information_schema_stats_expiry?

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc...
[6 Jun 2021 7:33] MySQL Verification Team 
Thank you, tanaka-San.

Hello HyunHo JUNG,

Thank you for the report.
Imho this is an expected behavior in 8.0 as default value
of information_schema_stats_expiry is 86400 secs (= 1 day) - https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc... 
This is introduced in 8.0 and not present in 5.7. 

Could you please confirm if setting information_schema_stats_expiry to minimum(0) helps? Thank you!

Also, see Bug #95407

regards,
Umesh
[6 Jun 2021 15:45] HyunHo JUNG 
Thank you, tsubasa tanaka and MySQL Team

mysql> show variables like '%information_schema_stats_expiry%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+

I checked that information_schema_stats_expiry is set to 1 day.

Thanks for your help.