In Application Lifecycle Management (ALM) 11 or 11.5X the below error can be seen during project upgrade:[SQLServer JDBC Driver][SQLServer]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘td.FAVORITES’ and the index name ‘FAVORITES_NAME_LWR_UK’. The duplicate key value …
Error: Failed SQL: /* ~~QC */ INSERT INTO FAVORITES
(FAV_ID,FAV_PARENT_ID,FAV_NAME,FAV_FILTER_DATA,FAV_LAYOUT_DATA,FAV_IS_PUBLIC,FAV_OWNER,FAV_MODULE,FAV_VER_STAMP) VALUES (, , , , , , , , …) [Mercury][SQLServer JDBC Driver][SQLServer]Cannot insert duplicate key row in object ‘td.FAVORITES’ with unique index ‘FAVORITES_NAME_LWR_UK’.
In Quaility Center (QC) 10.00 and earlier versions of QC it was possible to have different kind of favorite filters (for Grid and Tree views) with the same name.
In ALM 11.00 and above the favorites were consolidated to the ‘Favorites’ menu in both of the views (Tree and Grid Views). Therefore favorites with the same name cannot be moved to the new structure as they became duplicates and this is causing the upgrade the upgrade of the project to fail.
To resolve the problem and eliminate the duplicates the below solution could be used. Note that the ALM project has to be backed up prior any modification.
- Run the following query from a database management tool:
SELECT DISTINCT a.CSET_NAME, a.CSET_OWNER FROM td.COMMON_SETTINGS a, td.COMMON_SETTINGS b
WHERE a.CSET_CATEGORY in (‘PLANN_GRID_VIEWS’,’PLANN_VIEWS’)
and b.CSET_CATEGORY in (‘PLANN_GRID_VIEWS’,’PLANN_VIEWS’)
and a.CSET_NAME <> ‘__default__’
and b.CSET_NAME <> ‘__default__’
and a.CSET_OWNER <> ‘__default__’
and b.CSET_OWNER <> ‘__default__’
and a.CSET_OWNER = b.CSET_OWNER
and a.CSET_NAME = b.CSET_NAME
and a.CSET_CATEGORY <> b.CSET_CATEGORY
order by a.CSET_NAME
- For each row (CSET_NAME, CSET_OWNER) pair in the result run the following query:
SET CSET_NAME = CSET_NAME + ‘_tree’
Where CSET_NAME = ‘<CSET_NAME>’ and CSET_OWNER = ‘<CSET_OWNER>’ and CSET_CATEGORY =’PLANN_VIEWS’
Replace the <CSET_NAME> with the row CSET_NAME value from the result of query 1, and <CSET_OWNER> with the row CSET_OWNER value from the result of query 1.