0 Replies Latest reply on May 25, 2006 2:37 AM by Eitan Gur

    Cascade delete with jboss-3.2.5

    Eitan Gur Newbie

      Hi

      I'm using JBoss-3.2.5 with MySQL 4.1 with CMP.
      I have a problem with cascade deleting entity beans, in a table containing relation to itself.

      Say I have 2 entity-beans: UserEntity and ItemEntity.
      There is a one-to-many relation User - Item, and a one-to-many relation Item - Item (to maintain a tree-structure between different items).

      Sometimes (and that's the big problem - sometimes - the problematic scenario is not known yet), I get a foreign key constraint violation when deleting a user.
      Say I have user John, owning 6 items that are related (a parent, 2 children and 3 grandchildren). When I try to delete John, I get a foreign-key-constraint violation, and when I query the MySQL to see which items does John has - I see that the 2 grandchildren were not removed.
      When I inspect the queries with the log file of MySQL, I see that the application server did something like this:
      SELECT itemID FROM ItemEntity WHERE (owner = 123456)
      (say the result was ids 1 --> 6)
      UPDATE ItemEntity SET owner = null WHERE itemId = 1
      UPDATE ItemEntity SET owner = null WHERE itemId = 2
      UPDATE ItemEntity SET parent = null, owner = null WHERE itemId = 3
      UPDATE ItemEntity SET parent = null, owner = null WHERE itemId = 4
      UPDATE ItemEntity SET owner = null WHERE itemId = 5
      UPDATE ItemEntity SET owner = null WHERE itemId = 6
      DELETE FROM UserEntity where id = 123456
      commit

      See that the last 2 updates did not set the parent to be null, which causes the foreign key constraint violation.

      Is this a known bug in JBoss-3.2.5?

      Thanks