Aria Automation upgrade fails due to 'project-service-app' crashing

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• Aria Automation upgrade fails with error "LCMVRAVACONFIG90029" Deploying services.
• The 'project-service-app' is crashing
• The /var/log/services-logs/prelude/project-service-app/file-logs/project-service-app.log logfile contains a unique constraint error similar to:

WARN project-service [host='project-service-app-<id>' thread='main' user='' org='' trace='' parent='' span=''] o.s.b.w.r.c.AnnotationConfigReactiveWebServerApplicationContext.refresh:591 - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name '
liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset db/changelog/058-add-project-principals-uniqueness-constraint.xml::add-project-principals-uniqueness-constraint::kdinkov: Reason: liquibase.exception.DatabaseException: ERROR: could not create unique index "uq_principal_idproject_id_role"
Detail: Key (principal_id, project_id, role)=(<user>@<domain>, 555f9d6e-3720-473b-be5e-e5560eec21e2, ADMINISTRATOR) is duplicated. [Failed SQL: (0) ALTER TABLE project_principals
ADD CONSTRAINT uq_principal_idproject_id_role UNIQUE (principal_id, project_id, role)]
2023-11-15T09:50:02.054Z INFO project-service [host='project-service-app-6874f6469b-2m

Environment

VMware Aria Automation 8.x

Cause

The unique constraint errors are as a result of duplicate entries in the project service database.

Resolution

The issue is scheduled for resolution in the Aria Automation 8.16 release

Workaround:

To workaround the issue:

1. Snapshot each Aria Automation appliance as a best practice precaution

2. SSH to an Aria Automation appliance and connect to the postgres instance, specifically the project service database:

vracli dev psql project-db

3. Execute the query to remove any duplicates:

DELETE FROM project_principals WHERE id IN (
    -- entries that are 1:1 copies (both have user_id/group_id or both do not have ids)
    SELECT fst.id FROM project_principals fst
    JOIN project_principals snd ON fst.id > snd.id
        AND fst.principal_id = snd.principal_id
        AND fst.project_id = snd.project_id
        AND fst.role = snd.role
    WHERE fst.user_id = snd.user_id OR fst.group_id = snd.group_id
        OR (fst.user_id IS NULL AND fst.group_id IS NULL AND snd.user_id IS NULL AND snd.group_id IS NULL)

    UNION

    -- entries that differ in having user_id/group_id - the one that does not have id is chosen to be deleted
    SELECT fst.id FROM project_principals fst
    JOIN project_principals snd ON fst.id != snd.id
        AND fst.principal_id = snd.principal_id
        AND fst.project_id = snd.project_id
        AND fst.role = snd.role
    WHERE fst.user_id IS NULL AND fst.group_id IS NULL AND (snd.user_id IS NOT NULL OR snd.group_id IS NOT NULL)
);