6

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.

Thanks

  • 1
    What do you mean by "keep the last autoincrement value"? After/before restart your DB should be in the same state, don't you think? – Destroyica Oct 19 '14 at 8:54
  • It is not so in innodb. This is calculated as max id +1 – gipinani Oct 19 '14 at 9:01
  • @gipinani take a look at my answer =) dba.stackexchange.com/a/188223/2639 I do think it is inno that was doing this though. The bug upstream was filed and patched on inno. – Evan Carroll Oct 11 '17 at 19:20
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,

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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