Error during upgrade to 2014.1.1: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.logs and the index name 'PK_Logs'

Version 1

    Details

    When running the System Configuration Wizard (SCW) to perform the upgrade to HEAT 2014.1.1 an error is being received when upgrading the ConfigDB:

     


    Resolution

    Information about solving this problem can be found in the Addendum to the HEAT 2014.1.1 Release Notes.  The information from the addendum is reproduced below.


    Symptom
    During the upgrade, proceed to step 5 ("Upgrade System") of System Configuration Wizard. While upgrading Configuration Database, one or both versions of error is displayed:
    • "...The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.logs and the index name 'PK_Logs'"
    • "...Table 'Frs_def_fashboards' already has a primary key defined on it."

    Root Cause
    ConfigDB in 2013.x platform did not have necessary indices. They are required in 2014.1.1 platform. Index creation may fails with table “Logs” depending on customer’s environment.

    Workaround Steps:
    Prior to running upgrade, use the following query to see the duplication in Logs table in Configuration Database (not the application database).
    SELECT LogEntryId FROM Logs
    GROUP BY LogEntryId
    HAVING COUNT(LogEntryId) > 1

    The following query can be used to keep the duplicated data in backup Logs table if desired
    SELECT *
    INTO Logs_backup_20140325
    FROM Logs
    WHERE LogEntryId IN
    (
    SELECT LogEntryId FROM Logs
    GROUP BY LogEntryId
    HAVING COUNT(LogEntryId) > 1
    )

    Delete the duplication using the below query
    DELETE Logs
    WHERE LogEntryId IN
    (
    SELECT LogEntryId FROM Logs
    GROUP BY LogEntryId
    HAVING COUNT(LogEntryId) > 1
    )

    Upgrade ConfigDB successfully