Re: [jira] Resolved: (OPENJPA-39) Cascade delete does not work with foreign key constraints

Mon, 11 Sep 2006 17:54:42 -0700 

Hi Abe,
- When you delete a parent object and the operation cascades to children, the object-level operation order is delete parent, then delete children. 


In my experience, the cascade should delete the children first. This  
solves 99% of the cascade delete issues.


Craig

On Sep 11, 2006, at 4:57 PM, Abe White (JIRA) wrote:


     [ http://issues.apache.org/jira/browse/OPENJPA-39?page=all ]

Abe White resolved OPENJPA-39.
------------------------------

    Resolution: Won't Fix


Turns out the problem is more complicated than just having a  
foreign key.  The issue is:
- OpenJPA's UpdateManager plugin does not reorder SQL.  It issues  
SQL in the same order as the corresponding object-level  
operations.  The SQL-reordering UpdateManager is unique to Kodo.
- When you delete a parent object and the operation cascades to  
children, the object-level operation order is delete parent, then  
delete children.
- Issuing the SQL in that order will violate the FK constraint.   
OpenJPA is smart enough to detect this and null the children  
record's FK columns before deleting the parent.
- However, the columns in this case are non-nullable, so this  
strategy doesn't work.



So, given that there are no plans to donate Kodo's SQL-reordering  
UpdateManager, the options are:

1. Use nullable foreign key columns, or
2. Delete the child objects before the parent at the object level.


Also if you choose option #1, make sure to get the latest OpenJPA  
from SVN, as there was a critical problem with nulling the FK  
columns correctly that is now fixed (9/11/2006).



Cascade delete does not work with foreign key constraints
---------------------------------------------------------

                Key: OPENJPA-39
                URL: http://issues.apache.org/jira/browse/OPENJPA-39
            Project: OpenJPA
         Issue Type: Bug
         Components: jpa
        Environment: Microsoft SQL Server 2000
Windows XP
Java SE 1.5
OpenJPA - source downloaded Aug 28, 2006)
Show ยป

Microsoft SQL Server 2000 Windows XP Java SE 1.5 OpenJPA - source  
downloaded today (Aug 14, 2006)

           Reporter: Megan
           Priority: Minor
        Attachments: testcase.zip



Removing a parent object in OneToMany with cascade=CascadeType.ALL  
raises foreign key constraints exception

JpaParent.java
  @OneToMany(mappedBy="parent", cascade=CascadeType.ALL)
  private Set<JpaChild> children = new HashSet<JpaChild>();
JpaChild.java
  @ManyToOne(optional=false)
  @JoinColumn(name="ParentId", nullable=false)
  private JpaParent parent = null;

// This raises the following exception.  If foreign key is  
removed, it works OK (Maybe I have to let OpenJPA know about  
foreign key constraints)

JpaParent parent = em.find(JpaParent.class, 1);
em.remove(parent);

<2|true|0.9.0-incubating-SNAPSHOT>  
org.apache.openjpa.persistence.RollbackException: The transaction  
has been rolled back.  See the nested exceptions for details on  
the errors that occurred.
	at org.apache.openjpa.persistence.EntityManagerImpl.commit 
(EntityManagerImpl.java:371)

        at openjpa.test.BaseTestCase.destroyTestCase(BaseTestCase.java:82)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke 
(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke 
(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:585)

	at org.junit.internal.runners.BeforeAndAfterRunner.invokeMethod 
(BeforeAndAfterRunner.java:74)
	at org.junit.internal.runners.BeforeAndAfterRunner.runAfters 
(BeforeAndAfterRunner.java:65)
	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected 
(BeforeAndAfterRunner.java:37)
	at org.junit.internal.runners.TestMethodRunner.runMethod 
(TestMethodRunner.java:75)
	at org.junit.internal.runners.TestMethodRunner.run 
(TestMethodRunner.java:45)
	at  
org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod 
(TestClassMethodsRunner.java:71)
	at org.junit.internal.runners.TestClassMethodsRunner.run 
(TestClassMethodsRunner.java:35)
	at org.junit.internal.runners.TestClassRunner$1.runUnprotected 
(TestClassRunner.java:42)
	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected 
(BeforeAndAfterRunner.java:34)
	at org.junit.internal.runners.TestClassRunner.run 
(TestClassRunner.java:52)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run 
(JUnit4TestReference.java:38)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run 
(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests 
(RemoteTestRunner.java:460)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests 
(RemoteTestRunner.java:673)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run 
(RemoteTestRunner.java:386)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main 
(RemoteTestRunner.java:196)
Caused by: <0|true|0.9.0-incubating-SNAPSHOT>  
org.apache.openjpa.persistence.PersistenceException: The  
transaction has been rolled back.  See the nested exceptions for  
details on the errors that occurred.
	at org.apache.openjpa.kernel.BrokerImpl.newFlushException 
(BrokerImpl.java:2028)

        at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:1876)

	at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java: 
1772)
	at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion 
(BrokerImpl.java:1706)
	at org.apache.openjpa.kernel.LocalManagedRuntime.commit 
(LocalManagedRuntime.java:76)

        at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1271)

	at org.apache.openjpa.kernel.DelegatingBroker.commit 
(DelegatingBroker.java:831)
	at org.apache.openjpa.persistence.EntityManagerImpl.commit 
(EntityManagerImpl.java:360)

        ... 21 more

Caused by: <0|false|0.9.0-incubating-SNAPSHOT>  
org.apache.openjpa.persistence.PersistenceException: [BEA] 
[SQLServer JDBC Driver][SQLServer]DELETE statement conflicted with  
COLUMN REFERENCE constraint 'JpaChild_Parent_FK'. The conflict  
occurred in database 'pocdb', table 'JpaChild', column 'ParentId'.  
{prepstmnt 32048085 DELETE FROM JpaParent WHERE Id = ? AND OptLock  
= ? [params=(int) 1, (int) 1]} [code=547, state=23000]

FailedObject: openjpa.model.JpaParent-1

	at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException 
(DBDictionary.java:3701)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore 
(SQLExceptions.java:94)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore 
(SQLExceptions.java:64)
	at  
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInte 
rnal(PreparedStatementManagerImpl.java:103)
	at  
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush 
(PreparedStatementManagerImpl.java:68)
	at  
org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flushPrima 
ryRow(OperationOrderUpdateManager.java:158)
	at  
org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flush 
(OperationOrderUpdateManager.java:86)
	at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush 
(AbstractUpdateManager.java:88)
	at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush 
(AbstractUpdateManager.java:68)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush 
(JDBCStoreManager.java:512)
	at org.apache.openjpa.kernel.DelegatingStoreManager.flush 
(DelegatingStoreManager.java:127)

        ... 28 more

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: [BEA] 
[SQLServer JDBC Driver][SQLServer]DELETE statement conflicted with  
COLUMN REFERENCE constraint 'JpaChild_Parent_FK'. The conflict  
occurred in database 'pocdb', table 'JpaChild', column 'ParentId'.  
{prepstmnt 32048085 DELETE FROM JpaParent WHERE Id = ? AND OptLock  
= ? [params=(int) 1, (int) 1]} [code=547, state=23000]
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap 
(LoggingConnectionDecorator.java:186)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access 
$700(LoggingConnectionDecorator.java:53)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator 
$LoggingConnection$LoggingPreparedStatement.executeUpdate 
(LoggingConnectionDecorator.java:839)
	at  
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate 
(DelegatingPreparedStatement.java:266)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager 
$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1359)
	at  
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInte 
rnal(PreparedStatementManagerImpl.java:92)

        ... 35 more


--
This message is automatically generated by JIRA.
-

If you think it was sent incorrectly contact one of the  
administrators: http://issues.apache.org/jira/secure/ 
Administrators.jspa

-

For more information on JIRA, see: http://www.atlassian.com/ 
software/jira





Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!




Attachment:
smime.p7s

Description: S/MIME cryptographic signature






















					Reply via email to