Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

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

hibernate java jpa mysql ondelete sql database

This topic has been archived. This means that you cannot reply to this topic.
1 reply to this topic

#1 rakz

rakz

    CC Newcomer

  • Member
  • PipPip
  • 20 posts

Posted 03 September 2014 - 08:22 AM

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;

 
<hr/>
 

    //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.


Edited by rakz, 03 September 2014 - 08:34 AM.


#2 wim DC

wim DC

    Roar

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 2681 posts

Posted 04 September 2014 - 01:50 AM

class A{
    @OneToOne
    @OnDelete(cascade...)
    private B b;
}

Well I guess hibernate would delete B if A is deleted in this case and not the other way around.

 

 

Perhaps it works for postgres & ms-sql because hibernate creates DB triggers for the FK there, but not for mysql (dunno why).

 

I would just change it to a manyToOne instead of @OneToOne

@ManyToOne(targetEntity = Thread.class)
@JoinColumn(name = "thread_id")

I don't see why it would cause problems and it makes more sence.


Edited by wim DC, 04 September 2014 - 01:51 AM.





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download