IDSP upgrade fails with error "Table 'T_MACHINES' already exists" after improper downgrade

Products

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

Issue/Introduction

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

Message : (conn=########) Table 'T_MACHINES' already exists Location : db/migration/mysql/V1.217__AddMachines.sql

Environment

• Product: Symantec Identity Security Platform (formerly VIP Authentication Hub)
• Version: 3.4.7 and lower

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., version 3.4.2) 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. Starting with version 3.4.8, protections are in place to help prevent this state.

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.