Hibernate OnDelete Cascade not working for MySql but Works on postgres and Ms-Sql

Question

I'm having 2 Entities. Thread entity and Post entity using OnetoOne mapping from Post->Thread.

A Thread entity contains Numerous Posts. I know i should have used OnetoMany instead of OnetoOne, but for avoiding all the Collections problems i'm using OnetoOne

Now the problem is, when i delete a Thread, all the post associated with it must also be removed. I'm successful in doing it by using

@OnDelete(action = OnDeleteAction.CASCADE)

But it works only on Postgres and Ms-SQl but not on MySql(Tried InnoDb as well). The on delete cascade is not generated in the schema generation query.

Following are the code

//Thread Entity
@Id
@GeneratedValue
@Column(name = "thread_id")
private int ThreadID;

---

//Post Entity
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "post_id")
private int PostID;

@OneToOne()
@OnDelete(action = OnDeleteAction.CASCADE)
private thread ThreadID;

I get the following error While deleting an item from Thread entity using the following query

session.delete(session.load(thread.class,1));

Sep 02, 2014 8:33:51 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 1451, SQLState: 23000 Sep 02, 2014 8:33:51 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: Cannot delete or update a parent row: a foreign key constraint fails (forum.post_tbl, CONSTRAINT FK_bfbv5nknqj7ppd5630scimhtb FOREIGN KEY (ThreadID_thread_id) REFERENCES thread_tbl (thread_id)) org.hibernate.exception.ConstraintViolationException: could not execute statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58) at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3343) at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3546) at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:100) at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:377) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:369) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:293) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:339) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1234) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:404) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175) at test.main(test.java:84) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (forum.post_tbl, CONSTRAINT FK_bfbv5nknqj7ppd5630scimhtb FOREIGN KEY (ThreadID_thread_id) REFERENCES thread_tbl (thread_id)) HibernateException : could not execute statement 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:408) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133) ... 14 more BUILD SUCCESSFUL (total time: 2 seconds)

Please help me find a way out of this.

Oralee

Answer 1

This is what I did to resolve this problem based on Spring Boot.

Solution

from

// application.properties  
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

to

spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

Explanation
As you can see below, it seems like the default value for CascadeDelete at MySQL5InnoDBDialect.java only support Cascade.

// MySQLDialect.java
@Override
    public boolean supportsCascadeDelete() {
        return false;
    }

// MySQL5InnoDBDialect.java
@Override
    public boolean supportsCascadeDelete() {
        return true;
    }

Jennifferjennilee

Lost hours hunting down this problem. CascadeType does not work, you need to specifically use @OnDelete plus the dialect is wrong. Combination of these two fixes worked for me. – Piggish

@Piggish Sure, you need @OnDelete(action = OnDeleteAction.CASCADE) on field and I don't follow "the dialect is wrong", can you please give me more detail which dialect is wrong? – Jennifferjennilee

I was just confirming your solution about changing the dialect. – Piggish

Oh I see, I thought you were talking about me when you said "you need to,,," alright never mind. – Jennifferjennilee

FYI if you created DB tables from JPA entities with old dialect, you would need to regenerate them with new dialect. Otherwise your existing DB constraints do not let you remove entities even with new dialect set on the server. – Mosira