openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: [jira] Resolved: (OPENJPA-39) Cascade delete does not work with foreign key constraints
Date Tue, 12 Sep 2006 00:54:24 GMT
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:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message