[Maria-developers] Triangular FKs - Cascade delete leaves DB in state with broken referential integrity
Initially posted on "discuss" list... I have come across a very strange symptom with triangular FKs (see below) where a cascade delete of the central (member) record leaves the DB in a state with broken referential integrity. The FK structure is a little bit demanding and somewhat "triangular/circular", but has been in operation in our application for many years without problems. After recent upgrade from 10.1 to 10.2.12, we promptly got broken FKs. (I have not yet retested with 10.1). How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each). SET FOREIGN_KEY_CHECKS=0; CREATE TABLE member ( id int(11) NOT NULL AUTO_INCREMENT, default_address_id int(11) DEFAULT NULL, default_card_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY member_FI_2 (default_address_id), KEY member_FI_3 (default_card_id), CONSTRAINT member_FK_2 FOREIGN KEY (default_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT member_FK_3 FOREIGN KEY (default_card_id) REFERENCES payment_method (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO member VALUES (1,2,3); 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; SELECT * FROM member; +----+--------------------+-----------------+ | id | default_address_id | default_card_id | +----+--------------------+-----------------+ | 1 | 2 | 3 | +----+--------------------+-----------------+ SELECT * FROM address; +----+-----------+ | id | member_id | +----+-----------+ | 2 | 1 | +----+-----------+ SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | 2 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; SELECT * FROM member; [ empty ] SELECT * FROM address; [ empty ] SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | NULL | +----+-----------+-----------------------+ ------------ DB is now in an inconsistent state: payment_method.member_id=1 references a non-existent member record. Am I missing something, or should this never happen? Thanks -- Oliver Schönrock
On 03/02/18 13:42, Oliver Schonrock wrote:
After recent upgrade from 10.1 to 10.2.12, we promptly got broken FKs. (I have not yet retested with 10.1).
So, I have just done the test with 10.1, and can confirm that 10.1 DOES NOT exhibit this broken behaviour. Of course there was switch from Percona XtraDB to InnoDB engine between 10.1 and 10.2... Proof: Server version: 10.1.30-MariaDB SELECT * FROM member; +----+--------------------+-----------------+ | id | default_address_id | default_card_id | +----+--------------------+-----------------+ | 1 | 2 | 3 | +----+--------------------+-----------------+ SELECT * FROM address; +----+-----------+ | id | member_id | +----+-----------+ | 2 | 1 | +----+-----------+ SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | 2 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; SELECT * FROM member; [ empty ] SELECT * FROM address; [ empty ] SELECT * FROM payment_method; [ empty ] All gone, as they should be. Next steps? Is this a bug report for 10.2 with the InnoDB engine? -- Oliver Schönrock
On 03/02/18 13:43, Oliver Schonrock wrote:
So, I have just done the test with 10.1, and can confirm that 10.1 DOES NOT exhibit this broken behaviour. Of course there was switch from Percona XtraDB to InnoDB engine between 10.1 and 10.2...
MySQL 5.7.21 also works fine... InnoDB differences between MySQL 5.7.21 and MariaDB 10.2.12 ? Can anyone reproduce? -- Oliver Schönrock Mobile : +44 7880 617 446 email : oliver@schonrocks.com
Further info below: On 03/02/18 13:42, Oliver Schonrock wrote:
DELETE FROM member WHERE id = 1;
SELECT * FROM member; [ empty ]
SELECT * FROM address; [ empty ]
SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | NULL | +----+-----------+-----------------------+
------------
DB is now in an inconsistent state: payment_method.member_id=1 references a non-existent member record.
It gets slightly worse. Once the DB is in this inconsistent state, queries that should highlight the problem, do not, eg: 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; returns an empty result set. If you mysqldump the inconsistent DB, and re-insert it, then the above query returns: +----+-----------+ | id | member_id | +----+-----------+ | 3 | 1 | +----+-----------+ as it should. Suspect inconsistent INDEX, or similar ? -- Oliver Schönrock
On 03/02/18 13:42, Oliver Schonrock wrote:
How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each).
I have managed to simplify the test case further, by eliminating the "circular" ON DELETE SET NULL Contraints and their fields. Now it's a simple: parent => child1 => child2 with ON DELETE SET NULL FK to child1 to complete a "triangle" New test case SQL and results: -- Server version: 10.2.12-MariaDB FreeBSD Ports 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; 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 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; 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 -- 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 -- Oliver Schönrock
On 03/02/18 22:58, Oliver Schonrock wrote:
I have managed to simplify the test case further, by eliminating the "circular" ON DELETE SET NULL Contraints and their fields. Now it's a simple:
parent => child1 => child2 with ON DELETE SET NULL FK to child1 to complete a "triangle"
Visual version of DB structure (the triangle), see attached png (hopefully that works on this list). When I delete member parent record => address child is cascade deleted, BUT payment_method child is NOT cascade deleted. -- Oliver Schönrock
On 03/02/18 22:58, Oliver Schonrock wrote:
On 03/02/18 13:42, Oliver Schonrock wrote:
How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each). I have managed to simplify the test case further, by eliminating the "circular" ON DELETE SET NULL Contraints and their fields.
For anyone interested, I have reported this here: https://jira.mariadb.org/browse/MDEV-15199 and the issue has been confirmed by MariaDB developers. -- Oliver Schönrock
participants (1)
-
Oliver Schonrock