When setting or retrieving the auto_increment of a table in MySQL, starting from MySQL 8, the information_schema is cached. This means that when you retrieve AUTO_INCREMENT consecutively, you might get an outdated value. Here are some countermeasures for this issue.

Setting

ALTER TABLE my_table_name AUTO_INCREMENT = 1;

Retrieving

SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE TABLE_NAME = 'my_table_name';

However, there are instances where you might retrieve a cached value. You can address this using one of the following methods:

Solution A: Disable the cache of information_schema within the session

SET information_schema_stats_expiry = 0;

Solution B: Forcefully update the cache of information_schema for the table

ANALYZE TABLE my_table_name;

Reference Pages

• [MySQL8.0] When the AUTO_INCREMENT of information_schema.TABLES is outdated in InnoDB - Qiita
• MySQL :: MySQL 8.0 Reference Manual :: 8.2.3 Optimization of INFORMATION_SCHEMA Queries