PROBLEM WITH - AUTO_INCREMENT VALUE IN TABLE_SCHEMA NOT UPDATING

user_5427785 0
Viewed 1457

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.

auto-increment mysql
3 Answers

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.

Bill_Karwin_208600

I had the issue where the information schema wasn't being immediately updated.

To fix this I ran:

SET PERSIST information_schema_stats_expiry = 0;

As using SET GLOBAL didn't work for me.

Hope this helps somebody else.

user_9336660

I guess you must set AUTO_INCREMENT = 1 not 0

user_6346980
Related