Search code examples
sqljdbch2corruption

H2 Referential Integrity violation when adding row of a child table in cluster mode

TL; DR: Referential integrity violation triggered when no violation happened.
I run H2 in cluster mode with 2 nodes.
I have two tables in a H2 database (v1.4.189), a parent and a child. The child contains a foreign key to the ID of a row of parent table. Usually, I don't get any errors when inserting a row in child table.
But after a while, I'm getting this error when inserting : 

Referential integrity constraint violation: "CONSTRAINT_1FE: PUBLIC.CHILD FOREIGN KEY(fkey)
REFERENCES PUBLIC.PARENT(ID) (86)"

The strange thing is that the INSERT INTO data that produced the error was successfully inserted, and that there is no foreign key constraint violation !

I've tried to document the exact steps to reproduce the error, but with a fresh database, the error never happens :

drop table CHILD;
drop table PARENT;
create table CHILD(id int auto_increment, name varchar(255), fkey int);
create table PARENT(id int auto_increment, name varchar(255));

ALTER TABLE `CHILD` ADD FOREIGN KEY (fkey) REFERENCES `PARENT` (`id`);

insert into PARENT(name) values('hello');
insert into PARENT(name) values('world');
select * from PARENT; 


insert into CHILD(name, fkey) values('hello', 1); 
-- this works for a while, but someday the Referential integrity error 
-- will pop, but data will be added anyway (wtf?)
insert into CHILD(name, fkey) values('world', 2);

On the database, I'm only doing simple things like selecting, inserting, deleting...

The amusing fact is that after this error happened once, I get another strange errors : when deleting (or updating) rows of the CHILD table, the DELETE FROM or UPDATE functions always return 0, even if some rows have been deleted... (also jdbc executeUpdate() always returns 0)

Is the database corrupted at some point ?

The only workaroud I found to fix this error, is to delete all tables and recreate the tables, which is not what I want to do.

Solution

  • H2 main developper thomas mueller answered this question on github:

    This is one of the documented limitations of the cluster feature, see also "Clustering Algorithm and Limitations": "Using auto-increment and identity columns is currently not supported."

    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

    See issue on github

    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