Why MySQL 8.0.18’s ANALYZE Fails with Uppercase Table Names – A Bug Revealed

ANALYZE Command Overview

The ANALYZE command updates a table's statistical information, which the optimizer uses for query planning. Its main functions are:

Refresh table statistics.

Analyze each index and store the results.

In MySQL, statistical data is stored in INFORMATION_SCHEMA.TABLES. You can view it with:

SHOW TABLE STATUS LIKE 'table_name';

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'table_name';

Note that the information in INFORMATION_SCHEMA.TABLES is cached; the cache expiry is controlled by the information_schema_stats_expiry variable, which defaults to 86400 seconds (one day). Running ANALYZE forces an immediate refresh of this cached data.

Test Environment

The tests were performed on a server running CentOS 8.0 with MySQL version 8.0.18.

ANALYZE Tests

Step 1: View current table information – The original row count and statistics of the test table pdcr are displayed (see images).

Step 2: Insert a large volume of data – After inserting 3 million rows (three times the original size), the real row count increases, but the displayed statistics remain unchanged, showing that statistics are cached.

Step 3: Run ANALYZE with an uppercase table name – Executing ANALYZE TABLE PDCR; returns a success message, yet the statistics still do not update, indicating a problem.

Step 4: Run ANALYZE with a lowercase table name – Executing ANALYZE TABLE pdcr; also returns a success message, and this time the statistics are correctly refreshed, as shown in the resulting screenshots.

Conclusion

The experiments reveal a bug in MySQL 8.0.18: when the ANALYZE command is issued with an uppercase table name, MySQL reports success but fails to update the table's statistical information. Using a lowercase table name avoids the issue and updates the statistics as expected.