Primary key violations

Vote:
 

Hi,

we've migrated an EPiserver CMS database to an Azure environment. When editing content the publish button doesn't appear. I've checked the logs and there are several primary key voilations in the logs.

e.g. Violation of PRIMARY KEY constraint 'PK_tblWorkContent'. Cannot insert duplicate key in object 'dbo.tblWorkContent'. The duplicate key value is (15305079).

We've tried fixing the issue by creating another backup and restore, but still getting the same error. Does anyone know what could be the reason for the primary key violations?

Thanks,

Danie

#203997
Edited, May 16, 2019 11:39
Vote:
 

Can you post the full log ?

#204000
May 16, 2019 12:07
Vote:
 

How did you do the migration?

#204002
May 16, 2019 12:18
Vote:
 

I work alongside Daniel on this project.

The following applications have been tried:

  • Idera SQL Schema Compare and Idera SQL Data Compare
  • Microsoft Data Migration Assistant

We have disabled TDE and Automatic Tuning but there has been no change in the error.

@Quan Mai

Please, can you be more specific as to which logs?

#204003
May 16, 2019 12:21
Vote:
 

Is there a specific application which Episerver (or forum members) recommend and have successfully used in the past with Azure migration?

If it is of any relevance, the source/legacy database details are as follows:

  • Database Server: Microsoft SQL Server 2014 Standard
  • Compatibility Level: 100
  • Operating System: Microsoft Windows Server 2008 R2 (VMware)
  • Collation: Finnish_Swedish_CI_AS.
#204004
May 16, 2019 12:29
Vote:
 

Hi,

here is the full stack:

2019-05-16 08:38:47,637 [19] [03e15794-1e03-42d2-94d2-8a8953975079] ERROR EPiServer.Global: Unhandled exception in ASP.NET
System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_tblWorkContent'. Cannot insert duplicate key in object 'dbo.tblWorkContent'. The duplicate key value is (15305048).
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.DataAccess.Internal.ContentSaveDB.CreateNewVersion(IDatabaseExecutor executor, IContent content, Nullable`1 baseVersion, String currentUser, ContentSaveCommand saveCommand)
at EPiServer.DataAccess.Internal.ContentSaveDB.<>c__DisplayClass24_0.<SaveInternal>b__0()
at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass32_0.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass33_0`1.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
at EPiServer.DataAccess.Internal.ContentSaveDB.SaveInternal(IContent content, SaveOptions options)
at EPiServer.DataAccess.Internal.ContentSaveDB.Save(IContent content, SaveAction action, String currentUser)
at EPiServer.Core.Internal.DefaultContentProviderDatabase.Save(IContent content, SaveAction action, String currentUser)
at EPiServer.Core.Internal.DefaultContentProvider.Save(IContent content, SaveAction action)
at EPiServer.Core.Internal.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
at EPiServer.Cms.Shell.Service.Internal.ContentService.Save(IContent content, SaveAction saveAction, AccessLevel accessLevel)
at EPiServer.Cms.Shell.Service.Internal.ContentService.CreateDraft(ContentReference contentLink, Boolean setAsCommonDraft)
at EPiServer.Cms.Shell.UI.Rest.Internal.ContentVersionStore.Post(CreateContentVersionViewModel entity)
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.<BeginInvokeAction>b__1c()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult)
at EPiServer.Shell.Services.Rest.RestControllerBase.EndExecute(IAsyncResult asyncResult)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.<>c__DisplayClass285_0.<ExecuteStepImpl>b__0()
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

#204005
May 16, 2019 12:36
Vote:
 

@Geogre: a rule of thumb is to always post the full log/stack trace instead of only error message :) In the context of this thread I think it is quite self-explanatory for what I mean, but sorry if it is unclear to you.

#204006
May 16, 2019 12:38
Vote:
 

@Daniel: I think the primary key violation is just a by-product. I would suggest to try to disable retry policy https://vimvq1987.com/understand-retry-policies-episerver-cms/ to see if the actual error is something else 

#204009
May 16, 2019 12:55
Vote:
 

Hi Quan,

thanks for your suggestion. 

I've added the following in the web.config

1. <section name="episerver.dataStore" type="EPiServer.Data.Configuration.EPiServerDataStoreSection, EPiServer.Framework.AspNet" />

and

2. 

<episerver.dataStore>
<siteDataSettings>
<add siteId="B89EE14B-B42C-45F2-A98D-67DD116C0DF3" connectionStringName="EPiServerDB" retries="0" />
</siteDataSettings>
</episerver.dataStore>

When I reload the site, it errors with Unrecognized element 'siteDataSettings'.

Do you know if my section in 1 is correct?

Thanks,

Danie

#204036
May 16, 2019 14:48
Vote:
 
#204037
May 16, 2019 14:52
Vote:
 

Thanks for your quick reply! I've changed my config to:

<episerver.dataStore>
<dataSettings connectionStringName="EPiServerDB" retries="0" />
</episerver.dataStore>

After trying to change the content I'm still getting the same primary key issue:  

2019-05-16 12:54:24,625 [83] [fb6167d3-82e6-42c3-af22-46e3ffa738f6] ERROR EPiServer.Global: Unhandled exception in ASP.NET
System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_tblWorkProperty'. Cannot insert duplicate key in object 'dbo.tblWorkContentProperty'. The duplicate key value is (54684575).
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.DataAccess.Internal.ContentSaveDB.CreateNewVersion(IDatabaseExecutor executor, IContent content, Nullable`1 baseVersion, String currentUser, ContentSaveCommand saveCommand)
at EPiServer.DataAccess.Internal.ContentSaveDB.<>c__DisplayClass24_0.<SaveInternal>b__0()
at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass32_0.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass33_0`1.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
at EPiServer.DataAccess.Internal.ContentSaveDB.SaveInternal(IContent content, SaveOptions options)
at EPiServer.DataAccess.Internal.ContentSaveDB.Save(IContent content, SaveAction action, String currentUser)
at EPiServer.Core.Internal.DefaultContentProviderDatabase.Save(IContent content, SaveAction action, String currentUser)
at EPiServer.Core.Internal.DefaultContentProvider.Save(IContent content, SaveAction action)
at EPiServer.Core.Internal.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
at EPiServer.Cms.Shell.Service.Internal.ContentService.Save(IContent content, SaveAction saveAction, AccessLevel accessLevel)
at EPiServer.Cms.Shell.Service.Internal.ContentService.CreateDraft(ContentReference contentLink, Boolean setAsCommonDraft)
at EPiServer.Cms.Shell.UI.Rest.Internal.ContentVersionStore.Post(CreateContentVersionViewModel entity)
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.<BeginInvokeAction>b__1c()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult)
at EPiServer.Shell.Services.Rest.RestControllerBase.EndExecute(IAsyncResult asyncResult)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.<>c__DisplayClass285_0.<ExecuteStepImpl>b__0()
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
ClientConnectionId:bca036c9-0a3b-490e-8a54-0ff1e5e7b09f
Error Number:2627,State:1,Class:14
ClientConnectionId before routing:7badb159-ebe7-4fc0-a838-b3c983e57dc9
System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_tblWorkProperty'. Cannot insert duplicate key in object 'dbo.tblWorkContentProperty'. The duplicate key value is (54684575).
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.DataAccess.Internal.ContentSaveDB.CreateNewVersion(IDatabaseExecutor executor, IContent content, Nullable`1 baseVersion, String currentUser, ContentSaveCommand saveCommand)
at EPiServer.DataAccess.Internal.ContentSaveDB.<>c__DisplayClass24_0.<SaveInternal>b__0()
at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass32_0.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass33_0`1.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
at EPiServer.DataAccess.Internal.ContentSaveDB.SaveInternal(IContent content, SaveOptions options)
at EPiServer.DataAccess.Internal.ContentSaveDB.Save(IContent content, SaveAction action, String currentUser)
at EPiServer.Core.Internal.DefaultContentProviderDatabase.Save(IContent content, SaveAction action, String currentUser)
at EPiServer.Core.Internal.DefaultContentProvider.Save(IContent content, SaveAction action)
at EPiServer.Core.Internal.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
at EPiServer.Cms.Shell.Service.Internal.ContentService.Save(IContent content, SaveAction saveAction, AccessLevel accessLevel)
at EPiServer.Cms.Shell.Service.Internal.ContentService.CreateDraft(ContentReference contentLink, Boolean setAsCommonDraft)
at EPiServer.Cms.Shell.UI.Rest.Internal.ContentVersionStore.Post(CreateContentVersionViewModel entity)
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.<BeginInvokeAction>b__1c()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult)
at EPiServer.Shell.Services.Rest.RestControllerBase.EndExecute(IAsyncResult asyncResult)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.<>c__DisplayClass285_0.<ExecuteStepImpl>b__0()
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
ClientConnectionId:bca036c9-0a3b-490e-8a54-0ff1e5e7b09f
Error Number:2627,State:1,Class:14
ClientConnectionId before routing:7badb159-ebe7-4fc0-a838-b3c983e57dc9

#204038
May 16, 2019 14:58
Vote:
 

Thanks.

Now I'm guessing here, but it sounds like the identity column in the table tblWorkContentProperty is faulty, which makes sense because you migrated the data. I would suggest to try this to reset the seed:

DBCC CHECKIDENT (tblWorkContentProperty , RESEED, 0);
DBCC CHECKIDENT (tblWorkContentProperty , RESEED);

As always, take backups and proceed with caution :)
#204039
May 16, 2019 15:09
Vote:
 

Hi Quan,

you are correct, the "current identity value" was incorrect. What happend was that the schema was first migrated (including the current indentity value) and then the data was synced.  When the data was synced, the pkid value was already increment because there were content changes in the source database. Instead of syncing the database we've made an offline backup and restore which fixed the issue. 

Thanks for your help!

Danie

#204055
May 17, 2019 10:27
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.