We have some nuget external feeds, when trying to create a new docker repository feed we get a SQL exception
Error while executing SQL command in transaction 'http://loc-cb-02:8080/api/Spaces-1/feeds f5f72aec697b4cbe8273a9686452575a': Violation of PRIMARY KEY constraint 'PK_Feed_Id'. Cannot insert duplicate key in object 'dbo.Feed'. The duplicate key value is (Feeds-2).
The statement has been terminated.
The command being executed was:
INSERT INTO dbo.[Feed] (Name, FeedType, SpaceId, Id, JSON) VALUES
(@Name, @FeedType, @SpaceId, @Id, @JSON)
SQL Error 2627 - Violation of PRIMARY KEY constraint 'PK_Feed_Id'. Cannot insert duplicate key in object 'dbo.Feed'. The duplicate key value is (Feeds-2).
The statement has been terminated.
Every time we save, the feed id increases, so if we press save enough times, we get to a free feed id.
Hi Chris,
Thanks for reaching out, Sorry to hear you have hit this issue with Octopus. We have seen this error previously when Octopus database gets restored while the service is still running, could this have happened in your environment?
One thing to try would be to restart the Octopus service, this should reload the KeyAllocation table which should fix this issue.
If that doesn’t help, could you please send me the results of the following two queries:
SELECT Id FROM Feed
SELECT * FROM KeyAllocation WHERE CollectionName = ‘Feed’
The fix will be to increment the value of the Allocated column of the KeyAllocation table (where CollectionName = ‘Feed’) to be greater than the last ID in the feed table. But before proceeding please make sure you have a database backup. Octopus will need to be restarted after that.
Let me know if this sorts it out?
Regards
John
Hi John,
Through my multiple save attempts, the feed is eventually saved.
Any subsequent feeds are then able to be added fine, so it does seem like your assumption on the key allocation would be correct.
I updated from a 2018.7.0 version to 2019.3.3 using the installer which automatically stopped and restarted the service.
From my point of view, my issue is now fixed, but it would seem that on upgrade, they KeyAllocations were not correctly updated.
Hi Chris,
Thanks for the update.
There could have been something wrong with the update.
Just to make sure the database is correct after the upgrade, can you run the system integrity checks in /configuration/diagnostics:
Regards
John
