[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-1917) Bulk Delete on the owning side of a ManyToMany relation needs to delete corresponding rows from the JoinTable

Eugen Paraschiv (JIRA) noreply at atlassian.com
Fri Mar 2 09:40:51 EST 2012 

    [ https://hibernate.onjira.com/browse/HHH-1917?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45794#comment-45794 ] 

Eugen Paraschiv edited comment on HHH-1917 at 3/2/12 8:40 AM:
--------------------------------------------------------------

I have pasted the endire SQL generated by Hibernate when performing the bulk delete. It looks like the delete is performed before dropping FK constraints. It also looks like MyISAM is indeed not enforcing the FK constraints, which is why it's working on MySQL. 
What do you mean by it's working as designed?
I will provide the full details here as well: 
I have the unidirectional @ManyToMany without any kind of cascading: 

@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 code: 
Privilege existingPrivilege = privilegeService.create( new Privilege( ) );
Role newRole = new Role();
newRole.getPrivileges().add( existingPrivilege );
getService().create( newRole );

roleService.deleteAll(); // failing

This will fail with: 
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

So it's indeed looking like deleting the Roles does not delete the entries in the join table first (I simply want to delete all Roles, and leave the Privileges intact). 
Am I missing something (you mentioned this would be an improvement not a bug). Also, is there a way to simply delete all which will not trigger this issue?
Thanks for the feedback. 
Eugen. 

      was (Author: eugenparaschiv):
    I have pasted the endire SQL generated by Hibernate when performing the bulk delete. It looks like the delete is performed before dropping FK constraints. It also looks like MyISAM is indeed not enforcing the FK constraints, which is why it's working on MySQL. 
What do you mean by it's working as designed?
I will provide the full details here as well: 
I have the unidirectional @ManyToMany without any kind of cascading: 

@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 code: 
Privilege existingPrivilege = privilegeService.create( new Privilege( randomAlphabetic( 6 ) ) );
Role newRole = new Role();
newRole.getPrivileges().add( existingPrivilege );
getService().create( newRole );

roleService.deleteAll(); // failing

This will fail with: 
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

So it's indeed looking like deleting the Roles does not delete the entries in the join table first (I simply want to delete all Roles, and leave the Privileges intact). 
Am I missing something (you mentioned this would be an improvement not a bug). Also, is there a way to simply delete all which will not trigger this issue?
Thanks for the feedback. 
Eugen. 
  
> Bulk Delete on the owning side of a ManyToMany relation needs to delete corresponding rows from the JoinTable
> -------------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-1917
>                 URL: https://hibernate.onjira.com/browse/HHH-1917
>             Project: Hibernate ORM
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 3.2.0.cr2
>         Environment: Hibernate 3.2 cr2
> Hibernate Annotations 3.2.0.CR1 
> Hibernate Entity Manager 3.2.0.CR1 
> Oracle 10.1, with Oracle 10.2 JDBC Driver
>            Reporter: Keenan Ross
>
> Emmanuel says in http://forum.hibernate.org/viewtopic.php?t=961140,
>  [quote]I also know we need to fix some limitations regarding associations table updates / delete and bulk operations[/quote]
> This issue provides a reminder that this task is pending.
> For bulk deletes, consider this scenario: Assume Person and Group entities in a bidirectional ManyToMany relationship with Group as the owning side. This implies a Join table, with the default name of Group_Person. Currently issuing
> em.createQuery("delete from Group").executeUpdate();
> makes no attempt to delete the corresponding rows from Group_Person, even though Group is the owning side, giving constraint errors. Since Group_Person has no corresponding entity, I don't think the spec's prohibition on lifecycle operations or cascading applies here. The only workarround is to individually delete the Groups.
> --keenan

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

More information about the hibernate-issues mailing list