Duplicate Favorites Issue when upgrading a project from QC 10.00 or earlier to ALM 11.XX

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 …

or

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.

  1. 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

  1. For each row (CSET_NAME, CSET_OWNER) pair in the result run the following query:

UPDATE td.COMMON_SETTINGS

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s