Referential integrity constraint violation: Hibernate, @OneToMany with CascadeType.ALL, Trigger AFTER INSERT

[GoogleCodeExporter]

I have a problem with trigger after insert and Hibernate.

I try to reproduce it with a separate simple test case, but failed - in 
simple test case all works fine. :(

So I try do describe how it is in real app.

I have Card object with Walltes object
....
 @OneToMany(cascade = CascadeType.ALL)
 @JoinTable(name="CARD_WALLETS",
            joinColumns={@JoinColumn(name = "CARD_ID")},
            inverseJoinColumns = {@JoinColumn(name = "WALLET_ID")})
 public List<Wallet> getWallets()
 {}
....
So wallets are saved, updated and deleted with card.

But if I add after insert trigger for Cards table I have following error:

-------------------------
14:23:19 DEBUG o.h.u.JDBCExceptionReporter - Could not execute JDBC batch 
update [/* insert collection row ka.app.domain.Card.wallets */ insert into 
card_wallets (card_id, wallet_id) values (?, ?)]
Referential integrity constraint violation: FK_CRD_WLL_CRD: 
PUBLIC.CARD_WALLETS FOREIGN KEY(CARD_ID) REFERENCES PUBLIC.CARDS(ID); SQL 
statement:
/* insert collection row ka.app.domain.Card.wallets */ insert into 
card_wallets (card_id, wallet_id) values (?, ?) [23002-119]
    at org.h2.jdbc.JdbcPreparedStatement.executeBatch 
JdbcPreparedStatement.java:1097) [ZZZ%20H2/:na]
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch 
BatchingBatcher.java:70) [hibernate-3.3.2.GA.jar:na]
    at org.hibernate.jdbc.AbstractBatcher.executeBatch 
AbstractBatcher.java:268) [hibernate-3.3.2.GA.jar:na]
    at org.hibernate.engine.ActionQueue.executeActions 
ActionQueue.java:266) [hibernate-3.3.2.GA.jar:na]
    at org.hibernate.engine.ActionQueue.executeActions 
ActionQueue.java:171) [hibernate-3.3.2.GA.jar:na]
    at 
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions
(AbstractFlushingEventListener.java:321) [hibernate-3.3.2.GA.jar:na]
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush
(DefaultFlushEventListener.java:50) [hibernate-3.3.2.GA.jar:na]
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028) 
[hibernate-3.3.2.GA.jar:na]
....
---------------------------------

if I remove trigger - all work fine.

In trigger I just insert info into separate Z_HISTORY table:
...
protected static final String SQL_INSERT_INTO_HISTORY = "INSERT INTO 
PUBLIC.Z_HISTORY(USER, ACTION_TYPE, SCHEMA_NAME, TABLE_NAME, REF_ID, 
OLD_VALUE, NEW_VALUE) VALUES (?, ?, ?, ?, ?, ?, ?)";
...
public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws 
SQLException{
 PreparedStatement ins = conn.prepareStatement(SQL_INSERT_INTO_HISTORY);
 ins.setLong(1, 1L);
 ins.setInt(2, type);
 ins.setString(3, schemaName);
 ins.setString(4, tableName);
 ins.setLong(5, (Long) newRow[0]);
 ins.setNull(6, Types.VARCHAR);
 ins.setString(7, this.format(newRow));
 ins.execute();}

----------------------------
So, I add H2 sources to my project and try to debug.
Under debugger I see that all PK values for Card object and Wallet objects 
are PRESENT (I think it means that card and wallets are inserted into DB 
already) and when hibernate try to insert collection info into special 
table CARD_WALLETS(CARD_ID, WALLET_ID) it failed.

I found that H2 throw an error at:
 org.h2.table.Table.fireConstraints(Session, Row, Row, boolean)
 ...
   constraint.checkRow(session, this, oldRow, newRow);
   at org.h2.constraint.ConstraintReferential.checkRow(Session, Table, 
Row, Row) 
 ......
 HERE >> if (!found(session, refIndex, check, null)) {
            throw Message.getSQLException
(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1, 
getShortDescription());}

It seems that some how when there AFTER INSERT TRIGGER PRESENTS H2 failed 
to find referenced ID in index...
May be it not written when trigger is present?
Or may be h2 write it later and that is why when reference checked it 
failed.

What I could check more?

Original issue reported on code.google.com by kua...@gmail.com on 4 Oct 2009 at 6:57

[GoogleCodeExporter]

Could you post a simple, standalone test case that reproduces the problem? It 
would
be great if the test case does not have any dependencies except the H2 jar file 
and
Hibernate (a simple Java class that is run using a static main method). Please
include any initialization code (CREATE TABLE, INSERT and so on) in the Java 
class.

Original comment by thomas.t...@gmail.com on 4 Oct 2009 at 9:14

[GoogleCodeExporter]

Tomas, I tried but failed to reproduce this bug.
But under debugger I noticed what caused the problem.

In trigger I insert some info into Z_HISTORY table.
Z_HISTORY has and ID column on sequence
ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SEQ_Z_HISTORY) NOT NULL 
NULL_TO_DEFAULT 
SEQUENCE PUBLIC.SEQ_Z_HISTORY
and Z_HISTORY has a primary key
 ALTER TABLE PUBLIC.Z_HISTORY ADD CONSTRAINT PUBLIC.PK_Z_HISTORY PRIMARY KEY(ID);

So, after trigger finished THE WRONG IDENTITY RETURNED to hibernate
instead of ID of newly inserted _Card_ the ID of newly inserted Z_HISTORY 
inserted
and after that FK_CONSTRAINT fire the exception, because there no card with 
such ID.

It seems that H2 return LAST generated by ANY sequence ID, may be it is Ok, but 
what 
if something was generated inside trigger?

So as temporary workaround I remove ID column from Z_HISTORY table, and now all 
works fine.

This bug is very critical for me :(.

P.S. I don't understand why I have this bug in my app, but don't have from 
JUnit 
test case...

Original comment by kua...@gmail.com on 4 Oct 2009 at 11:51

[GoogleCodeExporter]

Finally, I do it.

I understand why my test case don't show the bug :)
Because, Z_HISTORY has no rows and returned generated ID was good for inserting.
Now I set sequence for Z_HISTORY at 1000 and the bug appeared.

In attach Eclipse project with test case.
To run it you need Hibernate hibernate-3.3.2.GA and slf4j + some slf4j 
implementation, I use logback, you may use another.
And one more: you should configure paths in test and in hibernate.cfg.xml
I write about it in test code comments.

It was a real hunt :) for a bug.

Original comment by kua...@gmail.com on 4 Oct 2009 at 1:25

Attachments:

• [ZZZ H2 bugs_2009-10-04-21.zip](https://storage.googleapis.com/google-code-attachments/h2database/issue-129/comment-3/ZZZ H2 bugs_2009-10-04-21.zip)

[GoogleCodeExporter]

Hello, Thomas.

Have you try my test case? This problem is quite critical for me.
I need to know it is H2 or Hibernate bug and try to find out workaround.

Original comment by kua...@gmail.com on 5 Oct 2009 at 2:10

[GoogleCodeExporter]

> Have you try my test case?

No, I didn't have time yet. I'm sorry.

Original comment by thomas.t...@gmail.com on 7 Oct 2009 at 7:03

[GoogleCodeExporter]

Hello, Thomas.

If you have time, please look my test case.
This bug (or may be feature) is very important for me.

Original comment by kua...@gmail.com on 11 Oct 2009 at 9:43

[GoogleCodeExporter]

Hello, Thomas.

I just interesting - have you try my test case?

Original comment by kua...@gmail.com on 29 Oct 2009 at 2:43

[GoogleCodeExporter]

Hi,

Sorry, I didn't have time yet... Could you upload the dependencies somewhere 
please?
It takes a lot of time just downloading all the jar files from the different
projects... Sorry, I know I'm lazy... but I guess you have the libraries 
already, so
maybe if you have time, could you zip them and upload them somewhere? That 
would be
great!

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 3 Nov 2009 at 7:40

[GoogleCodeExporter]

hi! I upload all dependencies to:
 http://rapidshare.com/files/302129110/h2_test_deps.rar.html

I hope this time you fix the bug :)
------------------
Alexey

Original comment by kua...@gmail.com on 4 Nov 2009 at 3:16

[GoogleCodeExporter]

This will be fixed in the next release.

Original comment by thomas.t...@gmail.com on 5 Nov 2009 at 8:17

• Changed state: Accepted

[GoogleCodeExporter]

Some more details: The problem is that the generated value for "owner.id" is 
lost.
This happens as follows:

- You insert a row into "owner", the identity value becomes 1.
- The triggers inserts a row into "z_history", the identity becomes 1000.
- Hibernate calls getGeneratedKeys(), which now returns 1000 (that's wrong of 
course)

A workaround is not to use getGeneratedKeys (I think you can configure that in
Hibernate). Another workaround is to 'reset' the identity value in the trigger. 
It's
really just a hack:

      + "CREATE SEQUENCE FUZZ;\n"

    public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws
SQLException {
        ResultSet rs = conn.createStatement().executeQuery("CALL IDENTITY()");
        rs.next();
        long identity = rs.getLong(1);
        rs.close();
        ...
        PreparedStatement ins = conn
                .prepareStatement("INSERT INTO PUBLIC.Z_HISTORY (OLD_VALUE,
NEW_VALUE) VALUES (?, ?)");
        ins.setString(1, "foo");
        ins.setString(2, "bar");
        ins.execute();
        ...
        conn.createStatement().execute("ALTER SEQUENCE FUZZ RESTART WITH " + identity
+ "; CALL NEXT VALUE FOR FUZZ");
    }

This should work (it did for me). The real solution is to implement 
SCOPE_IDENTITY()
and use this for getGeneratedKeys(). I have now implemented this.

Original comment by thomas.t...@gmail.com on 5 Nov 2009 at 8:20

[GoogleCodeExporter]

>> You insert a row into "owner", the identity value becomes 1.
>> The triggers inserts a row into "z_history", the identity becomes 1000.
>> Hibernate calls getGeneratedKeys(), which now returns 1000 (that's wrong of 
course)

Yes. You are right, actually I try to describe this behavior in comments #2 & 
#3.

It would be grate if you implement SCOPE_IDENTITY(), because I don't really 
want to 
hacking around this problem. It would be great if it works "out of the box".
----------------
Alexey

Original comment by kua...@gmail.com on 6 Nov 2009 at 5:28

[GoogleCodeExporter]

Should be fixed with version 1.2.123

Original comment by thomas.t...@gmail.com on 8 Nov 2009 at 1:30

• Changed state: Fixed