INSERT statement conflicted with the FOREIGN KEY constraint for Payment methods and Regions

[BenWhite27]

Umbraco: v13.0.3 Deploy: v13.0.3 Commerce.Deploy: v13

TL;DR;

Deleted regions are not exported to UDAs but are still listed as dependencies in payment method UDAs

Full Info

When cloning my Cloud environment to my local PC the installer fails to deploy the schema to the fresh database. This is before the Restoring content phase happens. The same thing also happens when creating a new development environment in Umbraco Cloud.

The deploy.log output is at the bottom. Looking at the UDA files I can see that the problem artifact umb://umbraco-commerce-payment-method/93b59042f0554000a0a2018c8757acd5 has umbraco-commerce-region dependencies listed that don't exist in the freshly created local database and aren't listed in the Live environment's backoffice either.

In total the artifact has 10 region dependencies, which matches the live database, however 2 of them have deletedTimestamp values. Only 8 region UDA files are exported to disk, even after a fresh export to UDA performed in the live environment. I can also confirm that the PaymentMethodAllowedCountryRegions table has entries for the deleted regions too.

I've had a support case with Umbraco Cloud since the beginning of January, but it's starting to feel like a bug to me so I thought I'd post here as well.

Can I safely remove the extra dependencies from the UDA file, or will they be added again with a fresh export?

EXCEPTION:

Umbraco.Deploy.Core.Exceptions.ProcessArtifactException: Process pass #4 failed for artifact umb://umbraco-commerce-payment-method/93b59042f0554000a0a2018c8757acd5.
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessArtifacts(IEnumerable`1 arts, String ownerEmail, IDeployContext context, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessPackageAsync(Guid sessionId, Package package, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.Proceed(List`1 artifacts, Manifest manifest)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsyncSub(IWorkContext context, List`1 resume, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsync(IWorkContext context, CancellationToken token)

INNER:
    Microsoft.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoCommercePaymentMethodAllowedCountryRegion_umbracoCommerceRegion". The conflict occurred in database "c:\[PATH REMOVED]", table "dbo.umbracoCommerceRegion", column 'id'.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoCommercePaymentMethodAllowedCountryRegion_umbracoCommerceRegion". The conflict occurred in database "c:\[PATH REMOVED]", table "dbo.umbracoCommerceRegion", column 'id'.
The statement has been terminated.
The statement has been terminated.
       at Umbraco.Commerce.Infrastructure.Resiliency.PollyExecutionStrategyBase.Execute[TResult](Func`1 operation, Func`1 verifySucceeded)
       at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.Execute(IUnitOfWorkOptions options, Action`1 action)
       at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.Execute(Action`1 action)
       at Umbraco.Commerce.Deploy.Connectors.ServiceConnectors.UmbracoCommercePaymentMethodServiceConnector.Pass4(ArtifactDeployState`2 state, IDeployContext context)
       at Umbraco.Commerce.Deploy.Connectors.ServiceConnectors.UmbracoCommercePaymentMethodServiceConnector.Process(ArtifactDeployState`2 state, IDeployContext context, Int32 pass)
       at Umbraco.Deploy.Infrastructure.Connectors.ServiceConnectors.ServiceConnectorBase`3.Process(ArtifactDeployState dart, IDeployContext context, Int32 pass)
       at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessArtifacts(IEnumerable`1 arts, String ownerEmail, IDeployContext context, CancellationToken token)

EXCEPTION:

Umbraco.Deploy.Core.Exceptions.ProcessArtifactException: Process pass #4 failed for artifact umb://umbraco-commerce-payment-method/93b59042f0554000a0a2018c8757acd5.
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessArtifacts(IEnumerable`1 arts, String ownerEmail, IDeployContext context, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessPackageAsync(Guid sessionId, Package package, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.Proceed(List`1 artifacts, Manifest manifest)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsyncSub(IWorkContext context, List`1 resume, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsync(IWorkContext context, CancellationToken token)

INNER:
    Microsoft.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoCommercePaymentMethodAllowedCountryRegion_umbracoCommerceRegion". The conflict occurred in database "c:\[PATH REMOVED]", table "dbo.umbracoCommerceRegion", column 'id'.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoCommercePaymentMethodAllowedCountryRegion_umbracoCommerceRegion". The conflict occurred in database "c:\[PATH REMOVED]", table "dbo.umbracoCommerceRegion", column 'id'.
The statement has been terminated.
The statement has been terminated.
       at Umbraco.Commerce.Infrastructure.Resiliency.PollyExecutionStrategyBase.Execute[TResult](Func`1 operation, Func`1 verifySucceeded)
       at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.Execute(IUnitOfWorkOptions options, Action`1 action)
       at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.Execute(Action`1 action)
       at Umbraco.Commerce.Deploy.Connectors.ServiceConnectors.UmbracoCommercePaymentMethodServiceConnector.Pass4(ArtifactDeployState`2 state, IDeployContext context)
       at Umbraco.Commerce.Deploy.Connectors.ServiceConnectors.UmbracoCommercePaymentMethodServiceConnector.Process(ArtifactDeployState`2 state, IDeployContext context, Int32 pass)
       at Umbraco.Deploy.Infrastructure.Connectors.ServiceConnectors.ServiceConnectorBase`3.Process(ArtifactDeployState dart, IDeployContext context, Int32 pass)
       at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessArtifacts(IEnumerable`1 arts, String ownerEmail, IDeployContext context, CancellationToken token)

[Failed]

EXCEPTION
Umbraco.Deploy.Core.Exceptions.ProcessArtifactException: Process pass #4 failed for artifact umb://umbraco-commerce-payment-method/93b59042f0554000a0a2018c8757acd5.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoCommercePaymentMethodAllowedCountryRegion_umbracoCommerceRegion". The conflict occurred in database "c:\[PATH REMOVED]", table "dbo.umbracoCommerceRegion", column 'id'.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoCommercePaymentMethodAllowedCountryRegion_umbracoCommerceRegion". The conflict occurred in database "c:\[PATH REMOVED]", table "dbo.umbracoCommerceRegion", column 'id'.
The statement has been terminated.
The statement has been terminated.
   at Umbraco.Commerce.Infrastructure.Resiliency.PollyExecutionStrategyBase.Execute[TResult](Func`1 operation, Func`1 verifySucceeded)
   at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.Execute(IUnitOfWorkOptions options, Action`1 action)
   at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.Execute(Action`1 action)
   at Umbraco.Commerce.Deploy.Connectors.ServiceConnectors.UmbracoCommercePaymentMethodServiceConnector.Pass4(ArtifactDeployState`2 state, IDeployContext context)
   at Umbraco.Commerce.Deploy.Connectors.ServiceConnectors.UmbracoCommercePaymentMethodServiceConnector.Process(ArtifactDeployState`2 state, IDeployContext context, Int32 pass)
   at Umbraco.Deploy.Infrastructure.Connectors.ServiceConnectors.ServiceConnectorBase`3.Process(ArtifactDeployState dart, IDeployContext context, Int32 pass)
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessArtifacts(IEnumerable`1 arts, String ownerEmail, IDeployContext context, CancellationToken token)
ClientConnectionId:040941cd-1add-4552-9781-46e37639bb3e
Error Number:547,State:0,Class:16
   --- End of inner exception stack trace ---
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessArtifacts(IEnumerable`1 arts, String ownerEmail, IDeployContext context, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ProcessPackageAsync(Guid sessionId, Package package, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.Proceed(List`1 artifacts, Manifest manifest)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsyncSub(IWorkContext context, List`1 resume, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsync(IWorkContext context, CancellationToken token)

[Frost117]

Hi @BenWhite27

Had a look at this on my project and I was able to reproduce the issue by doing the following steps which you provided:

1. Create Region
2. Create Payment Method and a Shipping Method with the region assigned.
3. Check that the UDA schema is exported as expected.
4. Delete the Region
5. See that the Region no longer has a UDA file, but the region remains as a dependency in the UDA files for the Payment method and Shipping method

Additionally, when deleting the region, if you have not cleared the dependency from the Payment method as a allow country or shipping method allow region/country that reference will stay in the database and not be cleared out.

[BenWhite27]

Quick update: I worked with Umbraco Cloud support to resolve the conflicts in my database. The instructions they provided have corrected the data in my database and permits a painless clone/restore of the cloud project once more.

In my case it was the Payment Method Allowed Regions table which still had references to some regions that were deleted. The instructions for Support were as follows, repeated for each payment method:-

1. Unselect all regions
2. Save
3. Unselect all countries
4. Save
5. Re-select the original Countries and Regions
6. Save

This effectively rebuilds the relationships and clears the table of records related to the deleted regions.

I still believe there is a bug or two present here.

1. As there is no way to restore a region from the back office, I'm not sure why it was marked as deleted, instead of actually deleting the region record and cascading the deletes down to the Allowed region assignments. If the region record is required still, then there's probably not much Umbraco Commerce can do for this one.
2. The Deploy package should either...
   • not export dependencies to UDA files if the principal of the dependency is deleted.
   • OR
   • also export the deleted principal along with the normal entities.

[mattbrailsford]

Hey @BenWhite27

Really glad the workaround worked, and rest assured we will address this. We just wanted to ensure you weren't having to wait around for a fix to be released in the meantime.

Thank you for raising this issue 🙌