Violation of PRIMARY KEY constraint 'PK_Release_Id'

[hennuy.francois]

Hello, I have this message when I try to create a release in Octopus, Can you help me?

[12:49:36][Octopus Deploy] The statement has been terminated.
[12:49:36][Octopus Deploy] The command being executed was:
[12:49:36][Octopus Deploy] INSERT INTO dbo.[Release]  (Version, Assembled, ProjectId, ChannelId, ProjectVariableSetSnapshotId, ProjectDeploymentProcessSnapshotId, Id, JSON) VALUES 
[12:49:36][Octopus Deploy] (@Version, @Assembled, @ProjectId, @ChannelId, @ProjectVariableSetSnapshotId, @ProjectDeploymentProcessSnapshotId, @Id, @JSON)
[12:49:36][Octopus Deploy] INSERT INTO [RelatedDocument] ([Id], [Type], [RelatedDocumentId], [RelatedDocumentType]) VALUES
[12:49:36][Octopus Deploy] (@Id, 'Release', @relateddocument_0, 'VariableSet')
[12:49:36][Octopus Deploy] 
[12:49:36][Octopus Deploy] SQL Error 2627 - Violation of PRIMARY KEY constraint 'PK_Release_Id'. Cannot insert duplicate key in object 'dbo.Release'. The duplicate key value is (Releases-8556).
[12:49:36][Octopus Deploy] The statement has been terminated.
[12:49:36][Octopus Deploy] Error from Octopus server (HTTP 500 InternalServerError)
[12:49:36][Octopus Deploy] Exit code: -7
[12:49:36][Octopus Deploy] Octo.exe exit code: -7

[dane.falvo]

Hi @hennuy.francois,

I’m sorry that you’ve hit this issue. It’s definitely not ideal, but it is something that we have seen in the past.

The PK_Release_Id generally increments everytime a release is created. Occasionally, the Release ID for some reason, will fall behind the highest number in the dbo.Release table, usually when something unexpected happens.

Luckily, there is a relatively quick fix for this but it requires running two SQL queries against your database.

Query 1 - find out the highest value in the dbo.Release table.

SELECT Top 10 CAST(REPLACE(id, 'Releases-', '') AS BIGINT) AS MaskedId FROM dbo.Release ORDER BY MaskedId DESC

e.g. result

1. 2124
2. 2123
3. 2120
4. 2118

This should provide you with a list of ID’s. You will want to select the highest value, which will be in row 1. Add approximately 10 to this number and then set that value in the KeyAllocation table using this query.

Update dbo.KeyAllocation
Set Allocated = 2134
where CollectionName = 'Release'

Please be sure to use best practices and take backups before and after making any changes to the KeyAllocation table. If you re-encounter this issue and need to run through this process again, please contact us before doing so as these instructions are not recommended to be used in any case other than when directly instructed by Octopus Support.

I hope this helps and allows you to keep deploying.

Kind Regards,

[hennuy.francois]

Thanks for your quick response, I will see if it works.