Hibernate many-to-many cannot delete


#1

I’m using hibernate.

@Entity
@Table(name="user")
public class User{
@ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
  private List<Role> roles;
}
@Entity
@Table(name="role")
public class Role {
@ManyToMany(mappedBy = "roles", fetch = FetchType.LAZY)
  private List<User> users;
}

through repository.delete Role
Cannot delete the association table relationship.Exception:
Cannot delete or update a parent row: a foreign key constraint fails


#2

Try clearing the users collections before deleting:

role.getUsers().clear().

#3

Still no, you need to set up cascade property?


#4

I find that defining @query (“delete form where role_name=:roleName”) in Repository can be deleted and generated:
Hibernate: delete from user_roles where (role_id) in (select id from role where role_name=?).
I can’t understand.


#5

For many-to-many, CascadeType.REMOVE is a terrible idea.

For more details, check out this article.


#6

I use @jointable at both ends. Do you recommend doing this?


#7

What you need to do is to:

  1. Add the data access logic so we know what you are doing.
  2. Activate the SQL log.
  3. Add the stacktrace of the issue so we know exactly what fails. It might be due to some other entity.

I use @jointable at both ends. Do you recommend doing this?

No, you don’t use that. You use @JoinTable on one end and mappedBy on the other.

I find that defining @query (“delete form where role_name=:roleName”) in Repository can be deleted and generated:
Hibernate: delete from user_roles where (role_id) in (select id from role where role_name=?).
I can’t understand.

  1. You have a typo delete form. Please copy-paste the exact code you have, don’t type it.
  2. This is an SQL query. Did you really run it as such? Or are you running it like a JPQL query instead?
  3. I can’t understand what you are doing either. Check the requirements I’ve given you above for what you need to do so that your question is crystal clear.

#8

I expect that deleting the role will delete the association in the relational table.

But now there are exceptions.:

2018-01-22 17:50:40.785  WARN 18028 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1451, SQLState: 23000
2018-01-22 17:50:40.786 ERROR 18028 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_groups`, CONSTRAINT `FKcxjeeiqtt3g4v86stce81hpfh` FOREIGN KEY (`group_id`) REFERENCES `user_group` (`id`))
2018-01-22 17:50:40.788  INFO 18028 --- [io-18086-exec-8] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2018-01-22 17:50:40.805 ERROR 18028 --- [io-18086-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_groups`, CONSTRAINT `FKcxjeeiqtt3g4v86stce81hpfh` FOREIGN KEY (`group_id`) REFERENCES `user_group` (`id`))
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)

User Entity

@Setter
@Getter
@Entity
@Table(name = "user", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"}),@UniqueConstraint(columnNames = {"email"})})
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "entityCache")
public class User {
  @Id
  @Column(name = "id", length = 36)
  private String id;
  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "created_time", nullable = false)
  private Date createdTime;
  @Column(name = "username", nullable = false)
  private String username;
  @Column(name = "enabled", nullable = false)
  private Boolean enabled;
  @Column(name = "email_verified", nullable = false)
  private Boolean emailVerified;
  @Column(name = "first_name")
  private String firstName;
  @Column(name = "last_name")
  private String lastName;
  @Column(name = "email")
  private String email;
  @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE, region = "entityCollectionCache") 
  @ElementCollection(targetClass = String.class)
  @CollectionTable(name = "user_actions", joinColumns = @JoinColumn(name = "action_id"))
  @Column(name = "action")
  private List<String> requiredActions;

  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
  private List<ClientRole> roles;
  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "user_groups", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "group_id", referencedColumnName = "id"))
  private List<UserGroup> userGroups;
}

Role

@Setter
@Getter
@Entity
@Table(name = "client_role", uniqueConstraints = {@UniqueConstraint(columnNames = {"role_name","client_id"})})
@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "entityCache")
public class ClientRole {
  @Id
  @Column(name = "id", length = 128, nullable = false)
  private String id;
  @Column(name = "name", length = 255, nullable = false)
  private String name;
  @Column(name = "description", length = 2000)
  private String description;
  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "client_role_composites", joinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")}, inverseJoinColumns = {@JoinColumn(name = "composite_role_id", referencedColumnName = "id")})
  private List<ClientRole> composites;
  @ManyToMany(mappedBy = "roles", fetch = FetchType.LAZY)
  private List<User> users;
}

remove method

@Transactional
  public void removeRole(String clientInfoId, String... roleName) {
    for(int i = 0; i< roleName.length; i++) {
      ClientRole role = repository.findByNameAndClientInfo(roleName[i], clientInfoId);
      roleRepository.delete(role.getId());
    }
  }

#9

That’s for the user_groups table. But since you haven’t activated the SQL log, as I told you, you don’t see the flow of SQL statements.

There’s something else in your code that you didn’t show us. Either you have a DB-level CASCADE on DELETE a FK-level or there is some other code trying to delete a Group entity as well.


#10

I’m sorry:

I use Spring Boot Jpa auto config,spring boot version:1.5.9, hibernate-core :5.0.12

It cannot automatically delete the user_roles relationship.

Hibernate: delete from role_composites where role_id=?
Hibernate: delete from client_role where id=?
2018-01-23 10:00:30.093  WARN 11132 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1451, SQLState: 23000
2018-01-23 10:00:30.094 ERROR 11132 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_roles`, CONSTRAINT `FKa8ud4r12oeoud33apgv613oqf` FOREIGN KEY (`role_id`) REFERENCES `client_role` (`id`))
2018-01-23 10:00:30.095  INFO 11132 --- [io-18086-exec-8] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2018-01-23 10:00:30.150 ERROR 11132 --- [io-18086-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_roles`, CONSTRAINT `FKa8ud4r12oeoud33apgv613oqf` FOREIGN KEY (`role_id`) REFERENCES `client_role` (`id`))
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2843)

#11

I could replicate the issue and created this Jira ticket HHH-12239.

Thanks for mentioning it.


#12

Thank you. I will keep track of this issue.