[5.x]: Upgrade fails with SQL constraint error in non-dev environments

[mikejpeters]

What happened?

Description

When upgrading to 5.x, it works the first time in the dev environment, but when running craft up on non-dev (i.e. staging/production) environments the migration fails with an SQL error:

Migrated up successfully.

Applying changes from your project config files ...
- removing commerce.stores.xxxxxxxxxxx ...
error: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`db`.`commerce_taxrates`, CONSTRAINT `fk_qxcjdrmsquxyuxywxlghyommqsgpsbnyzyti` FOREIGN KEY (`taxZoneId`) REFERENCES `commerce_taxzones` (`id`) ON UPDATE CASCADE)
The SQL being executed was: DELETE FROM `commerce_stores` WHERE `id`=1

Steps to reproduce

Note

I have re-attempted the upgrade multiple times from a db backup, and consistently hit this error, but I haven't verified whether this can be specifically reproduced starting from a fresh Craft 4 install. (In my case I'm starting with Craft 3, but don't hit any errors during the upgrade to Craft 4). I hope the error message above will be enough, but if more info is needed to reproduce please let me know!

1. Create a Craft 4 site
2. Install Commerce 4 and set up at least one tax rate, zone, and category, that is used in a product
3. Back up database
4. Update to Craft 5 & Commerce 5
   1. Update versions in composer.json and run composer update
   2. Run craft migrate/all
5. Disable dev mode
6. Restore database backup (Craft 4)
7. Run craft up

Expected behavior

Migrations should apply successfully

Actual behavior

Migrations fail because the store is attempted to be deleted but fails because of a foreign key constraint

Craft CMS version

5.4.9

Craft Commerce version

5.2.1

PHP version

8.2

Operating system and version

No response

Database type and version

MySQL 8.0.36

Image driver and version

No response

Installed plugins and versions

No response

[linear]

PT-2291 [5.x]: Upgrade fails with SQL constraint error in non-dev environments

[nfourtythree]

Hi @mikejpeters

Thank you for your message.

I have tried to replicate this using the steps you mentioned. The one difference being I started with a fresh install of Commerce 4.

I tried both upgrading with and without dev mode enabled and both times I was able to upgrade with no issue.

Looking at the stack trace you have included it seems to be an issue related to stores when applying the project config.

Are you able to send your composer files, a DB backup (from v4) and your project config files (pre-v5 upgrade) to [email protected] and reference this issue.

This way we can try to replicate it in the same scenario.

Thanks!