PM64885: ORPHAN REMOVAL CAUSES DB CONSTRAINT VIOLATION.

8.5.0.1: WebSphere Application Server V8.5 Fix Pack 1
8.0.0.5: WebSphere Application Server V8.0 Fix Pack 5
8.5.0.2: WebSphere Application Server V8.5 Fix Pack 2
8.0.0.6: WebSphere Application Server V8.0 Fix Pack 6
8.0.0.7: WebSphere Application Server V8.0 Fix Pack 7
8.0.0.8: WebSphere Application Server V8.0 Fix Pack 8
8.0.0.9: WebSphere Application Server V8.0 Fix Pack 9
8.0.0.10: WebSphere Application Server V8.0 Fix Pack 10
8.0.0.11: WebSphere Application Server V8.0 Fix Pack 11
8.0.0.12: WebSphere Application Server V8.0 Fix Pack 12
8.0.0.13: WebSphere Application Server V8.0 Fix Pack 13
8.0.0.14: WebSphere Application Server V8.0 Fix Pack 14
8.0.0.15: WebSphere Application Server V8.0 Fix Pack 15

APAR status

• Closed as program error.

Error description

• The problem is reproducible in WebSphere Application Server
  operations cascaded from parents to children and grandchildren.
  
  When you do the following a DataBase constraint violation
  occurs:
  
  1) Start a transaction
  2) Load a Parent instance from the database
  3) Navigate to a Child and modify it
  4) Cut the relationship between Child and GrandChild
  5) Cut the relationship between Parent and Child
  6) Commit the transaction
  
  This results in a stacktrace:
  
  Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
  ORA-02292: <detailed error message here)
  WHERE ID =? [params=?]} [code=2292, state=23000]
  at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap
  (LoggingConnectionDecorator.java:273)
  

Local fix

• No work around indicated.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:  All users of IBM WebSphere Application      *
  *                  Server V8.0.0 and V8.5.0 who make           *
  *                  use of the orphanRemoval=true               *
  *                  property on their JPA relationships where   *
  *                  the Entity contains database foreign key    *
  *                  constraints.                                *
  ****************************************************************
  * PROBLEM DESCRIPTION: Constraint violation exception when     *
  *                      removing relationship using             *
  *                      (orphanRemoval = true)                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The problem occurs with the following relationships between
  classes:
  Parent ->* Child ->* GrandChild.
  Class Parent has a collection of Childs.
  Class Child has a collection of GrandChilds.
  In the database a foreign key constraint is define on the
  necessary classes.
  Each one-to-many relationship is annotated like this:
  @OneToMany(cascade = CascadeType.ALL, orphanRemoval=true,...)
  That is we use orphan removal and remove operations cascade
  from parents to children and grandchildren.
  When we do the following a DB constraint violation occurs:
  1) Start a transaction
  2) Load a Parent instance from the database
  3) Navigate to a Child and modify it
  4) Cut the relationship between Child and GrandChild
  5) Cut the relationship between Parent and Child
  6) Commit the transaction
  In this scenario the following exception will occur:
  Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
  DELETE on table 'CHILD' caused a violation of
  foreign key constraint 'SQL120320110237861' for key (11). The
  statement has been rolled back. {prepstmnt 1074806800 DELETE
  FROM CHILD WHERE ID = ? [params=(int) 11]}
  [code=20000, state=23503]
  

Problem conclusion

• With this fix, code has been added to OpenJPA to properly
  remove an orphan object when database foreign key constraints
  exists.
  
  The fix for this APAR is currently targeted for
  inclusion in Service Levels (Fix Packs) 8.0.0.5 and
  8.5.0.1 of WebSphere Application Server versions 8.0.0 and
  8.5.0.
  
  Please refer to the recommended updates page for delivery
  information:
  http://www.ibm.com/support/docview.wss?rs=180&uid=swg27004980
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  PM64033

• APAR is sysrouted TO one or more of the following:

  PM70235 PM70237

Fix information

• Fixed component name

  WEBSPHERE APP S

• Fixed component ID

  5724J0800

Applicable component levels

• R800 PSY

     UP

• R850 PSY

     UP