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,

| improve this answer | |
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.

| improve this answer | |

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