Thank you for the very detailed description.
This issue seems odd. There are known issues with constraint violation and
deletion, but your model seems like it would be ok. Are there other
relationships than just these?
The general cascading delete issue that TopLink has, can normally be fixed
by removing the cascade delete and instead marking the mapping as
private-owned in the TopLink ClassDescriptor OneToManyMapping using a
DescriptorCustomizer. Otherwise you can remove or defer the database
constraint. Sometimes removing the objects one by one and using flush can
also work.
I forget the bug number for the known issue, but feel free to log your
issues in the bug database, or if you find the other issue you can add your
scenario and vote for the issue.
Axl Mattheus-2 wrote:
>
> To Whom it may Concern:
>
> PROBLEM:
> * Cascading deletes are broken under specific conditions when using
> TopLink Essentials as a persistence provider.
> * I get this behavior with MySQL, Oracle and JavaDB when using TopLink
> Essentials. I do not get this behavior on any database when using
> Hibernate
> or JPOX.
>
>
> CLASS MODEL LOOKS AS FOLLOWS:
> * A contact has many telephone numbers, email addresses and addresses.
> Addresses has many lines.
> * All associations between classes are bi-directional.
>
> +---------+ 1 * +---+-----+ 1 * +-------------+
> | Contact +----------+ Address +---------+ AddressLine |
> +-+--+--+-+ +---------+ +-------------+
> 1| 1|
> | | * +--+------------+
> | +-----------+ EmailAddress |
> | +---------------+
> |
> | * +-----+-----------+
> +---------------+ TelephoneNumber |
> +-----------------+
>
>
> ENTITY DECLARATIONS ARE BELOW:
> @Entity(name = "Contact")
> @Table(name = "contacts")
> public class Contact {
> ... elided ...
> @OneToMany(mappedBy = "contact", cascade = CascadeType.ALL,
> fetch = FetchType.EAGER)
> private final Set<EmailAddress> emailAddresses = new
> ConcurrentSkipListSet<EmailAddress>();
>
> @OneToMany(mappedBy = "contact", cascade = CascadeType.ALL,
> fetch = FetchType.EAGER)
> private final Set<TelephoneNumber> telephoneNumbers = new
> ConcurrentSkipListSet<TelephoneNumber>();
>
> @OneToMany(mappedBy = "contact", cascade = CascadeType.ALL,
> fetch = FetchType.EAGER)
> private final Set<Address> addresses = new
> ConcurrentSkipListSet<Address>();
> ... elided ...
> }
>
>
> @Entity(name = "EmailAddress")
> @Table(name = "email_addresses")
> public class EmailAddress {
> ... elided ...
> @ManyToOne(cascade = {
> CascadeType.PERSIST, CascadeType.MERGE,
> CascadeType.REFRESH
> }, optional = false)
> @JoinColumn(name = "contact_fk", referencedColumnName =
> "contact_id")
> private Contact contact;
> ... elided ...
> }
>
>
> @Entity(name = "TelephoneNumber")
> @Table(name = "telephone_numbers")
> public class TelephoneNumber {
> ... elided ...
> @ManyToOne(cascade = {
> CascadeType.PERSIST, CascadeType.MERGE,
> CascadeType.REFRESH
> }, optional = false)
> @JoinColumn(name = "contact_fk", referencedColumnName =
> "contact_id")
> private Contact contact;
> ... elided ...
> }
>
>
> @Entity(name = "Address")
> @Table(name = "addresses")
> public class Address {
> ... elided ...
> @OneToMany(mappedBy = "address", cascade = CascadeType.ALL, fetch
> =
> FetchType.EAGER)
> private final Set<AddressLine> lines = new
> ConcurrentSkipListSet<AddressLine>();
>
> @ManyToOne(cascade = {
> CascadeType.PERSIST, CascadeType.MERGE,
> CascadeType.REFRESH
> }, optional = true)
> @JoinColumn(name = "contact_fk", referencedColumnName =
> "contact_id")
> private Contact contact;
> ... elided ...
> }
>
>
> @Entity(name = "AddressLine")
> @Table(name = "address_lines")
> public class AddressLine {
> ... elided ...
> @ManyToOne(cascade = {
> CascadeType.PERSIST, CascadeType.MERGE,
> CascadeType.REFRESH
> }, optional = false)
> @JoinColumn(name = "address_fk", referencedColumnName =
> "address_id")
> private Address address;
> ... elided ...
> }
>
> THE CODE DELEING THE ENTITY IN THE DAO IS AS FOLLOWS:
> ... elided ...
> public void removeContact(final Contact contact) {
> connect();
> EntityTransaction tx = entityManager.getTransaction();
> try {
> tx.begin();
> final Contact c = (Contact) entityManager.merge(contact);
> entityManager.remove(c);
> tx.commit();
> } catch (Exception e) {
>
> Logger.getLogger(RolodexDAO.class.getName()).log(Level.WARNING,
> e.getMessage(), e);
> tx.rollback();
> } finally {
> disconnect();
> }
> }
> ... elided ...
>
>
> EXAMPLE OF A CONTACT THAT WILL BE DELETED:
> Contact_at_16dfa45=[
> Persistent Id=9,
> ... elided ...
> Email Address0=EmailAddress_at_1690ab=[
> Persistent Id=24,
> ... elided ...
> Email Address1=EmailAddress_at_173ec72=[
> Persistent Id=23,
> ... elided ...
> Telephone Number0=TelephoneNumber_at_1a85d38=[
> Persistent Id=28,
> ... elided ...
> Telephone Number1=TelephoneNumber_at_8046f4=[
> Persistent Id=31,
> ... elided ...
> Telephone Number2=TelephoneNumber_at_1b273cc=[
> Persistent Id=30,
> ... elided ...
> Telephone Number3=TelephoneNumber_at_d05c13=[
> Persistent Id=29,
> ... elided ...
> Address0=Address_at_c3014=[
> Persistent Id=18,
> ... elided ...
> Line0=AddressLine_at_6754d6=[
> Persistent Id=101,
> ... elided ...
> Line1=AddressLine_at_289d2e=[
> Persistent Id=100,
> ... elided ...
> Line2=AddressLine_at_1f2cea2=[
> Persistent Id=102,
> ... elided ...
> Line3=AddressLine_at_3a9bba=[
> Persistent Id=103,
> ... elided ...
> Line4=AddressLine_at_1dc0e7a=[
> Persistent Id=99,
> ... elided ...
>
> Contact with id 9 with (deleted by 14 below)
> Email Address with id 24 (deleted by 07 below)
> Email Address with id 23 (deleted by 06 below)
> Telephone Number with id 28 (deleted by 03 below)
> Telephone Number with id 31 (deleted by 02 below)
> Telephone Number with id 30 (deleted by 01 below)
> Telephone Number with id 29 (deleted by 04 below)
> Address with id 18 with (deleted by 13 below)
> Address Line with id 091 (deleted by 09 below)
> Address Line with id 090 (deleted by 12 below)
> Address Line with id 092 (deleted by 10 below)
> Address Line with id 093 (deleted by 11 below)
> Address Line with id 99 (deleted by 08 below)
>
> WILL BE DELETED BY THE FOLLOWING SQL GENERATED BY THE TopLink
> Essentials
> LIBRARY:
> ... elided ...
> 01 - [TopLink Fine]: 2008.05.24
> 10:48:18.093--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [30, 1]
> 02 - [TopLink Fine]: 2008.05.24
> 10:48:18.093--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [31, 1]
> 03 - [TopLink Fine]: 2008.05.24
> 10:48:18.109--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [28, 1]
> 04 - [TopLink Fine]: 2008.05.24
> 10:48:18.109--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [29, 1]
> 05 - [TopLink Fine]: 2008.05.24
> 10:48:18.109--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM legal_identifiers WHERE ((legal_identifier_id = ?) AND
> (legal_identifier_version_lock = ?))
> bind => [9, 1]
> 06 - [TopLink Fine]: 2008.05.24
> 10:48:18.140--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM email_addresses WHERE ((email_address_id = ?) AND
> (email_address_version_lock = ?))
> bind => [23, 1]
> 07 - [TopLink Fine]: 2008.05.24
> 10:48:18.140--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM email_addresses WHERE ((email_address_id = ?) AND
> (email_address_version_lock = ?))
> bind => [24, 1]
> 08 - [TopLink Fine]: 2008.05.24
> 10:48:18.140--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [99, 1]
> 09 - [TopLink Fine]: 2008.05.24
> 10:48:18.140--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [101, 1]
> 10 - [TopLink Fine]: 2008.05.24
> 10:48:18.140--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [102, 1]
> 11 - [TopLink Fine]: 2008.05.24
> 10:48:18.156--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [103, 1]
> 12 - [TopLink Fine]: 2008.05.24
> 10:48:18.156--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [100, 1]
> 13 - [TopLink Fine]: 2008.05.24
> 10:48:18.156--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM addresses WHERE ((address_id = ?) AND (address_version_lock = ?))
> bind => [18, 1]
> 14 - [TopLink Fine]: 2008.05.24
> 10:48:18.171--ClientSession(14298351)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM contacts WHERE ((contact_id = ?) AND (contact_version_lock = ?))
> bind => [9, 1]
> ... elided ...
>
> AS CAN BE SEEN FROM THE SQL ABOVE, ALL THE ADDRESS LINES ARE DELETED
> BEFOR THE ADDRESS IS DELETED AN THE CASCADING DELETE SUCCEEDS.
>
>
> EXAMPLE OF A CONTACT THAT WILL NOT BE DELETED:
> Contact_at_1c5ddc9=[
> Persistent Id=2,
> ... elided ...
> Email Address0=EmailAddress_at_47a0d4=[
> Persistent Id=4,
> ... elided ...
> Email Address1=EmailAddress_at_8ff4cf=[
> Persistent Id=3,
> ... elided ...
> Telephone Number0=TelephoneNumber_at_1114460=[
> Persistent Id=6,
> ... elided ...
> Telephone Number1=TelephoneNumber_at_9ffe3f=[
> Persistent Id=4,
> ... elided ...
> Telephone Number2=TelephoneNumber_at_1ed1b0b=[
> Persistent Id=5,
> ... elided ...
> Telephone Number3=TelephoneNumber_at_18a8ce2=[
> Persistent Id=7,
> ... elided ...
> Address0=Address_at_1690ab=[
> Persistent Id=5,
> ... elided ...
> Line0=AddressLine_at_1b273cc=[
> Persistent Id=29,
> ... elided ...
> Line1=AddressLine_at_173ec72=[
> Persistent Id=20,
> ... elided ...
> Line2=AddressLine_at_d05c13=[
> Persistent Id=21,
> ... elided ...
> Line3=AddressLine_at_14df764=[
> Persistent Id=27,
> ... elided ...
> Line4=AddressLine_at_1bbd7b2=[
> Persistent Id=22,
> ... elided ...
> Line5=AddressLine_at_14d1d41=[
> Persistent Id=19,
> ... elided ...
> Address1=Address_at_1acfa31=[
> Persistent Id=4,
> ... elided ...
> Line0=AddressLine_at_15dd910=[
> Persistent Id=30,
> ... elided ...
> Line1=AddressLine_at_8de462=[
> Persistent Id=28,
> ... elided ...
> Line2=AddressLine_at_55bb93=[
> Persistent Id=23,
> ... elided ...
> Line3=AddressLine_at_18ce14a=[
> Persistent Id=24,
> ... elided ...
> Line4=AddressLine_at_1ae90c=[
> Persistent Id=25,
> ... elided ...
> Line5=AddressLine_at_ba4211=[
> Persistent Id=26,
> ... elided ...
>
> Contact with id 2 (NOT deleted)
> Email Address with id 4 (deleted by 06 below)
> Email Address with id 3 (deleted by 07 below)
> Telephone Number with id 6 (deleted by 04 below)
> Telephone Number with id 4 (deleted by 03 below)
> Telephone Number with id 5 (deleted by 02 below)
> Telephone Number with id 7 (deleted by 01 below)
> Address with id 5 (NOT deleted)
> Address Line with id 29 (deleted by 19 below)
> Address Line with id 20 (deleted by 15 below)
> Address Line with id 21 (deleted by 11 below)
> Address Line with id 27 (deleted by 09 below)
> Address Line with id 22 (deleted by 12 below)
> Address Line with id 19 (deleted by 13 below)
> Address with id 4 (deleted by 16 below)
> Address Line with id 30 (deleted by 08 below)
> Address Line with id 28 (NOT deleted)
> Address Line with id 23 (NOT deleted)
> Address Line with id 24 (deleted by 10 below)
> Address Line with id 25 (NOT deleted)
> Address Line with id 26 (NOT deleted)
>
>
> WILL !!!NOT!!! BE DELETED BY THE FOLLOWING SQL GENERATED BY THE
> TopLink
> Essentials LIBRARY:
> 01 - [TopLink Fine]: 2008.05.24
> 03:19:42.203--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [7, 1]
> 02 - [TopLink Fine]: 2008.05.24
> 03:19:42.203--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [5, 1]
> 03 - [TopLink Fine]: 2008.05.24
> 03:19:42.203--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [4, 1]
> 04 - [TopLink Fine]: 2008.05.24
> 03:19:42.203--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_telephone_numbers WHERE ((telephone_number_id = ?) AND
> (telephone_number_version_lock = ?))
> bind => [6, 1]
> 05 - [TopLink Fine]: 2008.05.24
> 03:19:42.218--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_legal_identifiers WHERE ((legal_identifier_id = ?) AND
> (legal_identifier_version_lock = ?))
> bind => [2, 1]
> 06 - [TopLink Fine]: 2008.05.24
> 11:59:16.937--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_email_addresses WHERE ((email_address_id = ?) AND
> (email_address_version_lock = ?))
> bind => [4, 1]
> 07 - [TopLink Fine]: 2008.05.24
> 03:19:42.234--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_email_addresses WHERE ((email_address_id = ?) AND
> (email_address_version_lock = ?))
> bind => [3, 1]
> 08 - [TopLink Fine]: 2008.05.24
> 03:19:42.234--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [30, 1]
> 09 - [TopLink Fine]: 2008.05.24
> 03:19:42.234--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [27, 1]
> 10 - [TopLink Fine]: 2008.05.24
> 03:19:42.234--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [24, 1]
> 11 - [TopLink Fine]: 2008.05.24
> 03:19:42.250--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [21, 1]
> 12 - [TopLink Fine]: 2008.05.24
> 03:19:42.250--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [22, 1]
> 13 - [TopLink Fine]: 2008.05.24
> 03:19:42.250--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [19, 1]
> 14 - [TopLink Fine]: 2008.05.24
> 03:19:42.250--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [29, 1]
> 15 - [TopLink Fine]: 2008.05.24
> 03:19:42.250--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_address_lines WHERE ((address_line_id = ?) AND
> (address_line_version_lock = ?))
> bind => [20, 1]
> 16 - [TopLink Fine]: 2008.05.24
> 03:19:42.265--ClientSession(17777129)--Connection(20435088)--Thread(Thread[main,5,main])--DELETE
> FROM tbl_addresses WHERE ((address_id = ?) AND (address_version_lock = ?))
> bind => [4, 1]
>
> THIS CAUSES A FOREIGN KEY CONSTRAINT TO FAIL ON THE DATABASE!!!
> [TopLink Warning]: 2008.05.24
> 11:59:17.000--UnitOfWork(20910958)--Thread(Thread[main,5,main])--Exception
> [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b09d-fcs
> (12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseException
> Internal Exception:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Cannot delete or update a parent row: a foreign key constraint fails
> (`rolodex/tbl_address_lines`, CONSTRAINT `FK_tbl_address_lines_address_fk`
> FOREIGN KEY (`address_fk`) REFERENCES `tbl_addresses` (`address_id`))
> Error Code: 1451
> Call: DELETE FROM tbl_addresses WHERE ((address_id = ?) AND
> (address_version_lock = ?))
> bind => [4, 1]
>
> * AS CAN BE SEEN FROM THE SQL ABOVE, ADDRESS WITH ID 4 CANNOT BE
> DELETED
> BECAUSE IT STILL HAS DANGLING ADDRESS LINES IN THE ADDRESS_LINES TABLE!
> * THIS BEHAVIOR IS CONSISTENT ACROSS ALL CONTACTS WITH MULTIPLE
> ADDRESSES, THERE ARE SOME ADDRESS_LINES NOT DELETED THAT CAUSES THE
> FOREIGN
> KEY CONSTRAINTS TO FAIL.
>
> Please investigate and fix if it is a bug.
>
> ax/
>
>
-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
--
View this message in context: http://www.nabble.com/TopLink-Essentials-broken-cascading-delete-tp17511114p17514654.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.