I need a way to keep auto-increment counter between mysql service restart.

As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last autoincrement value?

This is a big problem for me. I'm using envers to keep entities audits. I get as many errors as many "last rows" I delete.

Suppose I'm starting insert data into an empty table. Suppose to insert 10 rows. Then suppose to delete the last 8. In my table I will have as result 2 entities, with id 1 and 2 respectively. In the audit table I will have all 10 entities, with id from 1 to 10: entities with id from 3 to 10 will have 2 action: create action and delete action.

auto-increment counter is now setted to 11 in main table. Restarting mysql service auto-increment counter goes to 3. So if I insert a new entity, it will be saved with id 3. But in audit table there is already an entity with id = 3. That entity is already marked as created and deleted. It result in an assertion failure during update /delete action because envers cannot handle this inconsistent state.



2 Answers 2


MySQL fix

After 14.5 years this is fixed in MySQL version 8.0

[6 Oct 16:40] Daniel Price Posted by developer: Fixed in 8.0.0 by WL#6204.

MariaDB fix

Also this was MariaDB 10.2.4 released on July 12, 2017

I found out this change was finally added on the 2ndquadrant blog,


If your auto-increments do have a meaning outside of an arbitrary indentifier, maybe they shouldn't be auto_increments, but simple int or bigint unsigned.

You can drop the auto_increment particle and just insert max(id)+1, but that would create you all sorts of concurrency problems (that the auto_increment solves for you automatically).

You could store the auto_increment on a separate table or table comment, and edit the table's auto_increment or insert auto_increment value - 1 and then delete it, depending on the ENGINE. But again, I would think twice why you want this before implementing it. Remember that gaps can be produced at any time.

EDIT: that is reasonable- I would recommend not deleting rows (mark them as "deleted=true") or following the advice above: on start, execute a query to check that your auto_increment (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE table_schema='database' AND table_name='table') is larger than max(audited_id) and perform a fake insert. You can put that logic with --init-file or on a trigger, as seen on this comment.


Not the answer you're looking for? Browse other questions tagged or ask your own question.