I have a database where some of the tables have parent –> child –> grandchild relationships through foreign keys as shown below.
CREATE TABLE Parent ( ParentID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,SomeValue VARCHAR(100) NOT NULL ); CREATE TABLE Child ( ChildID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,ParentID INT UNSIGNED NOT NULL ,SomeValue VARCHAR(100) NOT NULL ); ALTER TABLE Child ADD CONSTRAINT FK_Child_ParentID FOREIGN KEY (ParentID) REFERENCES Parent (ParentID) ON DELETE CASCADE; CREATE TABLE GrandChild ( GrantChildID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,ChildID INT UNSIGNED NOT NULL ,SomeValue VARCHAR(100) NOT NULL ); ALTER TABLE GrandChild ADD CONSTRAINT FK_GrandChild_ChildID FOREIGN KEY (ChildID) REFERENCES Child (ChildID) ON DELETE CASCADE;
Some sample data I have tested with is:
INSERT INTO Parent (SomeValue) VALUES ('ParentA'), ('ParentB'), ('ParentC'), ('ParentD'); INSERT INTO Child (ParentID, SomeValue) VALUES (1, 'ChildA'), (1, 'ChildB'), (2, 'ChildC'), (3, 'ChildD'), (3, 'ChildE'); INSERT INTO GrandChild (ChildID, SomeValue) VALUES (1, 'GrandChildA'), (2, 'GrandChildB'), (2, 'GrandChildB'), (3, 'GrandChildD'), (3, 'GrandChildE'), (3, 'GrandChildF');
And the stored procedures I’m using to delete records are:
DELIMITER // CREATE PROCEDURE DeleteChild(IN Child_ID INT) BEGIN DELETE FROM Child WHERE ChildID = Child_ID; END // DELIMITER ; DELIMITER // CREATE PROCEDURE DeleteParent(IN Parent_ID INT) BEGIN DELETE FROM Parent WHERE ParentID = Parent_ID; END // DELIMITER ;
After having done some testing I’ve discovered a data integrity problem where under certain circumstances it’s possible for a parent record to be deleted without it’s children and grandchildren records also being deleted, even though I have the ON DELETE CASCADE option enabled. I’ve managed to replicate the issue and the following steps result in it occurring.
Start a long-running statement that inserts a large number of records into the table ‘GrandChild’. I’ve been trying to simulate large insert jobs so I’m running the following statement which takes several minutes if there are around 1 million records:
INSERT INTO GrandChild (ChildID, SomeValue) SELECT ChildID, SomeValue FROM GrandChild;
While the previous statement is running, issue another statement to delete a record from the table ‘Child’ that has grandchildren records in ‘GrandChild’. I’ve tested this by calling the proc DeleteChild(1). The statement hangs and eventually times out after 50 seconds (the default innodb_lock_wait_timeout value) with an error 1205 after failing to get a lock on ‘GrandChild’.
Before the previous statement times out, issue another statement that deletes a record from the table ‘Parent’ that also has child and grandchild records. I’ve testing this by calling proc DeleteParent(1).
The ordering looks something like the following:
What happens here is that as soon as the statement in step 2 times out, the statement in step 3 completes but it does so having only deleted the matching records in the table ‘Parent’ but not any of the record’s children or grandchildren, leaving me with orphan records.
Can someone please explain to me why this might be happening?
I’m using MariaDB 10.2.11 with InnoDB and most of the settings I’m using are the defaults. Let me know if any extra information is needed to help debug this.
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!