Error: Unable to update indexes! when upgrading SES 6.4 SR1 to 6.5
Getting an error updating the database when upgrading the SES server from 6.4SR1 to 6.5 (6.5.0.241_signed)
Error: Unable to update indexes.
In SES 6.5 a unique index, "IX_UserKey", has been added to "UserKey" database table.
This will cause an update failure on any database that UserKey contains duplicated records.
The same way update can fail on "CompKeys" by "CompKeys_PC_Index_KeyIndex" unique index and "Computers" by "Computers_UniqueID" unique index.
To see duplicated records in UserKey table run:
WITH UKs AS(
SELECT UserKey_Index, UserIndex, KeyIndex, rn = ROW_NUMBER() OVER(PARTITION BY UserIndex, KeyIndex ORDER BY UserIndex, KeyIndex)
FROM UserKey
)
SELECT * FROM UKs WHERE (rn > 1);
To delete duplicated records from UserKey table run:
WITH UKs AS(
SELECT UserKey_Index, UserIndex, KeyIndex, rn = ROW_NUMBER() OVER(PARTITION BY UserIndex, KeyIndex ORDER BY UserIndex, KeyIndex)
FROM UserKey
)
DELETE FROM UKs WHERE (rn > 1);
Deleting duplicates from UserKey is safe. Here duplicates are just pointing the same key to the same user more than once.
The delete script just removes extra records, if there is any.
How this UserKey is created?
In user properties admin can add/assign keys to a user. This action adds records to UserKey table.
References: SD-11743