Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Date: Fri, 28 Sep 2012 07:07:08 +1100 (NCT)
From: "Givanildo Dantas Alves (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Message-ID: <1380029050.135461.1348776428327.JavaMail.jiratomcat@arcas>
Subject: [jira] [Created] (OPENJPA-2270) Wrong SQL reordering on DELETE
 causing foreign key constraint violations if relationships are nullified
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Givanildo Dantas Alves created OPENJPA-2270:
-----------------------------------------------

             Summary: Wrong SQL reordering on DELETE causing foreign key constraint violations if relationships are nullified
                 Key: OPENJPA-2270
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2270
             Project: OpenJPA
          Issue Type: Bug
          Components: kernel
    Affects Versions: 2.2.0, 2.1.1
            Reporter: Givanildo Dantas Alves


A Parent has a bi-directional one-to-many relationship to Child. A foreign key is added to ensure consistency between PARENT and CHILD tables

@Entity
public class Parent {
    @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
    private Set<Child> childList = new HashSet<Child>(); 
...
}

@Entity
public class Child {
    @ManyToOne
    @JoinColumn(name="PARENT_ID", nullable=false)
    @ForeignKey(deleteAction=ForeignKeyAction.RESTRICT)
    private Parent parent;
...
}


For a given Parent instance previously persisted in the database, I nullify the relationships to its associated Child instances (i.e remove all Child's from Parent collection and have those Child's no longer point to any Parent) and call EntityManager API to remove the Parent and the Child's

for (Child c : parent.getChildren()) {
       parent.removeChild(c);
       entityManager.remove(c);
}
entityManager.remove(parent);

However, OpenJPA attempts to execute the SQL DELETE statement for PARENT table prior to deleting the CHILD records, which causes a foreign key constraint violation:

DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=ADMINISTRATOR.CHILD.SQL120927163610030, DRIVER=3.61.75 {prepstmnt 1505188279 DELETE FROM ADMINISTRATOR.Parent WHERE PARENT_ID = ? [params=(long) 301]} [code=-532, state=23504]SQLCA OUTPUT[Errp=SQLRI079, Errd=-2145779603, 0, 0, 0, -95, 0]
DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=ADMINISTRATOR.CHILD.SQL120927163610030, DRIVER=3.61.75
FailedObject: com.ibm.dis.api.Parent-301

The error happens regardless of the value configured in openjpa.jdbc.UpdateManager property

I can only make deletion work fine if the relationships are not nullified


--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira