*** failed to apply m240224_024030_migrate_owners_table (time: 0.232s) while migrating craft/neo 4 -> craft/neo 5

[Zae]

Bug Description

Hi,

I'm getting the following error during migration when updating craft/neo from 4 to 5.

Not sure why this is happening, I tried running gc/run, and all kinds of pruning and resaving commands before upgrading and migrating, but so far, sadly I keep running into this error.

*** applying m240224_024030_migrate_owners_table
    > execute SQL: INSERT INTO {{%elements_owners}}
SELECT * FROM {{%neoblocks_owners}} ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`craft`.`elements_owners`, CONSTRAINT `fk_iclnfekldhlutvrzyojiqnaghydrwnyfcpix` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `elements_owners`
SELECT * FROM `neoblocks_owners` (/var/www/html/craft/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /var/www/html/craft/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `el...')
#1 /var/www/html/craft/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute('INSERT INTO `el...')
#2 /var/www/html/craft/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()
#3 /var/www/html/craft/vendor/spicyweb/craft-neo/src/migrations/m240224_024030_migrate_owners_table.php(18): yii\db\Migration->execute('INSERT INTO {{%...')
#4 /var/www/html/craft/vendor/craftcms/cms/src/db/Migration.php(50): benf\neo\migrations\m240224_024030_migrate_owners_table->safeUp()
#5 /var/www/html/craft/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()
#6 /var/www/html/craft/vendor/craftcms/cms/src/console/controllers/MigrateController.php(382): yii\console\controllers\BaseMigrateController->migrateUp('m240224_024030_...')
#7 [internal function]: craft\console\controllers\MigrateController->actionAll()
#8 /var/www/html/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /var/www/html/craft/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#10 /var/www/html/craft/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('all', Array)
#11 /var/www/html/craft/vendor/craftcms/cms/src/console/controllers/MigrateController.php(195): yii\console\Controller->runAction('all', Array)
#12 /var/www/html/craft/vendor/yiisoft/yii2/base/Module.php(552): craft\console\controllers\MigrateController->runAction('all', Array)
#13 /var/www/html/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#14 /var/www/html/craft/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('migrate/all', Array)
#15 /var/www/html/craft/vendor/yiisoft/yii2/base/Controller.php(212): craft\console\Application->runAction('migrate/all', Array)
#16 /var/www/html/craft/vendor/craftcms/cms/src/console/Controller.php(207): yii\base\Controller->run('migrate/all', Array)
#17 /var/www/html/craft/vendor/craftcms/cms/src/console/controllers/UpController.php(60): craft\console\Controller->run('migrate/all', Array)
#18 [internal function]: craft\console\controllers\UpController->actionIndex()
#19 /var/www/html/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#20 /var/www/html/craft/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#21 /var/www/html/craft/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('', Array)
#22 /var/www/html/craft/vendor/craftcms/cms/src/console/ControllerTrait.php(89): yii\console\Controller->runAction('', Array)
#23 /var/www/html/craft/vendor/craftcms/cms/src/console/Controller.php(216): craft\console\Controller->traitRunAction('', Array)
#24 /var/www/html/craft/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction('', Array)
#25 /var/www/html/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('up', Array)
#26 /var/www/html/craft/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('up', Array)
#27 /var/www/html/craft/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('up', Array)
#28 /var/www/html/craft/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest(Object(craft\console\Request))
#29 /var/www/html/craft/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#30 /var/www/html/craft/craft(19): yii\base\Application->run()
#31 {main}
*** failed to apply m240224_024030_migrate_owners_table (time: 0.232s)

Steps to reproduce

1. Have an up and running craft/neo 4 installation
2. Update to craft/neo 5
3. Run craft up
4. error.

Expected behaviour

I expect the command to finish successfully

Neo version

5.2.7

Craft CMS version

5.4.5.1

What is the affected Neo field's propagation method?

Save blocks to all sites the owner element is saved in

Does this issue involve templating, and if so, is eager-loading used?

This is not a templating issue

[ttempleton]

That seems like there are rows in neoblocks_owners referencing Neo blocks that no longer exist.

To confirm, can you try running the following SQL query on the Craft 4 database:

SELECT COUNT(*)
FROM `neoblocks_owners` `nbo`
WHERE NOT EXISTS (
    SELECT *
    FROM `elements` `e`
    WHERE `e`.`id` = `nbo`.`blockId`
);

and let me know if that comes back with a non-zero result.

[Zae]

Hi @ttempleton ,

The count is indeed 3 when running that query.

Is there an easy way to fix this?

[ttempleton]

I've just released 4.2.15 to clear out that data during Craft garbage collection. You'll need to restore your Craft 4 database and run garbage collection there again, and then proceed with the upgrade to Craft 5. Please let me know if you continue to have issues with Neo when upgrading to Craft 5.

[nnuyit]

I've just released 4.2.15 to clear out that data during Craft garbage collection. You'll need to restore your Craft 4 database and run garbage collection there again, and then proceed with the upgrade to Craft 5. Please let me know if you continue to have issues with Neo when upgrading to Craft 5.

Can you specific steps to process migration. We trial several time without success.

1. Updating composer.
   "craftcms/cms": "4.8.0" => "4.12.7"
   "spicyweb/craft-neo": "4.0.6" => "4.2.15".
2. run cli: ddev php craft gc --delete-all-trashed=1.
3. Update composer:
   "craftcms/cms": "4.12.7" => "5.4.5.1"
   "spicyweb/craft-neo": "4.2.15" => "5.2.7".
4. Run cli: ddev php craft up. ==> Same error.
   Result of the above query got 0 before running upgrade cli.

*** applying m240224_024030_migrate_owners_table
    > execute SQL: INSERT INTO {{%elements_owners}}
SELECT * FROM {{%neoblocks_owners}} ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`craft_elements_owners`, CONSTRAINT `craft_fk_ihzmgtnonvibbpsxlcbtabwavemdgqxfzxkc` FOREIGN KEY (`ownerId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `craft_elements_owners`
SELECT * FROM `craft_neoblocks_owners` (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `cr...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute('INSERT INTO `cr...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()
#3 /var/www/html/vendor/spicyweb/craft-neo/src/migrations/m240224_024030_migrate_owners_table.php(18): yii\db\Migration->execute('INSERT INTO {{%...')
#4 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(50): benf\neo\migrations\m240224_024030_migrate_owners_table->safeUp()
#5 /var/www/html/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()
#6 /var/www/html/vendor/craftcms/cms/src/console/controllers/MigrateController.php(382): yii\console\controllers\BaseMigrateController->migrateUp('m240224_024030_...')
#7 [internal function]: craft\console\controllers\MigrateController->actionAll()
#8 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#10 /var/www/html/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('all', Array)
#11 /var/www/html/vendor/craftcms/cms/src/console/controllers/MigrateController.php(195): yii\console\Controller->runAction('all', Array)
#12 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): craft\console\controllers\MigrateController->runAction('all', Array)
#13 /var/www/html/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#14 /var/www/html/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('migrate/all', Array)
#15 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(212): craft\console\Application->runAction('migrate/all', Array)
#16 /var/www/html/vendor/craftcms/cms/src/console/Controller.php(207): yii\base\Controller->run('migrate/all', Array)
#17 /var/www/html/vendor/craftcms/cms/src/console/controllers/UpController.php(60): craft\console\Controller->run('migrate/all', Array)
#18 [internal function]: craft\console\controllers\UpController->actionIndex()
#19 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#20 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#21 /var/www/html/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('', Array)
#22 /var/www/html/vendor/craftcms/cms/src/console/ControllerTrait.php(89): yii\console\Controller->runAction('', Array)
#23 /var/www/html/vendor/craftcms/cms/src/console/Controller.php(216): craft\console\Controller->traitRunAction('', Array)
#24 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction('', Array)
#25 /var/www/html/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('up', Array)
#26 /var/www/html/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('up', Array)
#27 /var/www/html/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('up', Array)
#28 /var/www/html/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest(Object(craft\console\Request))
#29 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#30 /var/www/html/craft(23): yii\base\Application->run()
#31 {main}
*** failed to apply m240224_024030_migrate_owners_table (time: 0.005s)

[ttempleton]

@nnuyit That could possibly be a similar issue to craftcms/cms#14839 (comment)? Try running the following SQL query before upgrading:

DELETE FROM craft_neoblocks WHERE id NOT IN (SELECT id FROM craft_elements);

And let me know if that resolves your issue.

[nnuyit]

@nnuyit That could possibly be a similar issue to craftcms/cms#14839 (comment)? Try running the following SQL query before upgrading:

DELETE FROM craft_neoblocks WHERE id NOT IN (SELECT id FROM craft_elements);

And let me know if that resolves your issue.

It's still not working.

    > execute SQL: INSERT INTO {{%elements_owners}}
SELECT * FROM {{%neoblocks_owners}} ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`craft_elements_owners`, CONSTRAINT `craft_fk_enrxmuymeoripqbbaydmqzjdktcfhdeaakzh` FOREIGN KEY (`ownerId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `craft_elements_owners`
SELECT * FROM `craft_neoblocks_owners` (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `cr...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute('INSERT INTO `cr...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()
#3 /var/www/html/vendor/spicyweb/craft-neo/src/migrations/m240224_024030_migrate_owners_table.php(18): yii\db\Migration->execute('INSERT INTO {{%...')
#4 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(50): benf\neo\migrations\m240224_024030_migrate_owners_table->safeUp()
#5 /var/www/html/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()
#6 /var/www/html/vendor/craftcms/cms/src/console/controllers/MigrateController.php(382): yii\console\controllers\BaseMigrateController->migrateUp('m240224_024030_...')
#7 [internal function]: craft\console\controllers\MigrateController->actionAll()
#8 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#10 /var/www/html/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('all', Array)
#11 /var/www/html/vendor/craftcms/cms/src/console/controllers/MigrateController.php(195): yii\console\Controller->runAction('all', Array)
#12 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): craft\console\controllers\MigrateController->runAction('all', Array)
#13 /var/www/html/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#14 /var/www/html/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('migrate/all', Array)
#15 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(212): craft\console\Application->runAction('migrate/all', Array)
#16 /var/www/html/vendor/craftcms/cms/src/console/Controller.php(207): yii\base\Controller->run('migrate/all', Array)
#17 /var/www/html/vendor/craftcms/cms/src/console/controllers/UpController.php(60): craft\console\Controller->run('migrate/all', Array)
#18 [internal function]: craft\console\controllers\UpController->actionIndex()
#19 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#20 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#21 /var/www/html/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('', Array)
#22 /var/www/html/vendor/craftcms/cms/src/console/ControllerTrait.php(89): yii\console\Controller->runAction('', Array)
#23 /var/www/html/vendor/craftcms/cms/src/console/Controller.php(216): craft\console\Controller->traitRunAction('', Array)
#24 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction('', Array)
#25 /var/www/html/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('up', Array)
#26 /var/www/html/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('up', Array)
#27 /var/www/html/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('up', Array)
#28 /var/www/html/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest(Object(craft\console\Request))
#29 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#30 /var/www/html/craft(23): yii\base\Application->run()
#31 {main}
*** failed to apply m240224_024030_migrate_owners_table (time: 0.007s)