[4.x]: Failed Migration when attempting upgrade to Craft4

LucyRa commented 2 years ago

What happened?

Description

*** applying m220213_015220_matrixblocks_owners_table
    > dropping {{%matrixblocks_owners}} if it exists ... done (time: 0.017s)
    > create table {{%matrixblocks_owners}} ... done (time: 0.012s)
    > add foreign key fk_qolovnzzjofhxuchjotolvstkhoqqootctei: {{%matrixblocks_owners}} (blockId) references {{%matrixblocks}} (id) ... done (time: 0.019s)
    > add foreign key fk_skqbeliwhnedsoudalwyhutmepvrcgxkenmp: {{%matrixblocks_owners}} (ownerId) references {{%elements}} (id) ... done (time: 0.017s)
    > execute SQL: INSERT INTO {{%matrixblocks_owners}} ([[blockId]], [[ownerId]], [[sortOrder]]) 
SELECT [[id]], [[ownerId]], [[sortOrder]] 
FROM {{%matrixblocks}} ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'sortOrder' cannot be null
The SQL being executed was: INSERT INTO `matrixblocks_owners` (`blockId`, `ownerId`, `sortOrder`) 
SELECT `id`, `ownerId`, `sortOrder` 
FROM `matrixblocks` (/Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `ma...')
#1 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('INSERT INTO `ma...')
#2 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()
#3 /Users/lucyramplin/Dev/dhi-digital/build/vendor/craftcms/cms/src/migrations/m220213_015220_matrixblocks_owners_table.php(36): yii\db\Migration->execute('INSERT INTO {{%...')
#4 /Users/lucyramplin/Dev/dhi-digital/build/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m220213_015220_matrixblocks_owners_table->safeUp()
#5 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()
#6 /Users/lucyramplin/Dev/dhi-digital/build/vendor/craftcms/cms/src/console/controllers/MigrateController.php(360): yii\console\controllers\BaseMigrateController->migrateUp('m220213_015220_...')
#7 [internal function]: craft\console\controllers\MigrateController->actionAll()
#8 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#10 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/console/Controller.php(182): yii\base\Controller->runAction('all', Array)
#11 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/base/Module.php(552): yii\console\Controller->runAction('all', Array)
#12 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#13 /Users/lucyramplin/Dev/dhi-digital/build/vendor/craftcms/cms/src/console/Application.php(90): yii\console\Application->runAction('migrate/all', Array)
#14 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#15 /Users/lucyramplin/Dev/dhi-digital/build/vendor/yiisoft/yii2/base/Application.php(384): yii\console\Application->handleRequest(Object(craft\console\Request))
#16 /Users/lucyramplin/Dev/dhi-digital/build/craft(22): yii\base\Application->run()
#17 {main}
*** failed to apply m220213_015220_matrixblocks_owners_table (time: 0.119s)

0 from 24 migrations were applied.

Migration failed. The rest of the migrations are canceled.

Steps to reproduce

Updated composer.json dependencies, ready for Craft4
Updated PHP version to 8.0.2
Ran php craft migrate/all

Expected behavior

Expected migrations to run, to finalise the upgrade to Craft4

Actual behavior

Migration for the matrix block owners table failed, seemingly due to an empty table column.

When attempting to finalise the migration in the browser the following is output:

Integrity constraint violation: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'sortOrder' cannot be null
The SQL being executed was: INSERT INTO matrixblocks_owners (blockId, ownerId, sortOrder)
SELECT id, ownerId, sortOrder
FROM matrixblocks

Craft CMS version

3.7/4

PHP version

8.0.2

Operating system and version

MacOS X 12.5.1

Database type and version

MySql 8.0.30

Image driver and version

No response

Installed plugins and versions

"craftcms/contact-form": "3.0.0",
"craftcms/postmark": "3.0.0",
"craftcms/redactor": "3.0.2",
"misterbk/mix": "1.6.0",
"nilsenpaul/complete-cookie-consent": "1.1",
"nystudio107/craft-seomatic": "4.0.7",
"percipioglobal/craft-colour-swatches": "4.2.0.1",
"putyourlightson/craft-snaptcha": "4.0.1",
"putyourlightson/craft-untransform": "2.0.1",
"scaramangagency/craftagram": "2.0.0",
"sebastianlenz/linkfield": "2.1.4",
"solspace/craft-freeform": "4.0.2",
"spicyweb/craft-embedded-assets": "3.0.5",
"verbb/navigation": "2.0.4",
"verbb/super-table": "3.0.0",

brianjhanson commented 2 years ago

Thanks for reporting! This looks to be the same issue as #11843 which has been fixed for the next release.

LucyRa commented 2 years ago

@brianjhanson - that's great, but how do I resolve this for my project?

angrybrad commented 2 years ago

@LucyRa You can restore your database to a 3.7 pre-update attempt database dump. Then when you do the Craft 4 update, change your composer.json's craftcms/cms requirement to:

"require": {
  "craftcms/cms": "dev-develop#a01c8c636a2db6c47537cfa0d4a19f8b90a52242 as 4.2.3",
  "...": "..."
}

Then run composer update - that'll pull in the fix early and the update should go through fine for you.

craftcms / cms