Referential integrity constraint violation with Hibernate and @ManyToMany mapping (doesn't happen in MySQL)

[Eugen Paraschiv]

Hi,
I'm working with the following:
h2 - 1.3.164
Hibernate - 4.1.0.Final
I have a Role entity:

@Entity
public class Role implements IEntity{
   
    @Id @GeneratedValue( strategy = GenerationType.AUTO ) @Column( name = "ROLE_ID" ) private Long id;
   
    @ManyToMany( fetch = FetchType.EAGER )
    @JoinTable( joinColumns = { @JoinColumn( name = "ROLE_ID",referencedColumnName = "ROLE_ID" ) },inverseJoinColumns = { @JoinColumn( name = "PRIV_ID",referencedColumnName = "PRIV_ID" ) } )
    private Set< Privilege > privileges;
}

And I'm running the following test:

        Privilege existingPrivilege = privilegeService.create( new Privilege( randomAlphabetic( 6 ) ) );
        final Role newRole = new Role();
        newRole.getPrivileges().add( existingPrivilege );
        getService().create( newRole );
       
        roleService.deleteAll();
       
Which essentially creates a Privilege, then adds it to a new role and persists the role; then deletes all roles.
Everything works fine until the deleteAll call, which fails in H2 (but works the way it should in MySQL):
...
Caused by: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK81B4C22896D682CC: PUBLIC.ROLE_PRIVILEGE FOREIGN KEY(ROLE_ID) REFERENCES PUBLIC.ROLE(ROLE_ID)"; SQL statement:
delete from Role [23503-164]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:398)
    at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:415)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:291)
    at org.h2.table.Table.fireConstraints(Table.java:862)
    at org.h2.table.Table.fireAfterRow(Table.java:879)
    at org.h2.command.dml.Delete.update(Delete.java:99)
    at org.h2.command.CommandContainer.update(CommandContainer.java:73)
    at org.h2.command.Command.executeUpdate(Command.java:226)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:129)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    ... 65 more

I'm guessing this is a bug (I have seen some other, similar bugs open (or resolved) in h2.
Should I open up a new issue for it?
Thanks.
Eugen.

[Noel Grandin]

please

- turn on hibernate's tracing (or H2's tracing)
- get the raw SQL calls
- reproduce the problem using raw SQL

than we should be able to help you further.

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/NdgwvtnqIrQJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Eugen Paraschiv]

So, the tests that reproduces the issue is doing a few inserts before the failing deleteAll() call, so there is some additional SQL involved; I will paste the test as well as all generated SQL, and will highlight the relevant bits:
The test code:

final Privilege existingAssociation = getAssociationService().create( new Privilege( randomAlphabetic( 6 ) )
final Role newResource = createNewEntity();
newResource.getPrivileges().add( existingAssociation );
getService().create( newResource );
privilegeService.deleteAll();

The SQL:
Hibernate: alter table Principal_Role drop constraint FK71AEF6277BF8AB4A
Hibernate: alter table Principal_Role drop constraint FK71AEF627323F55D9
Hibernate: alter table Role_Privilege drop constraint FK81B4C2287DC9D9CA
Hibernate: alter table Role_Privilege drop constraint FK81B4C22896D682CC
Hibernate: drop table Principal if exists
Hibernate: drop table Principal_Role if exists
Hibernate: drop table Privilege if exists
Hibernate: drop table Role if exists
Hibernate: drop table Role_Privilege if exists
Hibernate: create table Principal (PRINCIPAL_ID bigint generated by default as identity, name varchar(255) not null unique, password varchar(255) not null, primary key (PRINCIPAL_ID))
Hibernate: create table Principal_Role (Principal_PRINCIPAL_ID bigint not null, roles_ROLE_ID bigint not null, primary key (Principal_PRINCIPAL_ID, roles_ROLE_ID), unique (roles_ROLE_ID))
Hibernate: create table Privilege (PRIV_ID bigint generated by default as identity, name varchar(255) not null unique, primary key (PRIV_ID))
Hibernate: create table Role (ROLE_ID bigint generated by default as identity, name varchar(255) not null unique, primary key (ROLE_ID))
Hibernate: create table Role_Privilege (ROLE_ID bigint not null, PRIV_ID bigint not null, primary key (ROLE_ID, PRIV_ID))
Hibernate: alter table Principal_Role add constraint FK71AEF6277BF8AB4A foreign key (roles_ROLE_ID) references Role
Hibernate: alter table Principal_Role add constraint FK71AEF627323F55D9 foreign key (Principal_PRINCIPAL_ID) references Principal
Hibernate: alter table Role_Privilege add constraint FK81B4C2287DC9D9CA foreign key (PRIV_ID) references Privilege
Hibernate: alter table Role_Privilege add constraint FK81B4C22896D682CC foreign key (ROLE_ID) references Role
Hibernate: delete from Privilege
Hibernate: delete from Role
Hibernate: delete from Principal
Hibernate: insert into Privilege (PRIV_ID, name) values (null, ?)
Hibernate: insert into Role (ROLE_ID, name) values (null, ?)
Hibernate: insert into Role_Privilege (ROLE_ID, PRIV_ID) values (?, ?)

Hibernate: delete from Privilege
Hibernate: alter table Principal_Role drop constraint FK71AEF6277BF8AB4A
Hibernate: alter table Principal_Role drop constraint FK71AEF627323F55D9
Hibernate: alter table Role_Privilege drop constraint FK81B4C2287DC9D9CA
Hibernate: alter table Role_Privilege drop constraint FK81B4C22896D682CC
Hibernate: drop table Principal if exists
Hibernate: drop table Principal_Role if exists
Hibernate: drop table Privilege if exists
Hibernate: drop table Role if exists
Hibernate: drop table Role_Privilege if exists

The exception:
org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK81B4C2287DC9D9CA: PUBLIC.ROLE_PRIVILEGE FOREIGN KEY(PRIV_ID) REFERENCES PUBLIC.PRIVILEGE(PRIV_ID)"; SQL statement:
delete from Privilege [23503-164]

    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:398)
    at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:415)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:291)
    at org.h2.table.Table.fireConstraints(Table.java:862)
    at org.h2.table.Table.fireAfterRow(Table.java:879)
    at org.h2.command.dml.Delete.update(Delete.java:99)
    at org.h2.command.CommandContainer.update(CommandContainer.java:73)
    at org.h2.command.Command.executeUpdate(Command.java:226)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:129)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    ... 65 more

Hope this makes debugging and solving the issue easier.
Thanks.
Eugen.

> h2-database+unsubscribe@googlegroups.com.

[Noel Grandin]

Hmm, sorry to say that that is a bug in Hibernate.

It should either be deleting the Role_Privilege entry before deleting
from Privilege, or it should be deleting the foreign key constraints.

I think that the Hibernate code was written with the expectation that
the DB supports cascading deletes, which H2 does not.

>> > h2-database...@googlegroups.com.

>> > For more options, visit this group at
>> > http://groups.google.com/group/h2-database?hl=en.
>

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/h2-database/-/PQKn4I7jBHgJ.

>
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to

> h2-database...@googlegroups.com.

[Noel Grandin]

As to why it works in MySQL, that would be because MySQL does not
enforce FK constraints.