Mysql: keep auto increment counter after server reboot - inconsistent change tables
I am using the diagram InnoDb
on mysql 5.5
.
mysql 5.5
:
InnoDB uses an in-memory auto-increment counter, assuming the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT for the table, as described earlier.
This is a big problem for me. I am using envers
for entity audits. I get as many errors as there are "last lines" that I delete.
Suppose I start to insert data into an empty table. Suppose to insert 10 lines. Then suppose you want to delete the last 8. In my table, I will have 2 entities with IDs 1 and 2, respectively. In the audit table, I will have all 10 objects with ID from 1 to 10: objects with ID from 3 to 10 will have 2 actions: create action and delete action.
Auto-increment counteris now set to 11 in the main table. The mysql service auto-increment counter is restarting 3. So if I insert a new object it will be saved with ID 3. But there is already an object in the audit table with id = 3. This object is already marked as created and deleted, This results in an error assertions during update / delete action because envers cannot handle this inconsistent state.
ERROR org.hibernate.AssertionFailure - HHH000099: an assertion failure occured (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session): java.lang.RuntimeException: Cannot update previous revision for entity Customer_H and id **.
Is there a way to change this behavior and keep the auto increment values on server restart?
As I recall, there is no auto-increment counter information in the generated mysqldump file. So dump recovery can be a challenge!
source to share
A simple solution for this type of database is to use soft deletes rather than physical deletes.
Soft delete is where you add a field to your table that acts as a status / indicator as to whether a row is considered "active" or "inactive". From this you create all your queries in your table where the indicator column equals the "active" sentinel value, ignoring the inactive ones.
There are other options available, but these are simple and simple enough to implement.
source to share