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
