vRA/vRO 7.4 upgrade fails with duplicatesException – Orchestartor database

VAMI UI Error:

postupdate.log (/var/log/bootstrap):

Resolution: (As usual: Take backup/snapshot before performing below steps)

Quick fix: If you do not use Horizon Plugin in your environment, please remove it and then retry the upgrade. 

Else follow below steps

Step 1:

If you have VMware horizon plugin in your environment and if you are using this, before you perform upgrade to 7.4 – run the below script to find out the duplicate entry:

  1. SSH to vRA/vRO appliance.
  2. Connect to the database
    For vRA:                       For vRO:
    su – postgres                  su – postgres
    psql vcac                         psql vmware
  3. Run the below script:

select * from (
select categoryid, name, count(*)-1, ‘vmo_configelement’ as type from vmo_configelement group by categoryid, name having count(*) > 1
union
select parentcategoryid, name, count(*)-1, ‘vmo_configelementcategory’ as type from vmo_configelementcategory group by parentcategoryid, name having count(*) > 1
union
select elementid, ” as name, count(*)-1, ‘vmo_configelementcontent’ as type from vmo_configelementcontent group by elementid having count(*) > 1
union
select ” as id, name, count(*)-1, ‘vmo_package’ as type from vmo_package group by name having count(*) > 1
union
select packageid, ” as name, count(*)-1, ‘vmo_packagecontent’ as type from vmo_packagecontent group by packageid having count(*) > 1
union
select policyid, ” as name, count(*)-1, ‘vmo_policycontent’ as type from vmo_policycontent group by policyid having count(*) > 1
union
select categoryid, name, count(*)-1, ‘vmo_resourceelement’ as type from vmo_resourceelement group by categoryid, name having count(*) > 1
union
select elementid, ” as name, count(*)-1, ‘vmo_resourceelementcontent’ as type from vmo_resourceelementcontent group by elementid having count(*) > 1
union
select scriptmodulecategoryid, name, count(*)-1, ‘vmo_scriptmodule’ as type from vmo_scriptmodule group by scriptmodulecategoryid, name having count(*) > 1
union
select ” as id, name, count(*)-1, ‘vmo_scriptmodulecategory’ as type from vmo_scriptmodulecategory group by name having count(*) > 1
union
select scriptmoduleid, ” as name, count(*)-1, ‘vmo_scriptmodulecontent’ as type from vmo_scriptmodulecontent group by scriptmoduleid having count(*) > 1
union
select dunesobjectid, version, count(*)-1, ‘vmo_versionhistorycontent’ as type from vmo_versionhistorycontent group by dunesobjectid, version having count(*) > 1
union
select workflowid, ” as name, count(*)-1, ‘vmo_workflowcontent’ as type from vmo_workflowcontent group by workflowid having count(*) > 1
) as u;

Output will be like this:

Step 2:

Run the below command to populate the duplicate entries in vmo_resourceelement table (order by name so you will be able to see the duplicates in consecutive rows)

vcac=# select * from vmo_resourceelement where categoryid = ‘ff80808162fadd550162fae0ed3c03ec’ order by name;

Output will be like this:

Step 3:

Remove one duplicate entry for each property from above output:

For example: (For property name: de_DE.properties)

id | categoryid | name | objversion | mimetype | description | contentsize | allowedoperations
————————————–+———————————-+——————–+————+————+—————–+————-+——————-
cda27b0f-944c-4096-9219-ee0889242ce9 | ff80808162fadd550162fae0ed3c03ec | de_DE.properties | 1.1.0 | text/plain | Upgrade version | 0 | vf
c6ae3734-fd61-11e6-981d-0200ff3654b6 | ff80808162fadd550162fae0ed3c03ec | de_DE.properties | 1.1.0 | text/plain | Upgrade version | 0 | vf

Run command:

delete from vmo_resourceelement where id = ‘cda27b0f-944c-4096-9219-ee0889242ce9’ and name = ‘de_DE.properties’;

Do this for all 19 properties – Remove only one duplicate (doesn’t matter which one you remove)

 

Step 4:

Once you have completed removing all duplicate entries. Run the script again mentioned in Step 1 to check if there are any other duplicate entries.

output will be like this:

categoryid | name | ?column? | type
————+——+———-+——
(0 rows)

Step 5:

Now, attempt to upgrade to 7.4 and it should work! Tried in my lab and it worked.