Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2.2, 10.3.0
-
FreeBSD ports
Description
See SQL test case below for detail. See this thread for problem discovery:
https://lists.launchpad.net/maria-developers/msg11102.html
For a "triangular" table FK structure (see attached png visual) deleting parent record, should delete both children, but the ON DELETE CASCADE does not delete record in 2nd child table when 2nd child has ON DELETE SET NULL constraint to 1st child
Referential integrity is broken after parent is deleted. LEFT JOINs do not show broken FK.
MySQL 5.7.21 and MariaDB 10.1.30 do not exhibit this broken behaviour.
-- Server version: 10.2.12-MariaDB FreeBSD Ports
|
|
-- setup DB structure
|
|
SET FOREIGN_KEY_CHECKS=0; |
|
CREATE TABLE member ( |
id int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (id) |
) ENGINE=InnoDB;
|
|
INSERT INTO member VALUES (1); |
|
CREATE TABLE address ( |
id int(11) NOT NULL AUTO_INCREMENT, |
member_id int(11) NOT NULL, |
PRIMARY KEY (id), |
KEY address_FI_1 (member_id), |
CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE |
) ENGINE=InnoDB;
|
|
INSERT INTO address VALUES (2,1); |
|
CREATE TABLE payment_method ( |
id int(11) NOT NULL AUTO_INCREMENT, |
member_id int(11) NOT NULL, |
cardholder_address_id int(11) DEFAULT NULL, |
PRIMARY KEY (id), |
KEY payment_method_FI_1 (member_id), |
KEY payment_method_FI_2 (cardholder_address_id), |
CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE |
) ENGINE=InnoDB;
|
|
INSERT INTO payment_method VALUES (3,1,2); |
|
SET FOREIGN_KEY_CHECKS=1; |
|
-- show inserted data
|
|
SELECT * FROM member; |
+----+ |
| id |
|
+----+ |
| 1 |
|
+----+ |
|
SELECT * FROM address; |
+----+-----------+ |
| id | member_id |
|
+----+-----------+ |
| 2 | 1 |
|
+----+-----------+ |
|
SELECT * FROM payment_method; |
+----+-----------+-----------------------+ |
| id | member_id | cardholder_address_id |
|
+----+-----------+-----------------------+ |
| 3 | 1 | 2 |
|
+----+-----------+-----------------------+ |
|
-- finished setup now delete the central `member` record
|
DELETE FROM member WHERE id = 1; |
|
-- check the resulting state
|
|
SELECT * FROM member; |
-- empty set, correct
|
|
SELECT * FROM address; |
-- empty set, correct
|
|
SELECT * FROM payment_method; |
+----+-----------+-----------------------+ |
| id | member_id | cardholder_address_id |
|
+----+-----------+-----------------------+ |
| 3 | 1 | NULL | |
+----+-----------+-----------------------+ |
-- should be an empty set
|
-- referential integrity is broken: payment_method.member_id references a non-existent member.id
|
|
-- inconsistency continues during subsequent left join queries
|
|
SELECT
|
payment_method.id, member_id
|
FROM
|
payment_method
|
LEFT JOIN |
member ON member.id=payment_method.member_id |
WHERE
|
payment_method.member_id IS NOT NULL AND member.id IS NULL; |
|
-- empty set, when it should not be
|
-- note that if you dump and insert the broken DB, then above left join DOES show the broken FK, which is how I discovered it |
Attachments
Issue Links
- duplicates
-
MDEV-14222 Unnecessary 'cascade' memory allocation for every updated row when there is no FOREIGN KEY
- Closed
- is blocked by
-
MDEV-15219 FOREIGN KEY CASCADE or SET NULL operations will not resume after lock wait
- Closed
- relates to
-
MDEV-13902 Orphan rows despite ON DELETE CASCADE constraint
- Closed
-
MDEV-25087 Stack overflow upon exceeding FOREIGN KEY recursion depth
- Open
-
MDEV-9663 InnoDB assertion failure: *cursor->index->name == TEMP_INDEX_PREFIX, or !cursor->index->is_committed()
- Closed
-
MDEV-34403 Self-referencing table produces "Got error 193" from InnoDB on cascading DELETE
- Open