Unable to access catalog items or Post upgrade failures after vRealize Automation upgrade
search cancel

Unable to access catalog items or Post upgrade failures after vRealize Automation upgrade

book

Article ID: 325887

calendar_today

Updated On:

Products

VMware Aria Suite

Issue/Introduction

Symptoms:
  • Post upgrade task fails during upgrade attempt with:
2018-10-31T19:49:44.400742+00:00 FQDNofAppliance vcac-config: ERROR liquibase.severe:89 - liquibase/catalog-service-master.xml: liquibase/catalog-service-BUG2067627.xml::1::BUG2067627: Change Set liquibase/catalog-service-BUG2067627.xml::1::BUG2067627 failed.  Error: Error executing SQL ALTER TABLE public.cat_tenant ALTER COLUMN id TYPE CITEXT: ERROR: could not create unique index "cat_tenant_pkey"
  Detail: Key (id)=(DEV) is duplicated.
  • You are unable to access catalog items
  • In /var/log/vmware/vcac/catalina.out you see entries similar to:
[UTC:2018-09-07 11:05:49,831 Local:2018-09-07 11:05:49,831] vcac: [component="cafe:catalog" priority="ERROR" thread="tomcat-http--49" tenant="tenant-name" context="mwMhych9" parent="" token="mwMhych9"] com.vmware.vcac.platform.service.rest.resolver.ApplicationExceptionHandler.handleUnexpectedException:874 - result returns more than one elements; nested exception is javax.persistence.NonUniqueResultException: result returns more than one elements
org.springframework.dao.IncorrectResultSizeDataAccessException: result returns more than one elements; nested exception is javax.persistence.NonUniqueResultException: result returns more than one elements
  • In postgres database cat_tenant table you see duplicate entry for tenant:
# select * from public.cat_tenant;
            id | lastsync                | name
---------------+-------------------------+---------------
 TENANT        | 2017-11-22 17:00:04.934 | TENANT
 tenant        | 2018-09-06 19:05:14.954 | TENANT
 vsphere.local | 2018-09-06 20:27:57.591 | vsphere.local
(3 rows)


Environment

VMware vRealize Automation 7.x

Resolution

To work around the issue, remove the dependency and delete the old tenant.

Caution: The following steps involve deletion of data. Ensure you have taken valid snapshots of the appliance(s).

  • Open SSH session to vRA
  • Connect to the database:
    • su - postgres
    • psql vcac
  • Identify the old tenant:
# select * from public.cat_tenant;
      id | lastsync | name
---------------+-------------------------+---------------
 TENANT        | 2017-11-22 17:00:04.934 | TENANT
 tenant        | 2018-09-06 19:05:14.954 | TENANT
 vsphere.local | 2018-09-06 20:27:57.591 | vsphere.local
(3 rows)
  • Attempt to delete the tenant:

BEGIN;
# delete from cat_tenant where id='TENANT';
  • If DELETE was successful, commit the transaction:
COMMIT;

Else, ROLLBACK;

  • In the event you get an error similar to:
ERROR: update or delete on table "cat_tenant" violates foreign key constraint "cat_icon__tenant_id__cat_tenant__id__fkey" on table "cat_icon"
DETAIL: Key (id)=(TENANT) is still referenced from table "cat_icon".
  • Inspect table cat_icon:
select * from public.cat_icon;
  • You may see entries like:
                             id                             | contenttype |                          filename                          | image | subtenant_id | tenant_id
------------------------------------------------------------+-------------+------------------------------------------------------------+-------+--------------+----------
 cafe_default_icon_genericCatalogItem                       | image/png   | genericCatalogItem.png                                     | 26344 |              |
 cafe_default_icon_genericService                           | image/png   | genericService.png                                         | 26345 |              |
 cafe_default_icon_genericResourceOperation                 | image/png   | genericResourceOperation.png                               | 26346 |              |
 cafe_default_icon_genericAllServices                       | image/png   | genericAllServices.png                                     | 26347 |              |
 software_catalog_item_icon                                 | image/png   | software_catalog_item_icon                                 | 26348 |              |
 ServiceIcon-1495091052664                                  | image/png   | Linux.png                                                  | 26629 |              | tenant
 ServiceIcon-1495091070014                                  | image/png   | Windows (1).png                                            | 26630 |              | tenant
 ServiceIcon-1495091098833                                  | image/png   | VCO.png                                                    | 26631 |              | TENANT
  • Update the table to remove the dependency on cat_tenant table:
BEGIN;
UPDATE cat_icon SET tenant_id = 'tenant' WHERE tenant_id = 'TENANT';
  • Execute DELETE query again:
delete from cat_tenant where id='TENANT';
  • If delete is successful, commit the transaction:
COMMIT;

Else, ROLLBACK;


Additional Information

NOTE! If the cat_subtenant table is impacted, please open a new problem report for guidance. Do not run any scripts associated with cleaning up this content.  This issue can be environment specific.  Do not deviate from these instructions unless instructed to do so by VMware development engineering teams.

Impact/Risks:
  • As with any manual edits to a live database, ensure there is a full backup before making any modifications


Powered by Wolken Software