Bug #102792 analyze table didn't refresh statistics correctly in information_schema
Submitted: 3 Mar 7:18 Modified: 3 Mar 8:03
Reporter: burke sephiroth (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.23 OS:Any (ubuntu 2004 desktop x64)
Assigned to: CPU Architecture:Any
Tags: analyze table when lower_case_table_names=1

[3 Mar 7:18] burke sephiroth
Description:
After "analyze table T1" succeed, the statistics in "information_schema.statistics" and "information_schema.tables" will refresh. However, when set "lower_case_table_names=1", "analyze table t1" didn't refresh statistics correctly.

---------------- some config & system variables --------------------

mysql> select @@lower_case_table_names;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select @@information_schema_stats_expiry;
+-----------------------------------+
| @@information_schema_stats_expiry |
+-----------------------------------+
|                             86400 |
+-----------------------------------+
1 row in set (0.00 sec)

How to repeat:

--------------- prepare data on table t2 --------------------
create database my_test;
use my_test;
create table t2(c1 int auto_increment primary key, c2 int) engine=innodb;
insert into t2 values (1,1);
insert into t2(c2) select c2 from t2;
....  repeat sometimes (15 for example, insert 3w records)
insert into t2(c2) select c2 from t2;

------------- create table t1 and insert data ---------------
## create table with lower_case table name
create table t1 (c1 int primary key, c2 int);
insert into t1 select * from t2;

----------- analyze table using upper_case name -------------
## analyze table using upper_case table name
analyze table T1;
select * from information_schema.statistics where table_name='t1' \G
select * from information_schema.tables where table_name='t1' \G

----------- analyze table using lower_case name -------------
## analyze table using lower_case table name
analyze table t1;
select * from information_schema.statistics where table_name='t1' \G
select * from information_schema.tables where table_name='t1' \G

---------- diff of information_schema.statistics -------------

--- a/information_schema.statistics.result
+++ b/information_schema.statistics.result
@@ -8,7 +8,7 @@ TABLE_CATALOG: def
  SEQ_IN_INDEX: 1
   COLUMN_NAME: c1
     COLLATION: A
-  CARDINALITY: 3
+  CARDINALITY: 33120
      SUB_PART: NULL
        PACKED: NULL
      NULLABLE:

---------- diff of information_schema.tables -------------

--- a/information_schema.tables.result
+++ b/information_schema.tables.result
@@ -6,9 +6,9 @@
          ENGINE: InnoDB
         VERSION: 10
      ROW_FORMAT: Dynamic
-     TABLE_ROWS: 32768
- AVG_ROW_LENGTH: 0
-    DATA_LENGTH: 16384
+     TABLE_ROWS: 33120
+ AVG_ROW_LENGTH: 47
+    DATA_LENGTH: 1589248
 MAX_DATA_LENGTH: 0
    INDEX_LENGTH: 0
       DATA_FREE: 4194304

Seems that the informatin_schema ignore the system variables "@@lower_case_table_names".
[3 Mar 8:03] MySQL Verification Team
Hello burke,

Thank you for the report and test case.
Imho this is duplicate of Bug #102292, please see Bug #102292

regards,
Umesh