Running the configuration wizard on NPM 12.4 I received an error message saying:
dbm_DowngradeCurTable: Violation of PRIMARY KEY constraint 'PK_CPULoad_CS_dupl'. Cannot insert duplicate key in object 'dbo.CPULoad_CS_dupl'. The duplicate key value is (2019-02-21 19:00:08.5907252, 86).
Support said to drop a few tables including CPULoad_CS_dupl, but after running the drop and starting the configuration wizard again I saw the same error. I got past it with a few queries.
I started by looking at the rows with duplicate nodeID and timestamp values:
SELECT TOP 1000 * FROM [dbo].[ResponseTime_CS_cur] a
JOIN (SELECT NodeID, Timestamp, COUNT(*) as count
FROM ResponseTime_CS_cur
GROUP BY NodeID, Timestamp
HAVING COUNT(*) > 1) b
ON a.NodeID = b.NodeID AND a.Timestamp = b.Timestamp
Several of these were duplicated two or three times, and in total there were over 1,000 rows that were duplicated. So I got a count:
SELECT COUNT (*) FROM [dbo].[ResponseTime_CS_cur] a
JOIN (SELECT NodeID, Timestamp, COUNT(*) as count
FROM ResponseTime_CS_cur
GROUP BY NodeID, Timestamp
HAVING COUNT(*) > 1) b
ON a.NodeID = b.NodeID AND a.Timestamp = b.Timestamp
The total count was over 45,000 duplicated rows. Trying to list them all or delete them all at once failed so I deleted 10,000 at a time:
delete from dbo.ResponseTime_CS_cur where inmemID in
(SELECT top 10000 inmemID FROM [dbo].[ResponseTime_CS_cur] a
JOIN (SELECT NodeID, Timestamp, COUNT(*) as count
FROM ResponseTime_CS_cur
GROUP BY NodeID, Timestamp
HAVING COUNT(*) > 1) b
ON a.NodeID = b.NodeID AND a.Timestamp = b.Timestamp)
I had to repeat that last query five times to clear them all out, but after doing this I ran the configuration wizard without issue.
