AI OnAI Off
Primary key violations
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?
May 16, 2019 12:21
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.
May 16, 2019 12:29
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)
May 16, 2019 12:36
@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.
May 16, 2019 12:38
@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
May 16, 2019 12:55
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
May 16, 2019 14:48
The setting was changed to https://world.episerver.com/documentation/Items/Developers-Guide/Episerver-CMS/8/Configuration/Configuring-episerverdataStore/ - sorry about that.
I'll update my blogpost
May 16, 2019 14:52
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
May 16, 2019 14:58
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 :)
May 16, 2019 15:09
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
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.
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