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".
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".