Aria Automation upgrade fails with 'ERROR: cannot replace existing key for the provisioning database'

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• The issue affects upgrades to Aria Automation 8.11.1 & 8.12
• The upgrade fails and provisioning pods fail to initialize
• The logfile /services-logs/prelude/provisioning-service-db-upgrade/console-logs/provisioning-service-db-upgrade.log has errors similar to:

2023-03-03T09:36:28.013Z ERROR provisioning [host='provisioning-service-db-upgrade-6ns9h' thread='main' user='' org='' trace='' parent='' span=''] c.vmware.admiral.host.ProvisioningHost.log:454 - [8282] Failed Liquibase update: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset liquibase/changelog/335-resync-existing-attached-resources-owner-custom-properties.xml::24-disk-resync-owner-custom-properties-update:::
Reason: liquibase.exception.DatabaseException: ERROR: cannot replace existing key
Hint: Try using the function jsonb_set to replace key value. [Failed SQL: (0) WITH disk_desc_custom_prop AS (
SELECT
disk.description_link,
disk.custom_properties->>'__ownerGroupName' AS owner_group_name

FROM disk_state AS disk
INNER JOIN disk_state AS disk_desc
ON disk.description_link = disk_desc.document_self_link

WHERE disk.id != disk_desc.id
AND disk.description_link IS NOT null
AND disk.document_update_action != 'DELETE'
AND disk.custom_properties->>'_ownerGroupName' IS DISTINCT FROM disk_desc.custom_properties->>'_ownerGroupName')
UPDATE disk_state
SET custom_properties =
CASE
WHEN disk_desc_custom_prop.owner_group_name IS NOT NULL
THEN jsonb_insert(COALESCE(custom_properties, '{}'), '{__ownerGroupName}', to_jsonb(disk_desc_custom_prop.owner_group_name))
ELSE
custom_properties - '__ownerGroupName'
END
FROM disk_desc_custom_prop
WHERE disk_state.document_self_link = disk_desc_custom_prop.description_link]
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:126)
at liquibase.Liquibase.lambda$null$0(Liquibase.java:291)
at liquibase.Scope.lambda$child$0(Scope.java:180)
...
at com.vmware.admiral.host.ProvisioningSpringApplication.main(ProvisioningSpringApplication.java:122)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset liquibase/changelog/335-resync-existing-attached-resources-owner-custom-properties.xml::24-disk-resync-owner-custom-properties-update:::
Reason: liquibase.exception.DatabaseException: ERROR: cannot replace existing key

 

Environment

VMware vRealize Automation 8.x

Cause

The issue occurs due to a bug in how resource owners are handled by the database upgrade scripts.

Resolution

A resolution for the issue will be included in the Aria Automation 8.12 release. Upgrade attempts to this version will not be affected by the issue.

Workaround:

Note: Before proceeding best practise dictates that you first snapshot each Aria Automation appliance.

To workaround the issue you can unregister the offending resources, perform the upgrade and then onboard the resources.  To identify the affected resources for unregistering perform the following steps:

1. SSH to Aria Automation appliance and login as root user

2. To connect to the Provisioning database:

vracli dev psql

type 'yes' at the prompt

\c provisioning-db

3. To query for any affected resources run:

SELECT disk.description_link, disk.custom_properties->>'_ownerGroupName' AS owner_group_name FROM disk_state AS disk INNER JOIN disk_state AS disk_desc ON disk.description_link = disk_desc.document_self_link WHERE disk.id != disk_desc.id AND disk.description_link IS NOT null AND disk.document_update_action != 'DELETE' AND disk.custom_properties->>'ownerGroupName' IS DISTINCT FROM disk_desc.custom_properties->>'_ownerGroupName';