Cannot update Custom Resource as 2 tenants are using the same source_id

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• You try to update an vRSLCM migrated Custom Resource and it fails with the error

  could not execute statement; SQL [n/a]; constraint [form_formdefinition_source_id_source_type_type_tenant_key]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement".

Environment

VMware vRealize Automation 8.x

Cause

• In previous versions of vRSLCM, prior to 8.10 Patch 1, the migrated resource actions of a Custom Resource are written with the incorrect tenant id. These are from the source environment from which they have been migrated.
• After upgrading vRSLCM to 8.10 Patch 1 or later, trying to migrate the same Custom Resource creates a duplicate form_definition entry in the database causing an inconsistent state.

Resolution

This issue is resolved in VMware vRealize Automation 8.11.2.

See the Workaround section below for more information.

Workaround:

Prerequisites

• Please take simultaneous non-memory snapshots of each virtual appliance(s) in the cluster.
• You have access to root user and password
• You have SSH or console access to each virtual appliance.

Procedure

1. SSH / PuTTy into one vRA virtual appliance in the cluster
2. Connect to the form-db

   vracli dev psql form-db

3. Run the following command to isolate each resource action that has a duplicate entry in the form_formdefinition table

   select * from form_formdefinition fd where exists (select * from form_formdefinition fd2 where fd.source_id = fd2.source_id and fd.id != fd2.id) and source_type='resource.action';

Note: Document the form definitions where there is more than one form definition corresponding to a resource action. Document the tenant_id of the entries. The old and incorrect entries will most likely have the tenant_id of non-existing tenants in the target vRealize Automation environment. These are the entries that must be addressed in Step #4 and Step #5 for each pair of definitions.

4. Re-map the resource action form definition id to the correct one with the below query, updating the italicized content, correct-tenant-definition-id and wrong-tenant-definition-id, with the values pulled from Step #3.

   kubectl -n prelude exec -it postgres-0 – chpst -u postgres psql -d form-db -c "update xaas_resource_action set form_definition_id='correct-tenant-definition-id' where form_definition_id='wrong-tenant-definition-id'"

5. Delete the incorrect form definition

   kubectl -n prelude exec -it postgres-0 – chpst -u postgres psql -d form-db -c "delete from form_formdefinition where id='wrong-tenant-definition-id'"

Additional Information

Impact/Risks:
The affected Custom Resource cannot be updated from the UI. Duplicate / incorrect form definitions cannot be deleted via an API call unless you use the service token because of the incorrect tenant_id in the entry.