Search code examples
mysqlauto-increment

PROBLEM WITH - AUTO_INCREMENT VALUE IN TABLE_SCHEMA NOT UPDATING

SCENARIO

  1. ALTER TABLE {TABLE NAME} AUTO_INCREMENT = 1;

  2. INSERT INTO {TABLE NAME} ({COLUMN}) VALUES (1); (this is only record in table after auto increment was updated)

  3. SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = {DATABASE NAME} AND TABLE_NAME = {TABLE NAME};

the last select auto_increment is returning the old value before execution alter table in step 1) and I don't understand why and hot to fix it or maybe alter table in step 1) is not correct way to reset auto_increment.

THX

PS. I know a bit but not everything. I was researching this problem and didn't find satisfactory/explanatory answer.

Solution

  • The INFORMATION_SCHEMA doesn't update to reflect recent alterations. MySQL 8.0 changed it so it only updates once every 24 hours.

    You can set this:

    SET GLOBAL information_schema_stats_expiry=0;

    That will make INFORMATION_SCHEMA update immediately, at the cost of some overhead on your system.