[5.x]: failed to apply m230617_070415_entrify_matrix_blocks

[akiraWalk]

What happened?

Description

I'm in the process of upgrading Craft CMS and Craft Commerce from 4 to 5 and am getting to the migration portion of the upgrade and get "failed to apply m230617_070415_entrify_matrix_blocks"
The full error I get

Steps to reproduce

1. following the Craft CMS/Commerce upgrade guides for 4 to 5.
2. php82 craft project-config/rebuild
3. php82 craft utils/fix-field-layout-uids
4. changing over plugins to Craft 5 compatibility (added both before and after to installed plugins and versions)
5. composer update
6. php82 craft up (this is where the error happens)

The closest issue I found was "[5.x]: After Update Migration with foreign_key constraint of matrix blocks fails integrity constraint violation #14839"
and so I tried adding into the above steps

1. craft gc
2. use Fix FK plugin
3. run DELETE FROM craft_matrixblocks WHERE id NOT IN (SELECT id FROM craft_elements)
4. run the upgrade process and the issue persists.

Craft CMS version

5.1.6

PHP version

8.2.5

Operating system and version

Linux 3.10.0-1127.8.2.el7.x86_64

Database type and version

MySQL 8.0.29

Image driver and version

gd 8.2.5

Installed plugins and versions

"craftcms/cms": "4.9.5",
"craftcms/commerce": "^4.0.0",
"vlucas/phpdotenv": "^5.4.0",
"verbb/super-table": "3.0.14",
"craftcms/redactor": "3.0.4",
"ether/seo": "4.2.2",
"mmikkel/cp-field-inspect": "1.4.4",
"sebastianlenz/linkfield": "2.1.5",
"xpertbot/craft-wheelform": "3.2.1",
"verbb/image-resizer": "3.0.11",
"verbb/cloner": "2.0.4",
"doublesecretagency/craft-cpcss": "2.6.0",
"hybridinteractive/craft-position-fieldtype": "4.0.0",
"craftpulse/craft-colour-swatches": "4.4.0",
"utakka/redactor-anchors": "1.5.0",
"verbb/cp-nav": "4.0.12",
"craftcms/ckeditor": "3.8.3",
"verbb/hyper": "1.1.30",
"presseddigital/linkit": "4.0.4.1",
"craftcms/fix-fks": "2.1.0"

upgrading to

"craftcms/cms": "5.1.6",
"craftcms/commerce": "5.0.7",
"vlucas/phpdotenv": "^5.4.0",
"verbb/super-table": "4.0.0",
"ether/seo": "v5.0.0-rc2",
"mmikkel/cp-field-inspect": "2.0.1",
"xpertbot/craft-wheelform": "4.0.0",
"verbb/image-resizer": "4.0.0",
"doublesecretagency/craft-cpcss": "3.00",
"hybridinteractive/craft-position-fieldtype": "5.0.0",
"craftpulse/craft-colour-swatches": "5.0.0",
"verbb/cp-nav": "5.0.0",
"craftcms/ckeditor": "4.0.6",
"verbb/hyper": "2.0.0",
"presseddigital/linkit": "5.0.0",
"craftcms/fix-fks": "2.1.0"

[akiraWalk]

the text from the screenshot for clarity

execute SQL: INSERT INTO {{%entries}} ([[id]], [[primaryOwnerId]], [[fieldId]], [[typeId]], [[postDate]], [[dateCreated]], [[dateUpdated]]) SELECT [[id]], [[primaryOwnerId]], [[fieldId]], CASE WHEN [[typeId]] = 24 THEN 30 WHEN [[typeId]] = 18 THEN 31 WHEN [[typeId]] = 21 THEN 32 WHEN [[typeId]] = 22 THEN 33 WHEN [[typeId]] = 11 THEN 34 WHEN [[typeId]] = 19 THEN 35 WHEN [[typeId]] = 20 THEN 36 WHEN [[typeId]] = 15 THEN 37 WHEN [[typeId]] = 23 THEN 38 WHEN [[typeId]] = 13 THEN 39 WHEN [[typeId]] = 16 THEN 40 WHEN [[typeId]] = 14 THEN 41 WHEN [[typeId]] = 12 THEN 42 WHEN [[typeId]] = 17 THEN 43 WHEN [[typeId]] = 8 THEN 44 WHEN [[typeId]] = 5 THEN 45 WHEN [[typeId]] = 36 THEN 46 WHEN [[typeId]] = 37 THEN 47 WHEN [[typeId]] = 32 THEN 48 WHEN [[typeId]] = 34 THEN 49 WHEN [[typeId]] = 40 THEN 50 WHEN [[typeId]] = 33 THEN 51 WHEN [[typeId]] = 38 THEN 52 WHEN [[typeId]] = 25 THEN 53 WHEN [[typeId]] = 28 THEN 54 WHEN [[typeId]] = 31 THEN 55 WHEN [[typeId]] = 39 THEN 56 WHEN [[typeId]] = 41 THEN 57 WHEN [[typeId]] = 30 THEN 58 WHEN [[typeId]] = 26 THEN 59 WHEN [[typeId]] = 27 THEN 60 WHEN [[typeId]] = 29 THEN 61 WHEN [[typeId]] = 35 THEN 62 END, [[dateCreated]], [[dateCreated]], [[dateUpdated]] FROM {{%matrixblocks}} matrixblocks WHERE [[matrixblocks.typeId]] IN (24,18,21,22,11,19,20,15,23,13,16,14,12,17,8,5,36,37,32,34,40,33,38,25,28,31,39,41,30,26,27,29,35) ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (amcsearch_craft5.craft_entries, CONSTRAINT craft_fk_zczocrptptqdwwpqqyuzdfeerwhaxffybnnx FOREIGN KEY (primaryOwnerId) REFERENCES craft_elements (id) ON DELETE CASCADE) The SQL being executed was: INSERT INTO craft_entries (id, primaryOwnerId, fieldId, typeId, postDate, dateCreated, dateUpdated) SELECT id, primaryOwnerId, fieldId, CASE WHEN typeId= 24 THEN 30 WHENtypeId= 18 THEN 31 WHENtypeId= 21 THEN 32 WHENtypeId= 22 THEN 33 WHENtypeId= 11 THEN 34 WHENtypeId= 19 THEN 35 WHENtypeId= 20 THEN 36 WHENtypeId= 15 THEN 37 WHENtypeId= 23 THEN 38 WHENtypeId= 13 THEN 39 WHENtypeId= 16 THEN 40 WHENtypeId= 14 THEN 41 WHENtypeId= 12 THEN 42 WHENtypeId= 17 THEN 43 WHENtypeId= 8 THEN 44 WHENtypeId= 5 THEN 45 WHENtypeId= 36 THEN 46 WHENtypeId= 37 THEN 47 WHENtypeId= 32 THEN 48 WHENtypeId= 34 THEN 49 WHENtypeId= 40 THEN 50 WHENtypeId= 33 THEN 51 WHENtypeId= 38 THEN 52 WHENtypeId= 25 THEN 53 WHENtypeId= 28 THEN 54 WHENtypeId= 31 THEN 55 WHENtypeId= 39 THEN 56 WHENtypeId= 41 THEN 57 WHENtypeId= 30 THEN 58 WHENtypeId= 26 THEN 59 WHENtypeId= 27 THEN 60 WHENtypeId= 29 THEN 61 WHENtypeId= 35 THEN 62 END,dateCreated, dateCreated, dateUpdatedFROMcraft_matrixblocksmatrixblocks WHEREmatrixblocks.typeIdIN (24,18,21,22,11,19,20,15,23,13,16,14,12,17,8,5,36,37,32,34,40,33,38,25,28,31,39,41,30,26,27,29,35) (/home/webdev/www/vhosts/amcsearch-c3/craft/vendor/yiisoft/yii2/db/Schema.php:676)

[i-just]

Hi, thanks for getting in touch! Could you please send your composer.json, composer.lock and database export from before you started updating to [email protected] so that we can dig deeper?

[akiraWalk]

Thank you, an email with the files have been sent

[brandonkelly]

Thanks for sending that in @akiraWalk!

The root issue is that you’ve got a Matrix block row, and several Super Table block rows, which belong to elements that don’t exist anymore.

We should be clearing those out via garbage collection down the road, but I don’t think it should be a blocker for upgrading to Craft 5. So I’ve just relaxed the database’s foreign key checks for the Matrix block migration (0f7efe5) for the next release Craft 5.1.7, as well as PR’d the same change to Super Table (verbb/super-table#558).

If you want to test the fix, make sure you’re running Craft 5.1.7 or later, and change your verbb/super-table requirement in composer.json to dev-craft-5 and run composer update.

[boboldehampsink]

@brandonkelly this breaks upgrading on Heroku, where you don't have permissions for this.

[brandonkelly]

@boboldehampsink Just released Craft 5.2.4 with a fix for that (#15262).