MySql autoincrement counter resetting after server restart 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
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.



WL#6204: InnoDB persistent max value for autoinc columns


MariaDB fix

Also this was MariaDB 10.2.4 released on July 12, 2017


MDEV-6076 Persistent AUTO_INCREMENT for InnoDB.


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


Why PostgreSQL is better than MySQL