CODEWITHSUNDEEP
mysqlauto-increment
Chobeat
Chobeat

Reputation: 3525

Auto increment get reset to 0 at every service restart

It worked until now but when i restart the service, the database lost the auto-increment value for my table. It's for sure something i do with my queries, because i didn't modified my tables recently. What could it be?

Upvotes: 5

Views: 2408

Answers (2)

Y.K.
Y.K.

Reputation: 310

ALTER TABLE table_name ENGINE=MyISAM

Works for me. InnoDB stores the AI in memory, so it's reset to current number of rows + 1 when mysql server is restarted. So if your table is empty when you restart the server, it will be reset to 1.

MyISAM on the other hand was well built.

Upvotes: -1

niczero
niczero

Reputation: 367

That is the documented behaviour, it's not a bug.
When the server starts up, it goes through each (InnoDB) table determining what the new auto_increment value should be. From http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html :

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

It does something similar when you deploy DDL that causes a table rebuild, such as altering the engine -- even altering it to the same value it was before.

Upvotes: 3

Related Questions