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 fix this bug.
ax/