v8: Error on Saving Document Type with new Group + Property (A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = PK_cmsPropertyTypeGroup ])

[hfloyd]

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

8.17.0

Bug summary

I recently updated an Umbraco Cloud v8 Project to v8.17.0 (where the new "Tabs" UI was introduced). This is an existing project, and I am NOT using ANY Tabs - just the original Groups.

Working on my local copy of the site, I opened a Document Type which currently had no compositions, groups, or properties on it, and attempted to add a single Group with a single Property. On Save, I received the error: "A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = PK_cmsPropertyTypeGroup ]"

Specifics

Error Details

A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = PK_cmsPropertyTypeGroup ]

Exception Details
System.Data.SqlServerCe.SqlCeException: A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = PK_cmsPropertyTypeGroup ]
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.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) in D:\a\1\s\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 172
   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.Core.Persistence.Repositories.Implement.ContentTypeRepositoryBase`1.PersistUpdatedBaseContentType(IContentTypeComposition entity) in D:\a\1\s\src\Umbraco.Core\Persistence\Repositories\Implement\ContentTypeRepositoryBase.cs:line 380
   at Umbraco.Core.Persistence.Repositories.Implement.ContentTypeRepository.PersistUpdatedItem(IContentType entity) in D:\a\1\s\src\Umbraco.Core\Persistence\Repositories\Implement\ContentTypeRepository.cs:line 291
   at Umbraco.Core.Cache.FullDataSetRepositoryCachePolicy`2.Update(TEntity entity, Action`1 persistUpdated) in D:\a\1\s\src\Umbraco.Core\Cache\FullDataSetRepositoryCachePolicy.cs:line 88
   at Umbraco.Core.Services.Implement.ContentTypeServiceBase`3.Save(TItem item, Int32 userId) in D:\a\1\s\src\Umbraco.Core\Services\Implement\ContentTypeServiceBaseOfTRepositoryTItemTService.cs:line 430
   at Umbraco.Web.Editors.ContentTypeControllerBase`1.PerformPostSave[TContentTypeDisplay,TContentTypeSave,TPropertyType](TContentTypeSave contentTypeSave, Func`2 getContentType, Action`1 saveContentType, Action`1 beforeCreateNew) in D:\a\1\s\src\Umbraco.Web\Editors\ContentTypeControllerBase.cs:line 297
   at Umbraco.Web.Editors.ContentTypeController.PostSave(DocumentTypeSave contentTypeSave) in D:\a\1\s\src\Umbraco.Web\Editors\ContentTypeController.cs:line 298
   at lambda_method(Closure , Object , Object[] )
   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters)
   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()

Versions

Umbraco Id
Umbraco 8.17.0
Deploy 4.3.0.0
Forms 8.7.6

Steps to reproduce

NOTE This might be an issue limited to an upgraded install with existing Document Types.

1. Create a new Document Type without any compositions, groups, tabs, or properties. Save it.
2. Open the new type and attempt to add a Group with properties. Attempt to Save. View Error.

Expected result / actual result

I would expect the new group and property to save without any errors.

Possibly Related Issues

#11221

[hfloyd]

Interestingly, I was able to create the group & property on my Cloud Development environment, so possibly an SQLCE-only issue?

However, it doesn't fix the problem since when I then pull the change to my local environment and attempt a local "Schema Deployment From Data Files", it fails with a similar error:

System.Data.SqlServerCe.SqlCeException (0x80004005): A duplicate value cannot be inserted into a unique index. [ Table name = cmsPropertyTypeGroup,Constraint name = PK_cmsPropertyTypeGroup ]
   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.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) in D:\a\1\s\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 172
   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.Core.Persistence.Repositories.Implement.ContentTypeRepositoryBase`1.PersistUpdatedBaseContentType(IContentTypeComposition entity) in D:\a\1\s\src\Umbraco.Core\Persistence\Repositories\Implement\ContentTypeRepositoryBase.cs:line 380
   at Umbraco.Core.Persistence.Repositories.Implement.ContentTypeRepository.PersistUpdatedItem(IContentType entity) in D:\a\1\s\src\Umbraco.Core\Persistence\Repositories\Implement\ContentTypeRepository.cs:line 291
   at Umbraco.Core.Cache.FullDataSetRepositoryCachePolicy`2.Update(TEntity entity, Action`1 persistUpdated) in D:\a\1\s\src\Umbraco.Core\Cache\FullDataSetRepositoryCachePolicy.cs:line 88
   at Umbraco.Core.Services.Implement.ContentTypeServiceBase`3.Save(TItem item, Int32 userId) in D:\a\1\s\src\Umbraco.Core\Services\Implement\ContentTypeServiceBaseOfTRepositoryTItemTService.cs:line 430
   at Umbraco.Deploy.Connectors.ServiceConnectors.ContentTypeConnector.ContentTypeConnectorBase`2.Process(DeployState state, IDeployContext context, Int32 pass)
   at Umbraco.Deploy.Environments.CurrentEnvironment.ProcessArtifacts(IEnumerable`1 arts, IDeployContext context, CancellationToken token)

[hfloyd]

I was able to do a fresh local initialize of the database and was able to get the changes I made on the Development environment. Also, I tested adding a new group and property to an otherwise empty Doctype, and was able to do so successfully. All I can figure is that during the original update something didn't happen in the database internals which should have...

[tompipe]

I've just encountered this very issue, the following info may assist with a fix!

After doing an upgrade, I couldn't add any new tabs to any doctypes.

When I ran this SQL query against my SQL CE DB

SELECT AUTOINC_NEXT 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'cmsPropertyTypeGroup'
AND COLUMN_NAME = 'id'

I found that it returned a value of 92, but when I checked the cmsPropertyTypeGroup table, there was already an item using id 92 (and several entries subsequently)

I couldn't find a way to update the AUTOINC_NEXT value, so I ended up going through each of the doctypes which were using tabs with id's of 92 and above, and moved the properties off the suspect tabs onto another tab, and then resaved the doctype, repeating this process until the last entry in the cmsPropertyTypeGroup table was 91. Then I reversed the process, adding a new tab and moving the property back.

And then I could add tabs again. 😄

[keithlfs]

I couldn't find a way to update the AUTOINC_NEXT value...

ALTER TABLE cmsPropertyTypeGroup ALTER COLUMN id IDENTITY (250, 2); did the trick for me, setting the next auto-generated ID to 250.

Thanks for your info above, helped me solve my issue.