Violation of PRIMARY KEY constraint 'PK_EventRelatedDocument'. Cannot insert duplicate key in object 'dbo.EventRelatedDocument'. The duplicate key value is (78614). The statement has been terminated

Shyam · 18 August 2017 08:13

Hi Team,

I suddenly getting database error while deploying package to octopus

[Step 3/3] Octopus Deploy (1s)
[13:30:25][Octopus Deploy] Running command:   octo.exe create-release --server http://192.168.6.21:1083/ --apikey SECRET --project ProjectName --enableservicemessages --channel Mobile --deployto BE
[13:30:25][Octopus Deploy] Creating Octopus Deploy release
[13:30:25][Octopus Deploy] Octopus Deploy Command Line Tool, version 4.11.0
[13:30:25][Octopus Deploy] 
[13:30:25][Octopus Deploy] Build environment is Handshaking with Octopus server: http://192.168.6.21:1083/
[13:30:25][Octopus Deploy] Handshake successful. Octopus version: 3.12.6; API version: 3.0.0
[13:30:25][Octopus Deploy] Authenticated as: admin <> 
[13:30:25][Octopus Deploy] This Octopus Server supports channels
[13:30:25][Octopus Deploy] Finding project: PackageName
[13:30:25][Octopus Deploy] Building release plan for channel 'Mobile'...
[13:30:25][Octopus Deploy] Finding deployment process...
[13:30:25][Octopus Deploy] Finding release template...
[13:30:26][Octopus Deploy] The package version for some steps was not specified. Going to try and resolve those automatically...
[13:30:26][Octopus Deploy] Finding latest package for step: Deploy Mobile App
[13:30:26][Octopus Deploy] Selected 'PackageName' version '1.0.137' for 'Deploy Mobile App'
[13:30:26][Octopus Deploy] Finding latest package for step: Deploy Mobile App - Live
[13:30:26][Octopus Deploy] Selected 'PackageName' version '1.0.137' for 'Deploy Mobile App - Live'
[13:30:26][Octopus Deploy] Using version number from release template: 1.0.4396
[13:30:26][Octopus Deploy] Release plan for PackageName 1.0.4396
[13:30:26][Octopus Deploy] Channel: 'Mobile'
[13:30:26][Octopus Deploy]   #   Name                       Version   Source             Version rules      
[13:30:26][Octopus Deploy]   --- -------------------------- --------- ------------------ -------------------
[13:30:26][Octopus Deploy]   1   Deploy Mobile App          1.0.137   Latest available   Allow any version  
[13:30:26][Octopus Deploy]   2   Deploy Mobile App - Live   1.0.137   Latest available   Allow any version  
[13:30:26][Octopus Deploy] 
[13:30:26][Octopus Deploy] Creating release...
[13:30:26][Octopus Deploy] Octopus Server returned an error: Error while executing SQL command in transaction 'http://192.168.6.21:1083/api/releases?ignoreChannelRules=False 41c4022e0d0d49fab8fb4ae0dc13b57a': Violation of PRIMARY KEY constraint 'PK_EventRelatedDocument'. Cannot insert duplicate key in object 'dbo.EventRelatedDocument'. The duplicate key value is (78614).
[13:30:26][Octopus Deploy] The statement has been terminated.
[13:30:26][Octopus Deploy] The command being executed was:
[13:30:26][Octopus Deploy] INSERT INTO dbo.[EventRelatedDocument]  (EventId, RelatedDocumentId) values (@0__EventId, @0__RelatedDocumentId), (@1__EventId, @1__RelatedDocumentId)
[13:30:26][Octopus Deploy] SQL Error 2627 - Violation of PRIMARY KEY constraint 'PK_EventRelatedDocument'. Cannot insert duplicate key in object 'dbo.EventRelatedDocument'. The duplicate key value is (78614).
[13:30:26][Octopus Deploy] The statement has been terminated.
[13:30:26][Octopus Deploy] Server exception: 
[13:30:26][Octopus Deploy] Error while executing SQL command in transaction 'http://192.168.6.21:1083/api/releases?ignoreChannelRules=False 41c4022e0d0d49fab8fb4ae0dc13b57a': Violation of PRIMARY KEY constraint 'PK_EventRelatedDocument'. Cannot insert duplicate key in object 'dbo.EventRelatedDocument'. The duplicate key value is (78614).
[13:30:26][Octopus Deploy] The statement has been terminated.
[13:30:26][Octopus Deploy] The command being executed was:
[13:30:26][Octopus Deploy] INSERT INTO dbo.[EventRelatedDocument]  (EventId, RelatedDocumentId) values (@0__EventId, @0__RelatedDocumentId), (@1__EventId, @1__RelatedDocumentId)
[13:30:26][Octopus Deploy] System.Exception
[13:30:26][Octopus Deploy]    at Nevermore.RelationalTransaction.InsertMany[TDocument](String tableName, IReadOnlyCollection`1 instances, Boolean includeDefaultModelColumns, String tableHint)
[13:30:26][Octopus Deploy]    at Octopus.Core.RelationalStorage.RelatedDocumentStore.PopulateEventRelatedDocuments(IRelationalTransaction transaction, Event event) in RelatedDocumentStore.cs:line 70
[13:30:26][Octopus Deploy]    at Nevermore.RelationalTransaction.Insert[TDocument](String tableName, TDocument instance, String customAssignedId, String tableHint, Nullable`1 commandTimeoutSeconds)
[13:30:26][Octopus Deploy]    at Octopus.Server.EventStore.Store[TDocument](IRelationalTransaction session, Event event, TDocument model, IOctopusPrincipal currentPrincipal) in EventStore.cs:line 71
[13:30:26][Octopus Deploy]    at Octopus.Server.Web.Infrastructure.Api.CreateResponseDescriptor`2.Responder.Execute() in CreateResponseDescriptor.cs:line 85
[13:30:26][Octopus Deploy]    at Octopus.Server.Web.Infrastructure.Api.Responder`1.Respond(TDescriptor options, NancyContext context) in Responder.cs:line 142
[13:30:26][Octopus Deploy]    at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
[13:30:26][Octopus Deploy]    at Octopus.Server.Web.Api.OctopusRestApiModule.<>c__DisplayClass0_0.<.ctor>b__0(Object o) in OctopusRestApiModule.cs:line 48
[13:30:26][Octopus Deploy]    at Nancy.Routing.Route.<>c__DisplayClass4.<Wrap>b__3(Object parameters, CancellationToken context)
[13:30:26][Octopus Deploy] 
[13:30:26][Octopus Deploy] --Inner Exception--
[13:30:26][Octopus Deploy] SQL Error 2627 - Violation of PRIMARY KEY constraint 'PK_EventRelatedDocument'. Cannot insert duplicate key in object 'dbo.EventRelatedDocument'. The duplicate key value is (78614).
[13:30:26][Octopus Deploy] The statement has been terminated.
[13:30:26][Octopus Deploy] System.Data.SqlClient.SqlException
[13:30:26][Octopus Deploy]    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
[13:30:26][Octopus Deploy]    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
[13:30:26][Octopus Deploy]    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
[13:30:26][Octopus Deploy]    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
[13:30:26][Octopus Deploy]    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)
[13:30:26][Octopus Deploy]    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)
[13:30:26][Octopus Deploy]    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
[13:30:26][Octopus Deploy]    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
[13:30:26][Octopus Deploy]    at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass2_0.<ExecuteNonQueryWithRetry>b__0()
[13:30:26][Octopus Deploy]    at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func`1 func)
[13:30:26][Octopus Deploy]    at Nevermore.RelationalTransaction.InsertMany[TDocument](String tableName, IReadOnlyCollection`1 instances, Boolean includeDefaultModelColumns, String tableHint)
[13:30:26][Octopus Deploy] -----------------------
[13:30:26][Octopus Deploy] 
[13:30:26][Octopus Deploy] Error from Octopus server (HTTP 500 InternalServerError)
[13:30:26][Octopus Deploy] Exit code: -7
[13:30:26][Octopus Deploy] Octo.exe exit code: -7
[13:32:05][Step 3/3] Unable to create or deploy release. Please check the build log for details on the error.
[13:30:26][Step 3/3] Unable to create or deploy release. Please check the build log for details on the error.
[13:30:26][Step 3/3] Step Release (OctopusDeploy: Create release) failed

Can you please resolve this issue ASAP.

Regards,
Shyam

Matthew_Casperson · 21 August 2017 00:08

Hi Shyam, thanks for reaching out.

From the exception in the logs you have provided, it looks like the Id autoincrement value needs to be reset in the EventRelatedDocument table. To do this:

Shutdown Octopus Deploy
Create a full backup of the database
Run the following SQL (you may want to download SQL Server Management Studio from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms to run the script):

DECLARE @maxVal INT
SELECT @maxVal = ISNULL(max(ID),0)+1 from octopus.dbo.EventRelatedDocument
DBCC CHECKIDENT('octopus.dbo.EventRelatedDocument', RESEED, @maxVal)

Regards
Matt C

Shyam · 23 August 2017 11:03

Issue has been resolved

Thank you.