Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: open-jpa-dev@incubator.apache.org
Received-SPF: error (idunn.apache.osuosl.org: domain Sun.COM from
 192.18.42.249 cause and error)
Date: Mon, 11 Sep 2006 17:54:24 -0700
From: Craig L Russell <Craig.Russell@Sun.COM>
Subject: Re: [jira] Resolved: (OPENJPA-39) Cascade delete does not work with
 foreign key constraints
In-reply-to: <14156901.1158019042738.JavaMail.jira@brutus>
Sender: Craig.Russell@Sun.COM
To: open-jpa-dev@incubator.apache.org
Message-id: <3DB84D99-8C19-4168-A8D1-5FC1ED565073@SUN.com>
MIME-version: 1.0
Content-type: multipart/signed; protocol="application/pkcs7-signature";
 boundary=Apple-Mail-127--673359750; micalg=sha1
References: <14156901.1158019042738.JavaMail.jira@brutus>

--Apple-Mail-127--673359750
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=ISO-8859-1;
	delsp=yes;
	format=flowed

Hi Abe,

> - When you delete a parent object and the operation cascades to =20
> children, the object-level operation order is delete parent, then =20
> delete children.

In my experience, the cascade should delete the children first. This =20
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=3Dall ]
>
> Abe White resolved OPENJPA-39.
> ------------------------------
>
>     Resolution: Won't Fix
>
> Turns out the problem is more complicated than just having a =20
> foreign key.  The issue is:
> - OpenJPA's UpdateManager plugin does not reorder SQL.  It issues =20
> SQL in the same order as the corresponding object-level =20
> operations.  The SQL-reordering UpdateManager is unique to Kodo.
> - When you delete a parent object and the operation cascades to =20
> children, the object-level operation order is delete parent, then =20
> delete children.
> - Issuing the SQL in that order will violate the FK constraint.  =20
> OpenJPA is smart enough to detect this and null the children =20
> record's FK columns before deleting the parent.
> - However, the columns in this case are non-nullable, so this =20
> strategy doesn't work.
>
> So, given that there are no plans to donate Kodo's SQL-reordering =20
> 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 =20
> from SVN, as there was a critical problem with nulling the FK =20
> 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 =BB
>> Microsoft SQL Server 2000 Windows XP Java SE 1.5 OpenJPA - source =20
>> downloaded today (Aug 14, 2006)
>>            Reporter: Megan
>>            Priority: Minor
>>         Attachments: testcase.zip
>>
>>
>> Removing a parent object in OneToMany with cascade=3DCascadeType.ALL =20=

>> raises foreign key constraints exception
>> JpaParent.java
>>   @OneToMany(mappedBy=3D"parent", cascade=3DCascadeType.ALL)
>>   private Set<JpaChild> children =3D new HashSet<JpaChild>();
>> JpaChild.java
>>   @ManyToOne(optional=3Dfalse)
>>   @JoinColumn(name=3D"ParentId", nullable=3Dfalse)
>>   private JpaParent parent =3D null;
>> // This raises the following exception.  If foreign key is =20
>> removed, it works OK (Maybe I have to let OpenJPA know about =20
>> foreign key constraints)
>> JpaParent parent =3D em.find(JpaParent.class, 1);
>> em.remove(parent);
>> <2|true|0.9.0-incubating-SNAPSHOT> =20
>> org.apache.openjpa.persistence.RollbackException: The transaction =20
>> has been rolled back.  See the nested exceptions for details on =20
>> the errors that occurred.
>> 	at org.apache.openjpa.persistence.EntityManagerImpl.commit=20
>> (EntityManagerImpl.java:371)
>> 	at =
openjpa.test.BaseTestCase.destroyTestCase(BaseTestCase.java:82)
>> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> 	at sun.reflect.NativeMethodAccessorImpl.invoke=20
>> (NativeMethodAccessorImpl.java:39)
>> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke=20
>> (DelegatingMethodAccessorImpl.java:25)
>> 	at java.lang.reflect.Method.invoke(Method.java:585)
>> 	at org.junit.internal.runners.BeforeAndAfterRunner.invokeMethod=20=

>> (BeforeAndAfterRunner.java:74)
>> 	at org.junit.internal.runners.BeforeAndAfterRunner.runAfters=20
>> (BeforeAndAfterRunner.java:65)
>> 	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected=20=

>> (BeforeAndAfterRunner.java:37)
>> 	at org.junit.internal.runners.TestMethodRunner.runMethod=20
>> (TestMethodRunner.java:75)
>> 	at org.junit.internal.runners.TestMethodRunner.run=20
>> (TestMethodRunner.java:45)
>> 	at =20
>> org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod=20
>> (TestClassMethodsRunner.java:71)
>> 	at org.junit.internal.runners.TestClassMethodsRunner.run=20
>> (TestClassMethodsRunner.java:35)
>> 	at org.junit.internal.runners.TestClassRunner$1.runUnprotected=20=

>> (TestClassRunner.java:42)
>> 	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected=20=

>> (BeforeAndAfterRunner.java:34)
>> 	at org.junit.internal.runners.TestClassRunner.run=20
>> (TestClassRunner.java:52)
>> 	at =
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run=20
>> (JUnit4TestReference.java:38)
>> 	at org.eclipse.jdt.internal.junit.runner.TestExecution.run=20
>> (TestExecution.java:38)
>> 	at =
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests=20
>> (RemoteTestRunner.java:460)
>> 	at =
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests=20
>> (RemoteTestRunner.java:673)
>> 	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run=20
>> (RemoteTestRunner.java:386)
>> 	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main=20=

>> (RemoteTestRunner.java:196)
>> Caused by: <0|true|0.9.0-incubating-SNAPSHOT> =20
>> org.apache.openjpa.persistence.PersistenceException: The =20
>> transaction has been rolled back.  See the nested exceptions for =20
>> details on the errors that occurred.
>> 	at org.apache.openjpa.kernel.BrokerImpl.newFlushException=20
>> (BrokerImpl.java:2028)
>> 	at =
org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:1876)
>> 	at =
org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:=20
>> 1772)
>> 	at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion=20
>> (BrokerImpl.java:1706)
>> 	at org.apache.openjpa.kernel.LocalManagedRuntime.commit=20
>> (LocalManagedRuntime.java:76)
>> 	at =
org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1271)
>> 	at org.apache.openjpa.kernel.DelegatingBroker.commit=20
>> (DelegatingBroker.java:831)
>> 	at org.apache.openjpa.persistence.EntityManagerImpl.commit=20
>> (EntityManagerImpl.java:360)
>> 	... 21 more
>> Caused by: <0|false|0.9.0-incubating-SNAPSHOT> =20
>> org.apache.openjpa.persistence.PersistenceException: [BEA]=20
>> [SQLServer JDBC Driver][SQLServer]DELETE statement conflicted with =20=

>> COLUMN REFERENCE constraint 'JpaChild_Parent_FK'. The conflict =20
>> occurred in database 'pocdb', table 'JpaChild', column 'ParentId'. =20=

>> {prepstmnt 32048085 DELETE FROM JpaParent WHERE Id =3D ? AND OptLock =20=

>> =3D ? [params=3D(int) 1, (int) 1]} [code=3D547, state=3D23000]
>> FailedObject: openjpa.model.JpaParent-1
>> 	at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException=20
>> (DBDictionary.java:3701)
>> 	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore=20
>> (SQLExceptions.java:94)
>> 	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore=20
>> (SQLExceptions.java:64)
>> 	at =20
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInte=20=

>> rnal(PreparedStatementManagerImpl.java:103)
>> 	at =20
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush=20
>> (PreparedStatementManagerImpl.java:68)
>> 	at =20
>> org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flushPrima=20=

>> ryRow(OperationOrderUpdateManager.java:158)
>> 	at =20
>> org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flush=20
>> (OperationOrderUpdateManager.java:86)
>> 	at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush=20
>> (AbstractUpdateManager.java:88)
>> 	at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush=20
>> (AbstractUpdateManager.java:68)
>> 	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush=20
>> (JDBCStoreManager.java:512)
>> 	at org.apache.openjpa.kernel.DelegatingStoreManager.flush=20
>> (DelegatingStoreManager.java:127)
>> 	... 28 more
>> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: [BEA]=20=

>> [SQLServer JDBC Driver][SQLServer]DELETE statement conflicted with =20=

>> COLUMN REFERENCE constraint 'JpaChild_Parent_FK'. The conflict =20
>> occurred in database 'pocdb', table 'JpaChild', column 'ParentId'. =20=

>> {prepstmnt 32048085 DELETE FROM JpaParent WHERE Id =3D ? AND OptLock =20=

>> =3D ? [params=3D(int) 1, (int) 1]} [code=3D547, state=3D23000]
>> 	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap=20=

>> (LoggingConnectionDecorator.java:186)
>> 	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access=20=

>> $700(LoggingConnectionDecorator.java:53)
>> 	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator=20
>> $LoggingConnection$LoggingPreparedStatement.executeUpdate=20
>> (LoggingConnectionDecorator.java:839)
>> 	at =20
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate=20=

>> (DelegatingPreparedStatement.java:266)
>> 	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager=20
>> $CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1359)
>> 	at =20
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInte=20=

>> rnal(PreparedStatementManagerImpl.java:92)
>> 	... 35 more
>
> --=20
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the =20
> administrators: http://issues.apache.org/jira/secure/=20
> Administrators.jspa
> -
> For more information on JIRA, see: http://www.atlassian.com/=20
> 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!


--Apple-Mail-127--673359750
Content-Transfer-Encoding: base64
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Disposition: attachment;
	filename=smime.p7s

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw
ggJyoAMCAQICEB7Y7fLHmKoeHTUdbtYrvpAwDQYJKoZIhvcNAQEEBQAwYjELMAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1MTIxNTIxNDc0NFoXDTA2MTIxNTIxNDc0
NFowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny
YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC
ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMXXgEAm88nu8cFbxXSWqTq+GaYpCx/0QSom
8kBbVxpJIURuO4ErLDupeXu6y9+5e7ZKLbeSQE5xbfYPcQR/IhqmYKy5YqRcuzPXREBj2zKEkZph
pNXWpHgMdx9W8dq0Cu2i9Ux/S6c2MuEHrP6gfGGll+b/mzLoO280QHTuE4pcpKntRnwZdGxQ/5l8
IL+eLP+jpJAbYW9C+KNKofZtS6V6R0uzlqTOsEdZvwxZQ4mmPgHoz1+Gjwme/PC5sKvF09MaJDiI
pj9SvZ4CTCgcDZV78J086YwlVbMC0VQotjhu1p42lr8CS33IXLz3OWNrDETCAepah/Dgw2ZZApQ9
9L0CAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC
MAAwDQYJKoZIhvcNAQEEBQADgYEAKdIkgAWCg2Bi7ocnstfJA4iymTRI2/L4oQx9zvllM9bNJ2cR
cecJIx3HuoHbhPvemh1GExEPgHU+dXSxDmD0BEmPnhSReKCURyslnbMphPZ5kR6USzQFrRa+v0ii
J+SBO9VQYTQWT+xEjmRLM76MfkBFw3IOC9CUkRoYZ88pOoUwggM/MIICqKADAgECAgENMA0GCSqG
SIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH
EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp
Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h
aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw
NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3Rl
IENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWls
IElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUE
cJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/Ef
kTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMB
AAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3Js
LnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYD
VR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GB
AEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+
hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC
3CEZNd4ksdMdRv9dX2VPMYIDEDCCAwwCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh
d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt
YWlsIElzc3VpbmcgQ0ECEB7Y7fLHmKoeHTUdbtYrvpAwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN
AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwOTEyMDA1NDI2WjAjBgkqhkiG9w0B
CQQxFgQUER7ld9tZkCMG53VvrhMYg3oRCXQwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC
WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe2O3yx5iqHh01HW7WK76QMIGHBgsqhkiG
9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ
dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe
2O3yx5iqHh01HW7WK76QMA0GCSqGSIb3DQEBAQUABIIBAJWv+jUHsF7EJouoL1WcRnuqDuiAOIxi
17+mTIFmVcCo6aWbynQAGELmlzskNzCDz55urMixtUH4DKC1kMtdsbgfS1hPUWYOzzPqinj4GCQi
AyShmxcM9pX7Mtb7uwrF7wP8DS5dXzDFGyMCz2b1vvX7JFYnE2Cs2SjKilmQY369ztALc1bI2P+9
paiLIRKzr8YJUKfbFuJszbc4g/zIC8VmePT6GZrSi0pLT4CDubeyOofMOsTO5eCjulE4UrfvOnXY
QiHEf2NexCWodbQejFetxWMT1T32lCR9pSIekq1shagcUxLBDrbkVB0hS/tRefi+J1BuSPHByuY+
56omUjUAAAAAAAA=

--Apple-Mail-127--673359750--