Upgrade to Aria Operations 8.12 failing due to invalid JSON metadata in kv_actionschedules table

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

1. Aria Operations Admin UI System Update page shows failure on primary node at step 9: Failedresource key=pak_manager.action_failed, resource args=[run master postgres db upgrade]
2. Primary node /storage/log/vcops/log/centraldbupgrade.log contains errors like:

2023-05-25 15:29:46,730 INFO [main] .processJAVA - Invoking upgrade() method of class: com.vmware.vcops.dbupgrade.postgres.centraldb.upgrade.v872.ActionScheduleDataUpgrader
2023-05-25 15:29:46,730 INFO [main] com.vmware.vcops.dbupgrade.postgres.centraldb.upgrade.v872.ActionScheduleDataUpgrader.upgrade ActionScheduleDataUpgrader start
2023-05-25 15:29:46,911 INFO [main] com.vmware.vcops.dbupgrade.postgres.centraldb.upgrade.v872.ActionScheduleDataUpgrader.upgrade - There are 3 action schedules to be moved to new table.
2023-05-25 15:29:46,912 ERROR [main] .processJAVA - error:java.lang.NullPointerException: null at com.vmware.vcops.dbupgrade.postgres.centraldb.upgrade.v872.ActionScheduleDataUpgrader.getScheduleDefinition(ActionScheduleDataUpgrader.java:103) ~[vcops-dbupgrade-1.0-SNAPSHOT.jar:?]

 

Environment

VMware Aria Operations 8.12.x

Cause

The JSON column in postgres replication db kv_actionschedules table is missing a required "settings" child object. When the upgrade executes ActionScheduleDataUpgrader to migrate this data to a new table, a NullPointerException is thrown when it tries to read the invalid JSON into a java object.

Resolution

1. Execute a SQL query to show kv_actionschedules rows that are missing the "settings" child JSON attribute.

su - postgres -c "/opt/vmware/vpostgres/current/bin/psql -d vcopsdb -p 5433 -x -c \"SELECT * FROM kv_actionschedules WHERE NOT (col__kv_strvalue::jsonb)->'com.vmware.vcops.platform.api.model.action.ActionSchedule' ? 'settings';\""

2. Export the existing kv_actionschedules table to a CSV as a backup

su - postgres -c "/opt/vmware/vpostgres/current/bin/psql -d vcopsdb -p 5433 -c \"COPY kv_actionschedules FROM '/tmp/kv_actionschedules.csv' WITH (FORMAT CSV, HEADER TRUE);\""

3. Before upgrade, delete the broken action schedules from the UI
     a. Navigate to "Optimize > Workload Placement > Optimization Schedules"
     b. Click "Delete" in the context menu for rows whose name matches the "bad" rows found in Step (1) above
        OR
        Delete the broken rows from the command line on the primary node:

su - postgres -c "/opt/vmware/vpostgres/current/bin/psql -d vcopsdb -p 5433 -c \"DELETE FROM kv_actionschedules WHERE NOT (col__kv_strvalue::jsonb)->'com.vmware.vcops.platform.api.model.action.ActionSchedule' ? 'settings';\""

Additional Information

Impact/Risks:

The NullPointerException causes the upgrades to Aria Operations (AOPs) 8.12.x to fail.