[2021.01] dbstructure update error

[valvin1]

• I have searched open and closed issues for duplicates

Bug Description

dbstructure update is not complete after upgrading from 2020.09 to 2021.01. I'm not 100% sure that these query come from 2021.01 because bin/console dbstructure update didn't show error.
I've seen when going in admin menu and then run bin/console dbstructure update -f

I've launched a post-update which i believed was automatically triggered in my case. (in progress at the moment i'm writing)

Steps to Reproduce

run bin/console dbstructure update -f

Actual Result:

ALTER IGNORE TABLE `item` MODIFY `parent` int unsigned COMMENT 'item.id of the parent to this item if it is a reply of some form; otherwise this must be set to the id of this item', MODIFY `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT 'uri of the top-level parent to this item', MODIFY `parent-uri-id` int unsigned COMMENT 'Id of the item-uri table that contains the top-level parent uri', MODIFY `event-id` int unsigned COMMENT 'Used to link to the event.id', MODIFY `iaid` int unsigned COMMENT 'Deprecated', MODIFY `attach` mediumtext COMMENT 'Deprecated', ADD INDEX `vid` (`vid`), ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Error 1452 occurred during database update:
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-5e9b_9cf2a`, CONSTRAINT `#sql-5e9b_9cf2a_ibfk_10` FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON DELETE CASCADE)

ALTER IGNORE TABLE `notify` MODIFY `iid` int unsigned COMMENT 'item.id', MODIFY `parent` int unsigned COMMENT '', ADD INDEX `uri-id` (`uri-id`), ADD INDEX `parent-uri-id` (`parent-uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Error 1452 occurred during database update:
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-5e9b_9cf2a`, CONSTRAINT `#sql-5e9b_9cf2a_ibfk_2` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON DELETE CASCADE)

ALTER IGNORE TABLE `notify-threads` MODIFY `master-parent-item` int unsigned COMMENT '', ADD INDEX `master-parent-item` (`master-parent-item`), ADD FOREIGN KEY (`master-parent-item`) REFERENCES `item` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`master-parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Error 1452 occurred during database update:
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-5e9b_9cf2a`, CONSTRAINT `#sql-5e9b_9cf2a_ibfk_4` FOREIGN KEY (`master-parent-uri-id`) REFERENCES `item-uri` (`id`) ON DELETE CASCADE)

ALTER IGNORE TABLE `thread` ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT; 

Error 1452 occurred during database update:
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-5e9b_9cf2a`, CONSTRAINT `#sql-5e9b_9cf2a_ibfk_4` FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON DELETE CASCADE)
Errors encountered performing database changes: ALTER IGNORE TABLE `item` MODIFY `parent` int unsigned COMMENT 'item.id of the parent to this item if it is a reply of some form; otherwise this must be set to the id of this item', MODIFY `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT 'uri of the top-level parent to this item', MODIFY `parent-uri-id` int unsigned COMMENT 'Id of the item-uri table that contains the top-level parent uri', MODIFY `event-id` int unsigned COMMENT 'Used to link to the event.id', MODIFY `iaid` int unsigned COMMENT 'Deprecated', MODIFY `attach` mediumtext COMMENT 'Deprecated', ADD INDEX `vid` (`vid`), ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Errors encountered performing database changes: ALTER IGNORE TABLE `notify` MODIFY `iid` int unsigned COMMENT 'item.id', MODIFY `parent` int unsigned COMMENT '', ADD INDEX `uri-id` (`uri-id`), ADD INDEX `parent-uri-id` (`parent-uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Errors encountered performing database changes: ALTER IGNORE TABLE `notify-threads` MODIFY `master-parent-item` int unsigned COMMENT '', ADD INDEX `master-parent-item` (`master-parent-item`), ADD FOREIGN KEY (`master-parent-item`) REFERENCES `item` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`master-parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Errors encountered performing database changes: ALTER IGNORE TABLE `thread` ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT;

Expected Result:

No error :)
Is there a simple to resolve these SQL issues ?

Platform Info

Friendica Version: 2021.01

Friendica Source: docker

PHP version: 7.3.25

SQL version: 10.3

[MrPetovan]

These look eerily similar to the errors reported here: #9746 (comment)

[valvin1]

yes you are right, i'll investigate if there is a way to clean what did optimze.

[nupplaphil]

I had similar problems during the update. I had to search for dangling entries in the database which caused the foreign key problems. In sum there were about 1.000 notify and items without proper contact references which I deleted.

[elrido]

Upgrading the docker container from 2020.09 to 2021.01, I encounter very similar errors:

ALTER IGNORE TABLE `item` MODIFY `parent` int unsigned COMMENT 'item.id of the parent to this item if it is a reply of some form; otherwise this must be set to the id of this item', MODIFY `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT 'uri of the top-level parent to this item', MODIFY `parent-uri-id` int unsigned COMMENT 'Id of the item-uri table that contains the top-level parent uri', MODIFY `event-id` int unsigned COMMENT 'Used to link to the event.id', MODIFY `iaid` int unsigned COMMENT 'Deprecated', MODIFY `attach` mediumtext COMMENT 'Deprecated', ADD INDEX `vid` (`vid`), ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 1452 beim Update der Datenbank aufgetreten
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-alter-6-138`, CONSTRAINT `#sql-alter-6-138_ibfk_10` FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON DELETE CASCADE)
ALTER IGNORE TABLE `notify` MODIFY `iid` int unsigned COMMENT 'item.id', MODIFY `parent` int unsigned COMMENT '', ADD INDEX `uri-id` (`uri-id`), ADD INDEX `parent-uri-id` (`parent-uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 1452 beim Update der Datenbank aufgetreten
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-alter-6-138`, CONSTRAINT `#sql-alter-6-138_ibfk_2` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON DELETE CASCADE)
ALTER IGNORE TABLE `notify-threads` MODIFY `master-parent-item` int unsigned COMMENT '', ADD INDEX `master-parent-item` (`master-parent-item`), ADD FOREIGN KEY (`master-parent-item`) REFERENCES `item` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`master-parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 

Fehler 1452 beim Update der Datenbank aufgetreten
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-alter-6-138`, CONSTRAINT `#sql-alter-6-138_ibfk_3` FOREIGN KEY (`master-parent-item`) REFERENCES `item` (`id`) ON DELETE CASCADE)
ALTER IGNORE TABLE `thread` ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT; 

Fehler 1452 beim Update der Datenbank aufgetreten
Cannot add or update a child row: a foreign key constraint fails (`friendica`.`#sql-alter-6-138`, CONSTRAINT `#sql-alter-6-138_ibfk_4` FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON DELETE CASCADE)
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `item` MODIFY `parent` int unsigned COMMENT 'item.id of the parent to this item if it is a reply of some form; otherwise this must be set to the id of this item', MODIFY `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT 'uri of the top-level parent to this item', MODIFY `parent-uri-id` int unsigned COMMENT 'Id of the item-uri table that contains the top-level parent uri', MODIFY `event-id` int unsigned COMMENT 'Used to link to the event.id', MODIFY `iaid` int unsigned COMMENT 'Deprecated', MODIFY `attach` mediumtext COMMENT 'Deprecated', ADD INDEX `vid` (`vid`), ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `notify` MODIFY `iid` int unsigned COMMENT 'item.id', MODIFY `parent` int unsigned COMMENT '', ADD INDEX `uri-id` (`uri-id`), ADD INDEX `parent-uri-id` (`parent-uri-id`), ADD FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `notify-threads` MODIFY `master-parent-item` int unsigned COMMENT '', ADD INDEX `master-parent-item` (`master-parent-item`), ADD FOREIGN KEY (`master-parent-item`) REFERENCES `item` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`master-parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE; <br />
Fehler beim Ändern der Datenbank aufgetretenALTER IGNORE TABLE `thread` ADD FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, ADD FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT; <br />

These now repeat when I try to php bin/console.php dbstructure update -f. I'll try figuring out how to remove the dangling entries as suggested above.

Edit: Resolved for me using the following queries:

DELETE FROM `item` WHERE id IN (SELECT `item`.id FROM `item` LEFT JOIN `contact` ON `item`.`contact-id` = `contact`.`id` WHERE `contact`.`id` IS NULL);
DELETE FROM `item` WHERE id IN (SELECT `item`.id FROM `item` LEFT JOIN `contact` ON `item`.`owner-id` = `contact`.`id` WHERE `contact`.`id` IS NULL);
DELETE FROM `notify` WHERE id IN (SELECT `notify`.id FROM `notify` LEFT JOIN `item-uri` ON `notify`.`uri-id` = `item-uri`.`id` WHERE `item-uri`.`id` IS NULL);

These depend on the which tables are reported in the errors and on the actual data and may need to be applied to additional reported constraint violations.

[valvin1]

I think mysqloptimize isn't the one for this issue. it looks more it is related as mentioned by @nupplaphil to some inconsistency in database.
if i understand the first query it modifies some columns and add new foreign keys which were not there before. and here there are mismatched entries I need to find :( here there are contacts in items not presenet in my contact table.
it looks like i need to find queries to delete incorrect item.

[nupplaphil]

All of us having this problems are upgrading per docker.. Unfortunately, this sounds like a pattern..

[valvin1]

i'm a bit dubitative of the container origin.
it looks like foreign keys was not present before so many possibilities could lead to inconsistencies. containers could bring runtime issues, upgrade issues but don't see how it could interact on data present in database.

[valvin1]

here the queries I had to execute on my side (do not use if you are not sure what it does):

delete from item where `owner-id` not in (select id from contact);
delete from item where `contact-id` not in (select id from contact);
delete from notify where `uri-id` not in (select id from `item-uri`);

To get it I check all foreign keys and check if there was inconsistencies.