v9: SQL error when trying to create a new property on a tab that is inherited from a Composition (SQL CE and LocalDB)

[BarryFogarty]

Which exact Umbraco version are you using? For example: 8.13.1 - don't just write v8

9.0.0

Bug summary

A SQL error is generated when trying to create a new property on a tab that is inherited from a Composition (using SQL CE). The error is: A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = IX_cmsPropertyTypeGroupUniqueID ]

I believe this is caused by a rename of the tab in the Composition, and/or on the Document Type prior to the Save attempt.

Specifics

Received an error from the server

An error occurred
A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = IX_cmsPropertyTypeGroupUniqueID ]

Exception Details
System.Data.SqlServerCe.SqlCeException, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91: A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = IX_cmsPropertyTypeGroupUniqueID ]
Stacktrace
at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
   at NPoco.DatabaseTypes.SqlServerCEDatabaseType.ExecuteInsert[T](Database db, DbCommand cmd, String primaryKeyName, Boolean useOutputClause, T poco, Object[] args)
   at NPoco.Database.InsertImp[T](PocoData pocoData, String tableName, String primaryKeyName, Boolean autoIncrement, T poco)
   at NPoco.Database.Insert[T](String tableName, String primaryKeyName, Boolean autoIncrement, T poco)
   at NPoco.Database.Insert[T](T poco)
   at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.ContentTypeRepositoryBase`1.PersistUpdatedBaseContentType(IContentTypeComposition entity)
   at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.ContentTypeRepository.PersistUpdatedItem(IContentType entity)
   at Umbraco.Cms.Core.Cache.FullDataSetRepositoryCachePolicy`2.Update(TEntity entity, Action`1 persistUpdated)
   at Umbraco.Cms.Core.Services.Implement.ContentTypeServiceBase`2.Save(TItem item, Int32 userId)
   at Umbraco.Cms.Web.BackOffice.Controllers.ContentTypeController.<PostSave>b__30_1(IContentType type)
   at Umbraco.Cms.Web.BackOffice.Controllers.ContentTypeControllerBase`1.PerformPostSave[TContentTypeDisplay,TContentTypeSave,TPropertyType](TContentTypeSave contentTypeSave, Func`2 getContentType, Action`1 saveContentType, Action`1 beforeCreateNew)
   at Umbraco.Cms.Web.BackOffice.Controllers.ContentTypeController.PostSave(DocumentTypeSave contentTypeSave)
   at lambda_method1812(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   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|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

Steps to reproduce EDITED

I believe this only happened when I renamed a tab in the Composition, and then attempted to move the property in the target document type into this inherited tab. So, steps:

1. Create a Composition with a tab and a property
2. Create a Document Type and inherit the Composition
3. Create another property in the inherited tab
   4. Rename the tab in the Composition
   5. Rename the tab in the Document Type (note they are not merged)
   6. Move the Document Type property to the renamed inherited tab
4. Save Document Type

It is not possible to save any properties in tabs that are inherited from compositions.

Expected result / actual result

Umbraco should be able to save the Document Type after adding properties on tabs inherited that are inherited from compositions. Instead, a server error dialog appears with the above 'duplicate index in IX_cmsPropertyTypeGroupUniqueID' exception message.

[BarryFogarty]

Just to follow up quickly, this seems to be a showstopper for SQL CE, as it happens without any renaming shenanigans - simply by trying to add a local document type property to an composition-inherited tab. I have repro'd this on a fresh install, without doing steps 4,5 or 6 above i.e. I have updated my original post accordingly.

1. Create a Composition with a tab and a property
2. Create a Document Type and inherit the Composition
3. Create another property in the inherited tab
4. Save Document Type

The SQL error regarding a duplicate index in IX_cmsPropertyTypeGroupUniqueID is triggered.

[BarryFogarty]

I have repro'd this using LocalDB as well.

[nul800sebastiaan]

Thanks @BarryFogarty - likely a v8 problem too with tabs. Will do some testing to confirm.

[ronaldbarendse]

I've gotten this exception as well this morning while testing things for issue #11202, so I can confirm it's indeed a bug.

Looks like the back-office doesn't generate a new GUID for the new local group/tab and tries to save it using the key inherited from the composition, causing the duplicate key exception.

This issue wasn't surfaced in 8.17 RC1, as the keys weren't posted back to the server and therefore re-created on every save and thus unique (which is a bug that's fixed in PR #11121).

[bjarnef]

I noticed this as well when migrating a v8 Cloud project to v9 where I had a base composition to hold Background Color and Text Color properties:
umbraco/Umbraco.Deploy.Issues#72

[AaronSadlerUK]

I've just encountered this issue using normal SQL Server when trying to add a property to a composition in v9

Cannot insert duplicate key row in object 'dbo.cmsPropertyTypeGroup' with unique index 'IX_cmsPropertyTypeGroupUniqueID'. The duplicate key value is (527651aa-5457-45a9-ad75-070c14fbc2ad). The statement has been terminated.

[AaronSadlerUK]

@ronaldbarendse Is there a work around for this?

The reason being I can't continue with my build until it works 🙈

[ronaldbarendse]

@AaronSadlerUK You can temporary patch the changed files from PR #11231 (please report back, also if it worked correctly) or:

1. Create a new group with the same name below the inherited one;
2. Manually update the alias in the cmsPropertyTypeGroup table: the new one will probably have a number postfix, which you can remove;
3. Restart/recycle the application;
4. Reload the content type editor/back-office: the composition and local group should now be merged based on the alias.

[nul800sebastiaan]

Cherry picked for 8.17.0 in 213d8c0 - this will also be merged up to v9 for a 9.0.1 release.

[nul800sebastiaan]

Cherry picked for 9.0.1 in 5a3efa7

[AaronSadlerUK]

@nul800sebastiaan Any ideas when 9.0.1 is out, I have been blocked since 9.0.0 release due to this issue 😕

I also couldn't figure out how to regenerate the minified files to include @ronaldbarendse fix

[smarshallsay]

I am still experiencing this in 9.0.1, although I can confirm that @ronaldbarendse workaround does work for me.
I upgraded from beta / RC versions of 9.