MySQL, InnoDB and foreign keys

orichaud · **Posted:** Wed Jun 09, 2004 5:50 pm

Hi all,

First of all, I'm using Hibernate 2.x with MySQL 4.x. Hibernate is run as a Jboss service and is integrated with the underlying transaction manager.

MySQL uses InnoDB tables because with MyISAM tables, autocmmit can"t be disabled. As a consequence, I wasn't able to rollbacl changes as changes were committed on the fly. But the problem I'm now reporting didn't occur with MyISAM tables.

When I delete a group of objects, mySQL refuses to commit and Hibernate throws the following exception :

net.sf.hibernate.JDBCException: Could not synchronize database state with session: General error, message from server: "Cannot delete or update a parent row: a foreign key constraint fails". The foreign keys is generated by hibernate to handle binary associations.

Code:

Caused by: java.sql.SQLException: General error,  message from server: "Cannot delete or update a parent row: a foreign key constraint fails"
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1626)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:886)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:945)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:1844)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1602)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1488)
   at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:324)
   at net.sf.hibernate.persister.NormalizedEntityPersister.delete(NormalizedEntityPersister.java:606)
   at net.sf.hibernate.impl.ScheduledDeletion.execute(ScheduledDeletion.java:22)
   at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2100)
   at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2066)

Here is the corresponding SQL :

Code:

orichaud · **Posted:** Wed Jun 09, 2004 6:18 pm

By the way, is it related to the cascade delete option for relationships?

RobJellinghaus · **Joined:** Thu Jan 08, 2004 6:17 pm **Posts:** 278

Yes, it probably is.

Hibernate is telling you what the problem is. Turn on "show_sql" and see what sql Hibernate is trying to execute when it gets the exception. This will tell you exactly what objects you should have deleted before trying to delete the still-referenced-as-foreign-keys objects that are failing.

Cheers,
ROb