Hi,
I have solved foreign key constraint exceptions using other approaches before, but this one was particularly tricky (14 hours of debugging!)
The process was as follows:
1. create a whole bunch of objects in session 1
2. wait for session 2, 3, 4, etc
(a). session 2-4 does a lot of loads, updates, etc (multiple times over)
3. attempt to delete everything and cleanup in session 1
My problem was that some objects were not reattached to the session. I would then NULL out some associations in the object hierarchy, and these changes were not caught as "dirty" by Hibernate. The resulting change to the deep object (that removed an assocation) would not be updated to the database prior to the deletion of other objects. Hence, the foreign key constraint would be broken.
The solution was to call lock( ) ( or update( ) in my case, due to use of collection arrays that can't be reassociated with the session) on all relevant objects. You can use cascades to reattach objects to the session, but don't forget to reattach all required objects!
(I had forgotten to reattach an important part of the object graph that was *not* cascaded - DirectoryEntry class was delibrately not cascading to DirectoryObject ancestors, which contains most of the data within the entry itself. As seen in the Hibernate mapping, this is the "any" element).
Cheers,
Nigel
PS. Please give credit if this is useful to you!!!
Hibernate version:
3.2.1
Mapping documents:
Shortened version:
Code:
<class name="DirectoryEntry" table="dir_DIRECTORYENTRY" discriminator-value="2">
<cache usage="read-write" />
<id name="id" column="id" type="long" unsaved-value="0">
<generator class="native"/>
</id>
<discriminator column="type" type="int"/>
<version name="version" />
<!-- TODO: Need to put in AAA entries here -->
<property name="cn" column="cn" type="string" />
<many-to-one name="parent" column="parent" class="DirectoryContainer" />
<any name="objRef" id-type="long" lazy="true"> <!-- insert="true" update="true" optimistic-lock="true" -->
<column name="refTableName" />
<column name="refId" />
</any>
<set name="collections" table="pr_COLLECTION" inverse="true">
<cache usage="read-write" />
<key column="entryId" />
<many-to-many column="collectionId" class="meen.env.objmodel.PrCollection" />
</set>
<subclass name="DirectoryContainer" discriminator-value="3">
<map name="children" lazy="true" cascade="all">
<cache usage="read-write" />
<key column="parent" />
<index column="cn" type="string" />
<one-to-many class="DirectoryEntry" />
</map>
</subclass>
</class>
Shortened version:
Code:
public static void main (String[] args)
{
// Initialise config
Configuration cfg = new Configuration ();
cfg.addResource ("meen/objmodel.xml");
cfg.addResource ("meen/typeinfo.xml");
cfg.addResource ("meen/aaa.xml");
cfg.addResource ("meen/repository.xml");
cfg.addResource ("meen/distcoord.xml");
cfg.addResource ("meen/phynet.xml");
cfg.addResource ("meen/devexp.xml");
cfg.addResource ("test/testobject.xml");
cfg.addResource ("meen/sdl.xml");
cfg.addResource ("meen/servmgmt.xml");
SessionFactory sessions = cfg.buildSessionFactory ();
Session session = sessions.openSession();
// Get the service instance
DirectoryContainer root = (DirectoryContainer) session.createQuery (
"FROM DirectoryContainer WHERE (parent is NULL) AND (cn LIKE '@#$root')")
.uniqueResult();
DirectoryContainer entry1 = (DirectoryContainer) root.getChildren().get("services");
DirectoryEntry entry2 = (DirectoryEntry) entry1.getChildren().get("serv_inst_0000000");
// Delete the service instance
ServiceInstance obj1 = (ServiceInstance) entry2.getObjRef();
DirectoryContainer parent1 = entry2.getParent();
parent1.getChildren().remove(entry2.getCn());
entry2.setParent(null);
// Remove any references to target objects and collections
obj1.getIdentities().clear();
obj1.getServiceDefinition().getInstances().remove(obj1);
obj1.setServiceDefinition(null);
// Delete the service instance and 'entry', update parent 'container'
session.delete(entry2);
session.delete(obj1);
session.update(parent1);
// Get the service definition
DirectoryEntry entry3 = (DirectoryEntry) entry1.getChildren().get("test7");
ServiceDefinition obj2 = (ServiceDefinition) entry3.getObjRef();
DirectoryContainer parent2 = entry3.getParent();
parent2.getChildren().remove(entry3.getCn());
entry3.setParent(null);
// Remove any references to target objects and collections
// THIS RECURSIVELY CALLS removeReferences() on each object. For SdlNameExpression:
// setNameSym (null);
// setNameSymDecl (null);
obj2.removeReferences();
// Delete the service definition and 'entry', update parent 'container'
session.delete(entry3);
session.delete(obj2);
session.update(parent2);
// Commit changes
session.flush();
try {
session.connection().commit();
} catch (SQLException se) {System.out.println (se.getMessage());}
session.close();
sessions.close();
}
Full stack trace of any exception that occurs:Code:
11:42:51,000 WARN JDBCExceptionReporter:77 - SQL Error: 0, SQLState: null
11:42:51,000 ERROR JDBCExceptionReporter:78 - DELETE statement conflicted with COLUMN REFERENCE constraint 'FKC7CC224B444ADEE3'. The conflict occurred in database 'test', table 'sdl_STATEMENT', column 'ne_symbol'.
11:42:51,031 ERROR AbstractFlushingEventListener:301 - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:218)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2429)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2647)
at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:74)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:144)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at meen.env.repository.HibernateRepository.commit(HibernateRepository.java:280)
at meen.servmgr.gui.GMainWindow.cExperimentRuns(GMainWindow.java:1927)
at meen.servmgr.gui.GMainWindow$9.handleEvent(GMainWindow.java:253)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
at meen.servmgr.gui.GMainWindow.vMainLoop(GMainWindow.java:314)
at meen.servmgr.ServMgr.Run(ServMgr.java:249)
at meen.servmgr.ServMgr.main(ServMgr.java:154)
Caused by: java.sql.BatchUpdateException: DELETE statement conflicted with COLUMN REFERENCE constraint 'FKC7CC224B444ADEE3'. The conflict occurred in database 'test', table 'sdl_STATEMENT', column 'ne_symbol'.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:578)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
... 22 more
Database exception at 1185327771031
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:218)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2429)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2647)
at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:74)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:144)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at meen.env.repository.HibernateRepository.commit(HibernateRepository.java:280)
at meen.servmgr.gui.GMainWindow.cExperimentRuns(GMainWindow.java:1927)
at meen.servmgr.gui.GMainWindow$9.handleEvent(GMainWindow.java:253)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
at meen.servmgr.gui.GMainWindow.vMainLoop(GMainWindow.java:314)
at meen.servmgr.ServMgr.Run(ServMgr.java:249)
at meen.servmgr.ServMgr.main(ServMgr.java:154)
Caused by: java.sql.BatchUpdateException: DELETE statement conflicted with COLUMN REFERENCE constraint 'FKC7CC224B444ADEE3'. The conflict occurred in database 'test', table 'sdl_STATEMENT', column 'ne_symbol'.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:578)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
... 22 more
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:218)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2429)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2647)
at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:74)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:144)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at meen.env.repository.HibernateRepository.commit(HibernateRepository.java:280)
at meen.servmgr.gui.GMainWindow.cExperimentRuns(GMainWindow.java:1927)
at meen.servmgr.gui.GMainWindow$9.handleEvent(GMainWindow.java:253)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
at meen.servmgr.gui.GMainWindow.vMainLoop(GMainWindow.java:314)
at meen.servmgr.ServMgr.Run(ServMgr.java:249)
at meen.servmgr.ServMgr.main(ServMgr.java:154)
Caused by: java.sql.BatchUpdateException: DELETE statement conflicted with COLUMN REFERENCE constraint 'FKC7CC224B444ADEE3'. The conflict occurred in database 'test', table 'sdl_STATEMENT', column 'ne_symbol'.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:578)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
... 22 more
Name and version of the database you are using:
Microsoft SQL Server 2000 SP4
The generated SQL (show_sql=true):
... not relevant...
Debug level Hibernate log excerpt:
... not relevant...
Login
FAQ
Search