Umbraco.Forms 12.2: Cannot insert duplicate key row in object 'dbo.umbracoRelation' with unique index 'IX_umbracoRelation_parentChildType'

[stephen-sherman]

Bug summary

An error is thrown when publishing pages that have previously had Umbraco Forms present, or currently have multiple Umbraco Forms present.

Specifics

• New project.
• Umbraco 12.2.3.
• Umbraco Forms 12.2.
• Forms are added to the page using the Form Picker data type, within a Block Grid data type.
• The issue does not occur using Umbraco Forms 12.1.2.
• Full error message and screenshot attached.

Steps to reproduce

Scenario 1:

1. Add a form to a page.
2. Publish the page.
3. Add a second copy of the same form to the same page.
4. Publish the page.

Scenario 2:

1. Add a form to a page.
2. Publish the page.
3. Remove the form from the page.
4. Publish the page.
5. Re-add the form to the page.
6. Publish the page.

Expected result

Forms can be added and removed from pages over time as desired.

Actual result

The following error is thrown on publish:

Received an error from the server An error occurred Cannot insert duplicate key row in object 'dbo.umbracoRelation' with unique index 'IX_umbracoRelation_parentChildType'. The duplicate key value is (1090, 1089, 6). The statement has been terminated.

Exception Details Microsoft.Data.SqlClient.SqlException, Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5: Cannot insert duplicate key row in object 'dbo.umbracoRelation' with unique index 'IX_umbracoRelation_parentChildType'. The duplicate key value is (1090, 1089, 6). The statement has been terminated. Stacktrace at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) at NPoco.SqlServer.SqlBulkCopyHelper.BulkInsert[T](IDatabase db, IEnumerable`1 list, SqlBulkCopyOptions sqlBulkCopyOptions, InsertBulkOptions insertBulkOptions) at NPoco.SqlServer.SqlBulkCopyHelper.BulkInsert[T](IDatabase db, IEnumerable`1 list, InsertBulkOptions insertBulkOptions) at NPoco.DatabaseTypes.SqlServerDatabaseType.InsertBulk[T](IDatabase db, IEnumerable`1 pocos, InsertBulkOptions options) at NPoco.Database.InsertBulk[T](IEnumerable`1 pocos, InsertBulkOptions options) at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.RelationRepository.SaveBulk(IEnumerable`1 relations) at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.ContentRepositoryBase`3.PersistRelations(TEntity entity) at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.DocumentRepository.PersistUpdatedItem(IContent entity) at Umbraco.Cms.Core.Cache.DefaultRepositoryCachePolicy`2.Update(TEntity entity, Action`1 persistUpdated) at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.EntityRepositoryBase`2.Save(TEntity entity) at Umbraco.Cms.Core.Services.ContentService.<>c__DisplayClass66_0.<CommitDocumentChangesInternal>g__SaveDocument|2(IContent c) at Umbraco.Cms.Core.Services.ContentService.CommitDocumentChangesInternal(ICoreScope scope, IContent content, EventMessages eventMessages, IReadOnlyCollection`1 allLangs, IDictionary`2 notificationState, Int32 userId, Boolean branchOne, Boolean branchRoot) at Umbraco.Cms.Core.Services.ContentService.SaveAndPublish(IContent content, String culture, Int32 userId) at Umbraco.Cms.Web.BackOffice.Controllers.ContentController.PublishInternal(ContentItemSave contentItem, String defaultCulture, String cultureForInvariantErrors, Boolean& wasCancelled, String[]& successfulCultures) at Umbraco.Cms.Web.BackOffice.Controllers.ContentController.PostSaveInternal[TVariant](ContentItemSave contentItem, Func`3 saveMethod, Func`2 mapToDisplay) at Umbraco.Cms.Web.BackOffice.Controllers.ContentController.PostSave(ContentItemSave contentItem) at lambda_method927(Closure, Object) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

[AndyButland]

Thanks for reporting this... I've been able to replicate, and seems really it has exposed an issue in how the CMS handles relations introduced by third-party packages, when these references aren't direct properties of the page, but held indirectly via the use of the block list/block grid. I've created a PR for the CMS that will resolve the issue, but there will likely be other considerations from the team of how they want to handle it.

Due to how relation handling has already been refactored for Umbraco 13, we don't expect it to be an issue there.

The issue stems from the CMS first deleting all "automatic relations" for a page, and then re-adding then. It doesn't pick up the form relations in the first step when they are only referenced via the block grid. But does in the second. As such it then tries to add a relation that's already there, which leads to the exception you see.

So a rather clumsy but still effective workaround is to add a form picker property directly on the document type in question. It doesn't have to be used. Just the fact of it being there will be enough that the CMS then considers the form relations in this initial delete relations operation that runs after a page of the type is saved.

Meanwhile we'll see how to progress it with the CMS team via the linked PR,

[bjarnef]

@AndyButland another workaround may be to hook into ContentSavingNotification, which we used for a custom property editor before CMS allowed to implement GetAutomaticRelationTypesAliases() in custom property editors.

builder.AddNotificationHandler<ContentSavingNotification, ContentPageSavingNotificationHandler>();

// TODO: This can be removed in v10.4.0 / v11.1.0 where references for custom relation types are deleted
// See https://github.com/umbraco/Umbraco-CMS/pull/13389 and https://github.com/umbraco/Umbraco-CMS/pull/13470

public class ContentPageSavingNotificationHandler : INotificationHandler<ContentSavingNotification>
{
    private readonly IRelationService _relationService;

    public ContentPageSavingNotificationHandler(
        IRelationService relationService)
    {
        _relationService = relationService;
    }

    public void Handle(ContentSavingNotification notification)
    {
        var relationType = _relationService.GetRelationTypeByAlias("myRelationAlias");
        if (relationType == null)
            return;

        foreach (var node in notification.SavedEntities)
        {
            if (node.ContentType.Alias.Equals(ContentPage.ModelTypeAlias) || node.ContentType.Alias.Equals(AnotherContentPage.ModelTypeAlias))
            {
                // Umbraco currently doesn't remove relations using custom relation type, when using UmbracoEntityReference in property editor,
                // so we need to remove these before saved.

                var relations = _relationService.GetByParentId(node.Id, "myRelationAlias");

                if (relations != null)
                {
                    foreach (var relation in relations)
                    {
                        _relationService.Delete(relation);
                    }
                }
            }
        }
    }
}

[AndyButland]

We're picking up this issue to see if it can be solved in the CMS, but in the meantime, as of the next patch release, we'll add a configuration option to allow for disabling of tracking form relations. It will be applied as follows:

"Umbraco": {
  "Forms": {,
    "Options": {
      "DisableRelationTracking": true
    },
  }
}

[PBHansen]

Our project started as Umbraco 12, but upgraded to Umbraco 13 to fix this error as per this comment:

Due to how relation handling has already been refactored for Umbraco 13, we don't expect it to be an issue there.

and while we can save and remove forms again, I see the following error in logs and relations are not being added:

Is this a known issue? Not sure if we should revert to umbraco 12 and use the more hacky approach to fix it.

[AndyButland]

Yes, this is what I had expected. My understanding is that some independent refactoring was done on the "automatic relations" for Umbraco 13, which would mean the major issue of the exception being thrown on saving content wouldn't occur there. But the relations using custom relation types from packages within block list wouldn't be tracked at all. So still not right, just better in the sense of not blocking content creation.

[PBHansen]

Ah alright, want me to create a new issue then?

[AndyButland]

Sure, if you could please that would be useful so we have somewhere to track it and you can see progress. I've already started discussions with the CMS team about how we could resolve this.

[richarth]

Hi @AndyButland,

Do you know when the next patch release is likely to be please?

[AndyButland]

We're looking at the 16th January for the next patches for Forms.

[richarth]

Thanks Andy.

[teeto]

We're looking at the 16th January for the next patches for Forms.

this patch will be released as v12.2.3 of Umbraco Forms? when will be available? https://www.nuget.org/packages/Umbraco.Forms#versions-body-tab

[AndyButland]

The fix for this issue needs to be made in the CMS, so it's an upcoming patch release of CMS 12 that you'll need to update to to fully resolve. I don't have a date for it yet I'm afraid.

In Forms 12,2,2 however we have included a setting to allow you to disable relation tracking with Forms - see the release notes for details - and there are also the workarounds described in this issue available.

[teeto]

Thanks @AndyButland i already did the workaround in the config, but im waiting for the patch, i understood it was for UF, so i will be watching for Umbracon next v12 patch

[ProNotion]

Is there an update when a v12 fix for this will be out, please?

[AndyButland]

The CMS update to resolve the issue was in 12.3.8 (which I'm getting from the PR linked above).

[ProNotion]

The CMS update to resolve the issue was in 12.3.8 (which I'm getting from the PR linked above).

Oh, that's not good then as we are on 12.3.10 and are still getting the issue unless we set DisableRelationTracking to false. We are also using Forms v12.2.4

[AndyButland]

Hmm... then unfortunately at this point I'm afraid you will need to keep form relation tracking off, or upgrade to 13. As 12 was a short-term-support version, it's now end of life and won't be receiving further updates.

For that reason I'll also close this issue now.

[bjarnef]

@ProNotion we had the issue as well on a project on v12.3.10, but resolved in adding a composition with a Form picker property to the page types allowing to insert forms, e.g. into Block Grid.

but you could also try this workaround: https://github.com/umbraco/Umbraco.Forms.Issues/issues/1129#issuecomment-1862594134