mysql:服务器重启后保持自动递增计数器-不一致审计表 [英] mysql: keep auto-increment counter after server restart - inconsistent envers audit tables
问题描述
我在 mysql 5.5 上使用 InnoDb 模式.
mysql 5.5 指南状态:
InnoDB使用内存自动递增计数器，只要服务器运行.当服务器停止并重新启动时，InnoDB重新初始化表的第一个INSERT的每个表的计数器，如
InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.
这对我来说是个大问题.我正在使用 envers 进行实体审核.我得到的错误与删除的最后几行"一样多.
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.
假设我正在开始将数据插入到一个空表中.假设要插入10行.然后假设删除最后一个8.在我的表中，我将得到2个实体，分别具有ID 1和2.在审核表中，我将拥有所有10个ID为1到10的实体:ID为3到10的实体将有2个操作:创建操作和删除操作.
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.
自动增量计数器现在在主表中设置为11.重新启动mysql服务自动递增计数器转到3.因此，如果我插入一个新实体，它将以id 3保存.但是在审计表中已经有一个id = 3的实体.该实体已被标记为已创建并已删除.在更新/删除操作期间，这会导致声明失败，因为envers无法处理这种不一致的状态.
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.
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 behaviour and keep auto-increment values while restarting server?
我记得在mysqldump生成的文件中也没有有关自动增量计数器的信息.因此，还原转储也可能是一个问题！
As I remember also in mysqldump generated file there is no infos about autoincrement counter. So also restoring a dump could be a problem!
推荐答案
解决方案是将envers配置属性 org.hibernate.envers.allow_identifier_reuse 设置为 true .
The solution is to set the envers configuration property org.hibernate.envers.allow_identifier_reuse to true.
它的作用是在添加具有相同ID的新条目时设置已删除的记录 revend * 列.因此，在具有相同ID和NULL revend 的两个记录的情况下，您将不会获得条目.
What it does is setting the deleted records revend* columns when a new entry with the same ID is being added. So you won't get entries where there are two records with the same ID and NULL revend.
当前记录可能仍然存在问题，其中多个记录具有相同ID的 revend .不幸的是，此问题必须手动处理.这意味着您必须为这些记录自己设置 revend ，只留下一个带有NULL revend 的记录.
There might still be an issue with current records where there is more than one record with NULL revend for the same ID. This issue, unfortunately, has to be handled manually. This means that you have to set the revend by yourself for those records leaving just one with NULL revend.
这篇关于mysql:服务器重启后保持自动递增计数器-不一致审计表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
