[5.x]: After Update Migration with foreign_key constraint of matrix blocks fails integrity constraint violation

[LeoWie93]

What happened?

Description

We are trying to run the Database Migrations after Upgrading a Project to Craft 5.

Steps to reproduce

1. Update our Craft 4 Project to Version 5.0.X

Expected behavior

The migration runs without an error. => succesfully migrates matrix blocks

Actual behavior

Craft CMS version

5.0.x

PHP version

8.2

Operating system and version

Linux

Database type and version

Mysql 8.0.36

Image driver and version

No response

Installed plugins and versions

• redactor
• typed link field

[brandonkelly]

It sounds like you may have some orphaned data in your install.

Can you try running php craft gc on your Craft 4 install, and then reattempt the Craft 5 upgrade?

If you get the error again, we can help look into it further if you can send your database backup over to support@craftcms.com.

[boscho87]

Today Same Problem from 4.9.2 to 5.1.2 -> i send a Mail with additional Information (Mainly the Database Dump)

SELECT [[id]], [[primaryOwnerId]], [[fieldId]], CASE WHEN [[typeId]] = 7 THEN 8 WHEN [[typeId]] = 6 THEN 9 WHEN [[typeId]] = 4 THEN 10 WHEN [[typeId]] = 3 THEN 11 WHEN [[typeId]] = 2 THEN 12
 WHEN [[typeId]] = 1 THEN 13 WHEN [[typeId]] = 5 THEN 14 END, [[dateCreated]], [[dateCreated]], [[dateUpdated]]                                                                               
FROM {{%matrixblocks}} matrixblocks                                                                                                                                                           
WHERE [[matrixblocks.typeId]] IN (7,6,4,3,2,1,5) ... done (time: 0.022s)                                                                                                                      
    > execute SQL: INSERT INTO {{%elements_owners}}                                                                                                                                           
SELECT * FROM {{%matrixblocks_owners}} ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`welte`.`craft_el
ements_owners`, CONSTRAINT `craft_fk_ruftkoweqdqhtxdjnxfmznscmsqgkvjzxvaa` FOREIGN KEY (`elementId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE)                                    
The SQL being executed was: INSERT INTO `craft_elements_owners`                                                                                                                               
SELECT * FROM `craft_matrixblocks_owners` (/application/vendor/yiisoft/yii2/db/Schema.php:676)                                                                                                
#0 /application/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException()                                                                                                   
#1 /application/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute()                                                                                                   
#2 /application/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()                                                                                                          
#3 /application/vendor/craftcms/cms/src/migrations/m230617_070415_entrify_matrix_blocks.php(214): yii\db\Migration->execute()                                                                 
#4 /application/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m230617_070415_entrify_matrix_blocks->safeUp()                                                                 
#5 /application/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()                                                                              
#6 /application/vendor/craftcms/cms/src/console/controllers/MigrateController.php(365): yii\console\controllers\BaseMigrateController->migrateUp()                                            
#7 [internal function]: craft\console\controllers\MigrateController->actionAll()                                                                                                              
#8 /application/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()                                                                                                         
#9 /application/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()                                                                                          
#10 /application/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction()                                                                                            
#11 /application/vendor/craftcms/cms/src/console/ControllerTrait.php(89): yii\console\Controller->runAction()                                                                                 
#12 /application/vendor/yiisoft/yii2/base/Module.php(552): craft\console\controllers\MigrateController->runAction()                                                                           
#13 /application/vendor/yiisoft/yii2/console/Application.

[boscho87]

For reference:

Thanks Simon.

There are some orphaned blocks in your DB causing the problem. Those are there because some of you your DB table are missing their foreign keys.

Starting on your Craft 4 site, first, install this plugin and run it from Utilities → Fix FKs.

Next, to delete the blocks, run DELETE FROM craft_matrixblocks WHERE id NOT IN (SELECT id FROM craft_elements).

You can now composer update to Craft 5 then php craft migrate/all, and the update will work.

Let me know if that helps and we'll close the issue :)

[boscho87]

@LeoWie93 please close the issue