Understanding MySQL Auto‑Increment Reset Issues in InnoDB and MyISAM Engines

A new employee encountered a situation where, after restarting MySQL, the auto‑increment values of a table were lost, almost causing serious trouble. The article reviews this issue and explains the underlying mechanics.

When a table uses an auto‑increment column as its primary key, inserting five rows yields IDs 1‑5. Deleting rows 4 and 5 and then restarting the database leads to different ID generation behavior depending on the storage engine.

In older MySQL versions, InnoDB stores the auto‑increment counter only in memory, so after a restart the counter is reset to the maximum existing ID plus one. MyISAM, however, persists the counter in the data file, so it is not lost.

CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', `age` int(4) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO `user`(`name`, age) VALUES('刘备1', 21); INSERT INTO `user`(`name`, age) VALUES('刘备2', 22); INSERT INTO `user`(`name`, age) VALUES('刘备3', 23); INSERT INTO `user`(`name`, age) VALUES('刘备4', 24); INSERT INTO `user`(`name`, age) VALUES('刘备5', 25);

Scenario 1 (no restart): After inserting five rows, deleting IDs 4 and 5, and inserting another row, the new ID becomes 6 because the in‑memory counter continues to increase.

Scenario 2 (after restart): Deleting IDs 6 and 3 leaves the maximum ID at 2. After a server restart, InnoDB resets the auto‑increment counter to 3 (max ID + 1). Subsequent inserts start from 3, demonstrating the reset problem, which does not occur with MyISAM.

In MySQL 8.0 the counter logic changed: InnoDB writes the counter to the redo log and a system table, allowing the value to be recovered after a normal shutdown or even after a crash, using the larger of the stored value and the value derived from the redo log.

Summary:

If MySQL restarts, InnoDB recalculates AUTO_INCREMENT as the current maximum ID plus one.

Manually updating an ID to a higher value can cause duplicate‑key errors when the auto‑increment reaches that value.

MyISAM does not exhibit this reset behavior, so converting tables between engines requires caution.