Duplicate Key preventing creation of an index when migrating SDM

Products

• CA Service Desk Manager

Issue/Introduction

When trying to upgrade from 17.1 to 17.3 getting an error when applying the MDB patch.   Log shows:

01-25,21:13:33 DEBUG - The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.crwf' and the index name 'crwf_x0'. The duplicate key value is (cr:1492922, 100).

Environment

Release : 17.1

Component : SVC DESK UPGRADE

Cause

Querying the crwf table shows that there were duplicate entries.  One can see by running this DB query

select o.persid, o.object_id, o.sequence from crwf o
inner join(select object_id, sequence from crwf  group by object_id, sequence having count(*)> 1) s ON 
s.object_id = o.object_id and s.sequence = o.sequence

Resolution

Removal of the duplicate entries is the best long term solution.  However, finding the root cause of the duplicates will also lead to a longer term solution.  In one such case, a web services call being made to generate requests and workflows was found to be the cause of the duplicate entries.

Additional Information

An immediate workaround, though not advisable as this only bypasses the concern temporarily is to back up the crwf table, empty the table entirely, run the install process to migrate SDM, then load the crwf table from a backup.