问题描述
我在具有2个节点的群集模式下运行H2。
I run H2 in cluster mode with 2 nodes.
我有两个表。父母和孩子。子项包含指向父表行ID的外键。
我遇到了一个我无法理解的怪异问题:在违反唯一约束之前,一切正常。
I have two tables. A parent, and a child. The child contains a foreign key to the ID of a row of the parent table.
I'm experiencing a weird issue that I can not understand : everything is working OK until I violate a unique constraint.
步骤:
-一切工作正常
-我违反了(在此故意)唯一约束
-现在添加子行时,我在外键(parent.id)上遇到了参照完整性冲突,但是正确添加了子行。
Steps:
- Everything is working normally
- I violate (by purpose here) a Unique constraint
- Now when adding child rows, I get a referential integrity violation on the foreign key (parent.id), but the child row is properly added.
脚本:
create table CHILD(id int auto_increment, name varchar(255), fkey int);
create table PARENT(id int auto_increment, name varchar(255) UNIQUE);
ALTER TABLE `CHILD` ADD FOREIGN KEY (fkey) REFERENCES `PARENT` (`id`) ON DELETE CASCADE;
-- Insert the first parent, id will be '1'. Then insert the child, this works.
insert into PARENT(name) values('parent1');
insert into CHILD(name, fkey) values('child1', 1);
-- By purpose, we violate the Unique constraint violation on PARENT.name : Unique index or primary key violation: "CONSTRAINT_INDEX_8 ON PUBLIC.PARENT(NAME) VALUES ( /* 2 */ 'parent1' )"
insert into PARENT(name) values('parent1');
-- Then I delete this parent (by cascade, all childs are deleted)
delete from PARENT where name='parent1';
-- Then I re-insert this parent, this create the row (3, "parent3")
insert into PARENT(name) values('parent3');
-- I try to insert a child with the parent '3',
-- I get a Referential integrity constraint violation: "CONSTRAINT_3: PUBLIC.CHILD FOREIGN KEY(FKEY) REFERENCES PUBLIC.PARENT(ID) (3)"
insert into CHILD(name, fkey) values('child3', 3);
-- But the child was propertly added !
我发现的解决方法是将最后一个插入替换为
与:
The workaround I found is to replace the last insert into
with :
insert into CHILD(name, fkey) values('child3', SELECT ID from parent where name='parent3');
但这太奇怪了,因为从name =' parent3'
返回 3
。
But this is so weird, because SELECT ID from parent where name='parent3'
returns 3
.
在使用序列而不是auto_increment时也会发生。
当我不使用auto_increment或序列,所以当我自己管理id(但我不想)时,不会发生。
在没有群集的情况下运行H2并不会发生。
已通过H2 v1.3.176和H2 v1.4.189进行了测试。
It also happens when using sequences instead of auto_increment.
It does not happens when I don't use auto_increment or sequences and so when I manage the id myself (but I don't want to).
It does not happens when I run H2 without cluster.Tested with H2 v1.3.176 and H2 v1.4.189.
有人可以解释吗?我在这里弄错了吗?
Can somebody explain this ? Did I make a mistake here ?
推荐答案
H2主要开发人员thomas mueller在github上回答了这个问题:
H2 main developper thomas mueller answered this question on github:
恐怕很难解决。因此,建议不要使用群集
功能。将来可能会在
中删除对它的支持。我希望可以添加
一个新的群集/自动故障转移功能,但这将需要一些时间。
I'm afraid it's hard to fix it. I suggest to not use the cluster feature for this reason. Support for it will probably be removed in the future. I hope a new cluster / automatic failover feature can be added, but this will take some time.
但这可能对您很有趣:
But this might be interesting for you: https://github.com/shesse/h2ha
请参见
我设法使其工作:使用序列并两次插入:
-获取此表序列的nextval,例如: nextid = 从对偶中选择childsequence.nextval
-然后执行 INSERT INTO child
并指定ID nextid
I managed to get it working: using sequences and inserting in two times:
- Get the nextval for this table sequence, eg: nextid =select childsequence.nextval from dual
- Then do your INSERT INTO child
and specify the id nextid
这篇关于H2群集怪异行为:外键上的虚假参照完整性违规的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!