IDSP upgrade fails with error "Duplicate column name 'ACCESS_TYPE'" after improper downgrade

Products

• Symantec Identity Security Platform - IDSP (formerly VIP Authentication Hub)

Issue/Introduction

When you attempt to upgrade IDSP from 4.0.1 to version 4.0.3 or higher, the ssp-ssp-dataseed-job fails. The logs for the ssp-db-init container show a FlywayMigrateException similar to the following:

SQL State  : 42S21
Error Code : 1060
Message    : (conn=719422) Duplicate column name 'ACCESS_TYPE'
Location   : db/migration/mysql/V1.293__AddVanityHostAccessType.sql (/opt/nested:/opt/app/ssp-db-initializer.jar/!BOOT-INF/classes/!/db/migration/mysql/V1.293__AddVanityHostAccessType.sql)
Line       : 5
Statement  : -- Add ACCESS_TYPE column to G_VANITY_HOST table 
                    -- Values: 'FULL' (all APIs) or 'ENDUSER' (restricted to end-user APIs)
                    -- Default: 'ENDUSER' (secure by default)

                    ALTER TABLE G_VANITY_HOST
                    ADD COLUMN ACCESS_TYPE VARCHAR(20) NOT NULL DEFAULT 'ENDUSER'

Environment

• Product: Symantec Identity Security Platform (formerly VIP Authentication Hub)
• Version: 4.0.1

 

Cause

This issue occurs if you previously attempt to downgrade the environment by running a helm upgrade command using an older version's details (e.g., from version 4.0.2 to 4.0.1) instead of using helm rollback.

When you "upgrade" to a lower version, Flyway marks migrations that do not exist in the older version as DELETED in the flyway_schema_history table. However, Flyway does not physically drop the tables or undo the schema changes. Consequently, when you attempt a legitimate upgrade to a newer version, Flyway tries to re-run the "deleted" scripts and fails because the database objects already exist.

 

Resolution

To prevent this issue, do not attempt to downgrade by "upgrading" to an older version.

If you are already in this state, perform the following steps to workaround the upgrade failure:

1. Remove Erroneous Delete Records: Clean up the DELETE entries inserted during the improper downgrade attempt:
   
   DELETE FROM flyway_schema_history WHERE type = 'DELETE' AND success = 1;
   
   
2. Delete the Failed Job Pod: Remove the existing error-ed data seeding pod to allow the deployment to retry: 
   
   kubectl delete pod [pod-name] -n [namespace]


3. Rerun the Upgrade: Execute your helm upgrade command again.