vRealize Automation 7.6 to 8.x deployment migration failing with error: 400 Bad Request: The request URI is not valid. Since the segment 'LoadBalancers' refers to a collection

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

Below error is seen during deployment migration if load balancer constructs are present :

 

org.springframework.web.client.HttpClientErrorException$BadRequest: 400 Bad Request: "{"odata.error":{"code":"","message":
{"lang":"en-US","value":"The request URI is not valid. Since the segment 'LoadBalancers' refers to a collection, this must be the last segment in the request URI. All intermediate segments must refer to a single resource."}
}}"

Environment

VMware vRealize Automation 8.x
VMware vRealize Automation 7.x

Cause

There is a discrepancy between the Catalog Resource's load balancer Id and the Iaas DB's LB id. Hence we are running into this issue.

The issue is on the vRealize Automation 7.x side. 

Due to discrepancy between data in MSSQL DB and PSQL DB in vRealize Automation 7, the deployment migration fails as it queries for LB details in MSSQL DB in IAAS using its ID fetched from PSQL DB.

Resolution

To get a list of such load balancer IDs, post which cat_resource table  in vRA7 PSQL needs to be updated with the latest LB Id and then deployment migration can be attempted

Follow the below steps to workaround the issue in vRA7.6 after taking a backup of the postgres DB:

Workaround:

1. Query 1: Check the 'Id' of the load balancer by querying in MSSQL DB in IAAS for the load balancer.

SELECT * FROM DynamicOps.VCNSModel.LoadBalancers where name ='Load_Balancer1';

 

Where Load_Balancer1 is the name of the load balancer, for example:

SELECT * FROM DynamicOps.VCNSModel.LoadBalancers where name ='On-Demand_Load_Balancer_1-<UUID_1>';  

 

2. Query 2: Check the 'NetworkProviderBindingId' of the load balancer by querying in Postgres in the Cafe client.

SELECT id,name,resourcetype_id,parentresource_id,binding_id,resourcedata->'NetworkProviderBindingId' AS NetworkProviderBindingId, resourcedata->'Name' AS resourcename, resourcedata->'IaasEdgeId' as IaasEdgeId FROM cat_resource WHERE name ='Load_Balancer1';

Where Load_Balancer1 is the name of the load balancer, for example.
 

SELECT id,name,resourcetype_id,parentresource_id,binding_id,resourcedata->'NetworkProviderBindingId' AS NetworkProviderBindingId, resourcedata->'Name' AS resourcename, resourcedata->'IaasEdgeId' as IaasEdgeId FROM cat_resource WHERE name ='On-Demand_Load_Balancer_1-<UUID_1>';

 

3. Query 3: The 'Id' from query 1 should match the 'NetworkProviderBindingId' from query 2 above.If they differ, update the cat_resource table with the updated id ie., with the id from  DynamicOps.VCNSModel.LoadBalancers table in IAAS.

update cat_resource set resourcedata = REPLACE(resourcedata::text ,'", "NetworkProviderBindingId"=>"{\"type\":\"string\",\"value\":\"ID1\"','", "NetworkProviderBindingId"=>"{\"type\":\"string\",\"value\":\"ID2\"')::hstore where name = 'Load_Balancer1';

Where:

• Load_Balancer1 is the name of the LoadBalancer.

• ID1 is the old value.

• ID2 is the new value that needs to be updated (This should be the output of query 1)

For example:
 

update cat_resource set resourcedata = REPLACE(resourcedata::text ,'", "NetworkProviderBindingId"=>"{\"type\":\"string\",\"value\":\"3167\"','", "NetworkProviderBindingId"=>"{\"type\":\"string\",\"value\":\"3168\"')::hstore where name = 'On-Demand_ Load_Balancer_1-<UUID_1>';

 

4. In a similar way, the mismatched IDs for all such load balancers should be corrected before proceeding with deployment migration. Get the list of load balancers:

   select count ( * ) from cat_resource where resourcetype_id = 'Infrastructure.Network.LoadBalancer.NSX';
   
   select count ( * ) from [DynamicOps.VCNSModel].LoadBalancers where name is not NULL;

    

5. Check the 'NetworkProviderBindingId' with the 'Id' of the load balancers from IAAS DB.
6. Correct the 'NetworkProviderBindingId' for all the error load balancers using Query3.
7. Once all the load balancer IDs are correct, you can reattempt deployment migration.