5 Replies - 295 Views - Last Post: 18 April 2018 - 08:09 AM

#1 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,792
  • Joined: 15-January 14

Foreign key constraint not working

Posted 17 April 2018 - 03:11 PM

This just started happening, this has been working for a while and for some reason today it decided to break. I can't replicate this on other systems, they work fine, and unfortunately the one with the problem is a live site for a big client.

So, the error message:

Quote

Fatal error: File: rebuild_supervisor_ugs.php; Line: 39; Page mode: ; User ID: ; Database Error #1451: Cannot delete or update a parent row: a foreign key constraint fails (`adoa_lms`.`usergroup_admins`, CONSTRAINT `usergroup_admins_ibfk_2` FOREIGN KEY (`ugid`) REFERENCES `usergroups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE); SQL Query: DELETE FROM usergroups WHERE parent=482


With the relevant parts:

CREATE TABLE `usergroups` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `parent` int(11) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `title` (`title`),
 KEY `parent` (`parent`),
 CONSTRAINT `usergroups_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `usergroups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2551894 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `usergroup_admins` (
 `ugid` int(11) unsigned NOT NULL,
 `uid` int(11) unsigned NOT NULL,
 PRIMARY KEY (`ugid`,`uid`),
 KEY `ugid` (`ugid`),
 KEY `uid` (`uid`),
 CONSTRAINT `usergroup_admins_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `usergroup_admins_ibfk_2` FOREIGN KEY (`ugid`) REFERENCES `usergroups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


So, I kick everything off with the query in the error message, to delete user groups with a certain parent. That should cascade to several other tables, which has worked every day except today. It looks like the cascading should specifically prevent this error.

As a sanity check, I've verified that there are no rows in usergroup_admins with a UGID that does not exist in the usergroups table. I'm not sure how such a row would get there, but when things don't make sense I start checking that things are like I'm assuming.

Anything else I should check? From searching online all I see are cases where people set up the key incorrectly.

It looks like this is MySQL 5.6.38-1.

Is This A Good Question/Topic? 0
  • +

Replies To: Foreign key constraint not working

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2766
  • View blog
  • Posts: 10,958
  • Joined: 03-December 12

Re: Foreign key constraint not working

Posted 17 April 2018 - 03:49 PM

What is the delete statement targeting? Looks like a circular reference.
Was This Post Helpful? 0
  • +
  • -

#3 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,792
  • Joined: 15-January 14

Re: Foreign key constraint not working

Posted 17 April 2018 - 03:54 PM

What do you mean? I'm deleting all user groups with a certain parent. It seems like any time it deletes a user group it should cascade to all child tables and delete the corresponding records there as well. Where do you think a circular reference might be?
Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,792
  • Joined: 15-January 14

Re: Foreign key constraint not working

Posted 17 April 2018 - 04:38 PM

OK, more weirdness. I wrote some code to just get all user groups with that parent ID, and loop through it deleting each one individually. It stopped trying to delete one particular row with the same foreign key error message pointing to usergroup_admins. I looked up rows in that table where the UGID is the ID of the UG I'm deleting, and it found 1 row. I deleted that row manually, then tried to delete the same user group again, and I got the same error message. There are no rows in usergroup_admins with that ID in it now, and it's still saying there's a foreign key error. This looks like a MySQL bug, or maybe a corrupted table or something.
Was This Post Helpful? 0
  • +
  • -

#5 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,792
  • Joined: 15-January 14

Re: Foreign key constraint not working

Posted 17 April 2018 - 04:46 PM

I'm looking for bug reports, but in the meantime I did a depth-first recursive delete, deleting each record individually, and that worked.
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2766
  • View blog
  • Posts: 10,958
  • Joined: 03-December 12

Re: Foreign key constraint not working

Posted 18 April 2018 - 08:09 AM

I looked thru and couldn't find anything that resembled your issue. Were there self referencing records? Where the parent was the same as the record being deleted?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1