Knowledge Partner
Knowledge Partner
267 views

Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution
 
 

Hi Peers,

We are in process of upgrading QC 9.2 projects to ALM 12.50.

To achieve this this we are upgrading projects from QC 9.2 -> ALM 11.0 -> ALM 11.52 -> ALM 12.50.

While Upgrading one of the QC 9.2 project to ALM 11.0 we are recieving below error message:

18/12/2017 12:55:29: Error: 
18/12/2017 12:55:29: Stack: com.mercury.optane.core.db.spring.JDBCTemplate.execute(JDBCTemplate.java:279)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.JDBCTemplateForMaintenance.execute(JDBCTemplateForMaintenance.java:44)
18/12/2017 12:55:29: Stack: com.mercury.optane.core.db.spring.JDBCTemplate.execute(JDBCTemplate.java:243)
18/12/2017 12:55:29: Stack: com.mercury.optane.core.db.spring.JDBCTemplate.execute(JDBCTemplate.java:152)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.SqlsExecutor.run(SqlsExecutor.java:48)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.BaseFlowStep.start(BaseFlowStep.java:61)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.FlowEngine.actualStepExecute(FlowEngine.java:152)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.FlowEngine.executeStep(FlowEngine.java:107)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.FlowEngine.executeXML(FlowEngine.java:249)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.FlowEngine.executeFlowScript(FlowEngine.java:212)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.UpgradeTask.actualUpgrade(UpgradeTask.java:325)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.UpgradeTask.runTask(UpgradeTask.java:127)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.UpgradeTask.runTask(UpgradeTask.java:55)
18/12/2017 12:55:29: Stack: com.mercury.td.saserver.maintenance.AbsMaintenanceTask.run(AbsMaintenanceTask.java:272)
18/12/2017 12:55:29: Stack: java.lang.Thread.run(Thread.java:619)
18/12/2017 12:55:29: Exception occurred. Failed SQL: /* ~~QC */ 
CREATE UNIQUE NONCLUSTERED
INDEX [FAVORITES_NAME_LWR_UK]
ON [FAVORITES]([FAV_PARENT_ID],[FAV_OWNER],[FAV_MODULE],[FAV_NAME]) [Mercury][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 is (6, __default__, 2, IQ Overview). 18/12/2017 12:56:04:

As per vendor recommendation, followed KM00258261 provided in below link to apply workaround:

https://softwaresupport.softwaregrp.com/group/softwaresupport/search-result/-/facetsearch/document/KM00258261

Title : Duplicate Favorites Issue when upgrading a project from QC 10.00 or earlier to ALM 11.XX
Document ID : KM00258261
Product - Version: quality center ;
OS : null,All
Updated : 2016-Jun-16
Summary : This article provides a solution for an error occuring during an upgrade of a project from QC 10.00 or earlier to ALM 11.XX
Problem
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'.
Cause
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.
Fix
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
2. 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.

Unfortunately, Query 1 is returning 0 records for my project, hence I could not update any data for removing duplicates by using Query 2.

Can any one please help me in fixing this issue?

Regards,

Srihari

0 Likes
1 Solution

Accepted Solutions
Knowledge Partner
Knowledge Partner

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Thanks Roy this issue is resolved now.

From the error message we identified "IQ Overview" is the duplicate causing this issue.

So we queried COMMON_SETTINGS table with CSET_NAME = "IQ Overview"

It returned two records, for second record we updated "IQ Overview" to "IQ Overview Renamed".

Post that when we ran upgrade , project got upgraded successfully.

Thanks all for your help.

Regards,

Srihari

0 Likes
11 Replies
Chris_Grz Outstanding Contributor.
Outstanding Contributor.

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

In the first query remove or comment out the lines

and a.CSET_OWNER <> '__default__'
and b.CSET_OWNER <> '__default__'

From the error message, it seems that the faulty record has those caracterisctics.

<CSET_OWNER> in the second query would then be '__default__'.

As I have no mean to test this, I strongly recommend that you archive the project before applying this.

0 Likes
Knowledge Partner
Knowledge Partner

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hi Chris,

I removed below two lines and executed the query still it is returning 0 records.

nd a.CSET_OWNER <> '__default__'
and b.CSET_OWNER <> '__default__'

Please advise.

Regards,

Srihari

 

0 Likes
Carlos_S_ALM Super Contributor.
Super Contributor.

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hello Srihari

After restored the project from 9.2 to 11, have you tried to run the verify/repair process before upgrading?

Looking forward to your response.

Regards,

Carlos Solis Perez
Software Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the option at the bottom left of the post and show your appreciation.
0 Likes
Contributor.. Kenneth_G_ALM Contributor..
Contributor..

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hello Srihari

In QC 9.2 it was possible to have different kinds of favorite filters (for Grid and Tree views) with the same name. However, in ALM 11.00 and above the favorites were consolidated to the ‘Favorites’ menu in both of the views (Tree and Grid Views). Duplicate names are also not allowed in later versions. This is causing the upgrade error.

Check the next KM

https://softwaresupport.softwaregrp.com/group/softwaresupport/search-result/-/facetsearch/document/KM00207909

Kenneth Garcia.
Customer Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.
If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the STAR at the bottom left of the post and show your appreciation.
0 Likes
Knowledge Partner
Knowledge Partner

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hi Carlos, Yes, I verified the project and then repaired it before upgrade.

Both tasks completed successfully without any issues/failures.

Regards,

Srihari

0 Likes
Knowledge Partner
Knowledge Partner

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hi Kenneth,

Even with Valid SAID's I am getting "You donot have permission" while trying to access given KM.

https://softwaresupport.softwaregrp.com/group/softwaresupport/search-result/-/facetsearch/document/KM00207909

Could you please copy & paste the content for me.

Regards,

Srihari

0 Likes
Roy_C_QC Honored Contributor.
Honored Contributor.

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hi @Srihari-,

Thanks for your reply,

Please find attached the content of the knowledge document (KM00207909) that my colleague Kenneth referred.

Now then, it is important to remind that the Committed, Extedend, and Self-Help Support with Rights to New Versions  (upgrades) for QC 9.2 version has ended.

For this reason, you should use a newer version in order to get support and assistance while doing upgrades of projects and new installation.

It is good to see that you are planning the upgrade to 12.50 version; however, at this momennt the best action you can do is to start a new ALM installation of 11.52 version, at least. This way we can properly assist with any doubt or problem you might have.

In addition, let's consider that migrating projects from an version like 9.2 to 12.50 is not quite recommended, due to the extense difference between both version's database architechture. Indeed it will be easier for you to migrate project from more close versions, and the data integrity of projects will be kept.

Note: You can check the details about the Obsolence of Micro Focus products here:
softwaresupport.hp.com/obsolescence-migrations

The 9.2 version is not listed there because its Self-Help Support with Rights to New Versions ended some time ago.

I will be attentive to your response and comments,

Thanks & regards!

 

Roy Chinchilla Ramírez
Software Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the option at the bottom left of the post and show your appreciation.
0 Likes
Roy_C_QC Honored Contributor.
Honored Contributor.

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hi @Srihari-

It looks like the file was not attached,
Please find below the contents of it.
  

Title: Error: "..Cannot insert duplicate key row in object 'td.FAVORITES' with unique index 'FAVORITES_NAME_LWR_UK.." upgrading QC 10 project to ALM 11.5

Upgrading a QC 10 project to ALM 11.5 fails with the following type of error:


**********************
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 (, , , , , , , , ...
2012-09-13 11:15:46.91  ) [Mercury][SQLServer JDBC Driver][SQLServer]Cannot insert duplicate key row in object 'td.FAVORITES' with unique index 'FAVORITES_NAME_LWR_UK'.
2012-09-13 11:15:46.927  Stack: com.hp.alm.platform.db.CAbsStatement.createSQLExceptionWithSQLStatement(CAbsStatement.java:225)
**********************

Cause

In QC 10.00 it was possible to have different kinds of favorite filters (for Grid and Tree views) with the same name. However, in ALM 11.00 and above the favorites were consolidated to the ‘Favorites’ menu in both of the views (Tree and Grid Views). Duplicate names are also not allowed in later versions. This is causing the upgrade error with ALM 11.5.

Fix

Following workaround can be applied on the original QC 10 projects prior to the upgrade:

 

  1. Run the following query:

  

 

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 = 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 i'th row CSET_NAME value from the result of query 1, and <CSET_OWNER> with the i'th row CSET_OWNER value from the result of query 1.)

Note:  For Oracle database, must use “commit” command after the update.

Internal Response

Final fix will be provided with:

ALM 11.00 Patch 13
ALM 11.52 Patch 2

Roy Chinchilla Ramírez
Software Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the option at the bottom left of the post and show your appreciation.
0 Likes
Knowledge Partner
Knowledge Partner

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Thanks Roy this issue is resolved now.

From the error message we identified "IQ Overview" is the duplicate causing this issue.

So we queried COMMON_SETTINGS table with CSET_NAME = "IQ Overview"

It returned two records, for second record we updated "IQ Overview" to "IQ Overview Renamed".

Post that when we ran upgrade , project got upgraded successfully.

Thanks all for your help.

Regards,

Srihari

0 Likes
Roy_C_QC Honored Contributor.
Honored Contributor.

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hi @Srihari-,

Thanks for your update,

We are glad to know that now the issue is solved!
Feel free to reach us back if you need anything else.

 

Regards!

 

Roy Chinchilla Ramírez
Software Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the option at the bottom left of the post and show your appreciation.
0 Likes
Roy_C_QC Honored Contributor.
Honored Contributor.

Re: Upgrade Error: Duplicate key was found for the object name 'td.FAVORITES'

Jump to solution

Hi @Srihari_Amgen,

Thanks for your update,

We are glad to know that now the issue is solved!
Feel free to reach us back if you need anything else.

 

Regards!

 

Roy Chinchilla Ramírez
Software Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the option at the bottom left of the post and show your appreciation.
Roy Chinchilla Ramírez
Software Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the option at the bottom left of the post and show your appreciation.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.