Provisioning Service Database Upgrade Fails Due to Liquibase Precondition Check in VCF Automation

Products

• VCF Automation
• VCF Operations/Automation (formerly VMware Aria Suite)

Issue/Introduction

• In Aria Automation 8.18.1 P3, a liquibase change modified a unique constraint by dropping the existing constraint and adding a new unique index.
  VCFA 9.0 contains a similar liquibase change under a different sequence ID. This leads to a conflict detected during upgrade pre-checks, resulting in upgrade failure.

• During a database upgrade of the Provisioning Service, the Liquibase migration fails with the following error:
  `Failed Liquibase update: liquibase.exception.LiquibaseException:
liquibase.exception.MigrationFailedException: Migration failed for changeset
liquibase/changelog/380-update-unique-key-constraint-for-storage-profile-association-state.xml::update-storage-profile-association-state-unique-constraint-to-allow-unique-rows-only-for-non-delete-action-rows
Reason:
    liquibase/changelog.xml : SQL Precondition failed. Expected '1' got '0'`

Environment

Aria Automation 8.18.1 Patch 3
VCF Automation 9.0

Cause

The upgrade fails because both versions (Aria Automation 8.18.1 P3 and VCFA 9.0) attempt to manage the same constraint/index differently.
When the upgrade pre-check detects the missing index (uq_storage_profile_association_state_storage_item_id_storage_de), it conflicts with the expected state and halts the process.

Resolution

Important:

Before initiating the upgrade to VCFA 9.0, execute the following SQL commands on the storage_profile_association_state table in the provisioning-db to update the unique key constraint and ensure a successful upgrade but only after taking a provisioning-db backup:

• SSH to Aria Automation appliance and login as root user
• To connect to the provisioning database run the following command:
  
  vracli dev psql provisioning-db
  
  

1. Drop the conflicting index (if exists)

Drop Index SQL

 

DROP INDEX IF EXISTS uq_storage_assoc_state_item_desc;

2. Identify the conflicting rows 

Identify the conflicting rows

 

SELECT storage_item_id, storage_description_link, COUNT(*) FROM storage_profile_association_state GROUP BY storage_item_id, storage_description_link HAVING COUNT(*) > 1;

3. Deleting the DELETE document_update_action records.

Remove the deleted records

 

DELETE FROM storage_profile_association_state WHERE document_update_action = 'DELETE';

4.Validate no conflicting data remains

Validate no conflicting data remains

 

SELECT storage_item_id, storage_description_link, COUNT(*) FROM storage_profile_association_state GROUP BY storage_item_id, storage_description_link HAVING COUNT(*) > 1;

It should return 0 as the result count.

5. Check if the unique constraint exists

Check if unique constraint exists

 

SELECT COUNT(*) FROM pg_constraint WHERE conname = 'uq_storage_profile_association_state_storage_item_id_storage_de';

If result is 0, then run the below query.

6. Re-create the original unique constraint (if missing)

Re-create the original unique index

 

ALTER TABLE storage_profile_association_state ADD CONSTRAINT uq_storage_profile_association_state_storage_item_id_storage_de UNIQUE (storage_item_id, storage_description_link);

7. After completing the steps above and confirming no conflicting data remains, proceed with the upgrade to VCFA 9.0.

Notes:

• Later patches of Aria automation 8.18.1 after P3 and VCFA 9.0.1 and later automatically handle the constraint conflict more gracefully.