[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
8:40 a.m.
[
https://hibernate.onjira.com/browse/HHH-1917?page=com.atlassian.jira.plug...
]
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
4701
days inactive
4701
days old
hibernate-issues@lists.jboss.org
0 comments
1 participants
participants (1)
-
Eugen Paraschiv (JIRA)