Developing (v7+)

Foreign key checks issues when updating attributes

Permalink May 15, 2017 at 5:59 AM

For some reason we started running into foreign key check issues in our upgraded v8 site from v7. Something must have gotten corrupt somewhere because now anytime we do anything with attributes we get a foreign key integrity message about a foreign key check failing. Yes from command line sql I can disable foreign key checks for one off queries etc but I was wondering if there is some way to rebuild the foreign keys and indexes so everything is working again as it should be? This is becoming a nightmare for us and I'm not sure the best way to proceed. Any help appreciated.

Here is an example of the message from saving a page attribute through the UI:

An exception occurred while executing 'INSERT INTO atBoolean (value, avID) VALUES (?, ?)' with params [1, 1908]: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`atBoolean`, CONSTRAINT `FK_5D5F70A9A2A82A5D` FOREIGN KEY (`avID`) REFERENCES `attributevalues` (`avID`))

zanedev replied on May 15, 2017 at 8:54 pm Permalink Reply

I'm seeing the same thing on a site. Weird thing is it only happens on the live server, my local dev seems to ignore the foreign key checks automatically. Wonder what setting that is to turn it off on the server for now until I fix the bigger issue with the foreign key indexes.

goodsir replied on Jun 7, 2017 at 5:52 pm Permalink Reply

We ran into this issue too. For us it originated from working locally on a Windows machine and pushing up to Linux. It has to do with the way Windows handles database table names and how it converts them to lowercase by default. So even if you've set lower_case_table_names correctly in your php.ini for some reason the foreign key constraints still get added as lowercase.

If you look at the error you're receiving you'll notice the table is in all lowercase "attributevalues" when it should be "AttributeValues" . I manually had to update all those foreign key table names to the proper casing.

zanedev replied on Jun 7, 2017 at 5:59 pm Permalink Reply

Ah makes sense some of our developers were on Windows and we were exporting between them. Thanks for the tips!

lyc replied on Aug 17, 2017 at 10:35 am Permalink Reply

Trying also with Linux, the bug remains...

lyc replied on Aug 21, 2017 at 1:33 pm Permalink Reply

OK: there is a problem, because the database has problems...

Hoping that I was not mistaken, this is what logically (except error) should be done on a database before saving it (please try on a testing database, make a backup first of all !, ...) :

DELETE r FROM atAddress r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
DELETE r FROM atBoolean r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
DELETE r FROM atDateTime r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
DELETE r FROM atDefault r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
DELETE r FROM atExpress r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;

Viewing 15 lines of 93 lines. View entire code block.

DELETE r FROM atAddress r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atBoolean r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atDateTime r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atDefault r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atExpress r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atFile r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atFile r 
LEFT JOIN files b ON r.fID = b.fID
WHERE b.fID IS NULL ;
 
DELETE r FROM atNumber r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atSelect r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atSocialLinks r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM atTopic r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM AttributeValues r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ;
 
DELETE r FROM CollectionAttributeKeys r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ;
 
DELETE r FROM CollectionAttributeValues r 
LEFT JOIN attributevalues b ON r.avID = b.avID
WHERE b.avID IS NULL ;
 
DELETE r FROM CollectionAttributeValues r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ;
 
DELETE r FROM ConversationMessageNotifications r 
LEFT JOIN notifications b ON r.nID = b.nID
WHERE b.nID IS NULL ;
 
DELETE r FROM ExpressAttributeKeys r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ;
 
DELETE r FROM ExpressFormFieldSetAssociationControls r 
LEFT JOIN expressformfieldsetcontrols b ON r.id = b.id
WHERE b.id IS NULL ;
 
DELETE r FROM ExpressFormFieldSetAttributeKeyControls r 
LEFT JOIN expressformfieldsetcontrols b ON r.id = b.id
WHERE b.id IS NULL ;
 
DELETE r FROM ExpressFormFieldSetTextControls r 
LEFT JOIN expressformfieldsetcontrols b ON r.id = b.id
WHERE b.id IS NULL ;
 
DELETE r FROM FileAttributeKeys r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ;
 
DELETE r FROM FormSubmissionNotifications r 
LEFT JOIN expressentityentries b ON r.exEntryID = b.exEntryID
WHERE b.exEntryID IS NULL ;
 
DELETE r FROM FormSubmissionNotifications r 
LEFT JOIN notifications b ON r.nID = b.nID
WHERE b.nID IS NULL ; 
 
DELETE r FROM LegacyAttributeKeys r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ; 
 
DELETE r FROM PrivateMessageNotifications r 
LEFT JOIN notifications b ON r.nID = b.nID
WHERE b.nID IS NULL ; 
 
DELETE r FROM QueueMessages r 
LEFT JOIN queues b ON r.queue_id = b.queue_id
WHERE b.queue_id IS NULL ; 
 
DELETE r FROM SiteAttributeKeys r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ; 
 
DELETE r FROM SiteTreeTrees r 
LEFT JOIN sitetrees b ON r.siteTreeID = b.siteTreeID
WHERE b.siteTreeID IS NULL ; 
 
DELETE r FROM UserAttributeKeys r 
LEFT JOIN attributekeys b ON r.akID = b.akID
WHERE b.akID IS NULL ; 
 
DELETE r FROM UserSignupNotifications r 
LEFT JOIN notifications b ON r.nID = b.nID
WHERE b.nID IS NULL ; 
 
DELETE r FROM WorkflowProgressNotifications r 
LEFT JOIN notifications b ON r.nID = b.nID
WHERE b.nID IS NULL ;

Hoping that this does not create any problems other than those that should have been fixed...

Regards,

Forums

Developing (v7+)

Foreign key checks issues when updating attributes

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?