REFERENCE constraint error when running cart cleanup

[nickfrederiksen]

Which component is this issue related to?

Umbraco Commerce (Core)

Which Umbraco Commerce version are you using? (Please write the exact version, example: 10.1.0)

15.3.13

Bug summary

When the cart clean up task starts, it fails with a reference constraint error on the constraint: FK_umbracoCommerceOrderTransactionActivity_umbracoCommerceOrder

Specifics

We have this error message in our log:

Microsoft.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_umbracoCommerceOrderTransactionActivity_umbracoCommerceOrder". The conflict occurred in database "[REDACTED]", table "dbo.umbracoCommerceOrderTransactionActivity", column 'orderId'.
The statement has been terminated.
   at Umbraco.Commerce.Infrastructure.Resiliency.PollyExecutionStrategyBase.ExecuteAsync[TResult](Func`2 operation, Func`2 verifySucceeded, CancellationToken cancellationToken)
   at Umbraco.Commerce.Core.Services.OrderCleanupService.DeleteCartsOlderThanDaysAsync(Guid storeId, Int32 days, CancellationToken cancellationToken)
   at Umbraco.Commerce.Cms.Scheduling.CartCleanupHostedService.PerformExecuteAsync(Object state)
   at Umbraco.Cms.Infrastructure.HostedServices.RecurringHostedServiceBase.ExecuteAsync(Object state)
ClientConnectionId:339a085d-905a-47c5-ae09-8a8e50e5a0dd
Error Number:547,State:0,Class:16
ClientConnectionId before routing:6290a944-583d-4c9f-b950-9c47502290e0
Routing Destination:[REDACTED]

Steps to reproduce

Enable cart cleanup using this config and wait for it to run:

"Umbraco": {
	"Commerce": {
		"CartCleanupPolicy": {
			"EnableCleanup": true,
			"KeepCartsForDays": 60,
			"FirstRunTime": "* 4 * * *",
			"Period": "1.00:00:00"
		}
	}
}

Expected result / actual result

Old, abandoned, carts are removed.

Dependencies

No response

[mattbrailsford]

Hmm, it does look like we are missing removing the Order Transaction Activity entries when performing a bulk delete.

I've fixed this in our current v16 hotfix, but unfortunately v15 is now in security phase so only receiving security issue updates.

Are you able to upgrade to v16?

[nickfrederiksen]

Hi,

We are in talks with the client to begin an upgrade process. A fix for this would be another argument for that.

Thanks anyway!

[mattbrailsford]

16.4.0 has now been released which contains the fix for this issue.