Fixing Flyway Migration After OSS-Conductor Upgrade (3.21.4+)

Conductor OSS
,
1

Problem: Upgrading OSS-Conductor from version 3.21.4 to a newer version (e.g., 3.21.12 or similar) results in a Flyway migration error: “Validate failed: Migrations have failed validation”

Caused by: org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
conductor-server             | Detected applied migration not resolved locally: 13.
conductor-server             | If you removed this migration intentionally, run repair to mark the migration as deleted.
conductor-server             | Need more flexibility with validation rules? Learn more: https://rd.gt/3AbJUZE
conductor-server             |  at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:160) ~[flyway-core-10.10.0.jar!/:?]
conductor-server             |  at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:205) ~[flyway-core-10.10.0.jar!/:?]
conductor-server             |  at org.flywaydb.core.Flyway.migrate(Flyway.java:147) ~[flyway-core-10.10.0.jar!/:?]
conductor-server             |  at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
conductor-server             |  at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
conductor-server             |  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
conductor-server             |  at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[?:?]

My setup uses postgres 16 as database.

Cause:
In short issue is that, migration V13__workflow_index_columns.sql is applied in the database but missing from the new version’s migration scripts. This prevents the server from starting.
The newer OSS-Conductor version utilizes updated migration scripts (V13.1__workflow_index_columns.sql, V13.2__workflow_index_backfill_update_time.sql) and has removed or replaced the original V13__workflow_index_columns.sql. Flyway detects this discrepancy, leading to the validation error.

Detailed Steps to Resolve the Migration Issue:

  1. Access Your Database:

    • Use a database client (e.g., pgAdmin, DBeaver, command-line psql) to connect to the PostgreSQL database used by your OSS-Conductor.

  2. Inspect the Flyway Schema History:

    • Execute the following SQL query to view the Flyway migration history:
      SELECT * FROM flyway_schema_history;
    • Examine the results. Look for an entry with script column value V13__workflow_index_columns.sql. Note the installed_rank of this entry.

  3. Verify Missing Migration File:

    • Navigate to the postgres-persistence/src/main/resources/db/migration_postgres directory within your upgraded OSS-Conductor codebase.
    • Confirm that the V13__workflow_index_columns.sql file is absent.
    • Confirm the presence of V13.1__workflow_index_columns.sql, and V13.2__workflow_index_backfill_update_time.sql. For version other than (3.21.12), you might not have these.

  4. Delete the Conflicting Migration Record:

    • Execute the following SQL query, replacing <installed_rank> with the value you noted earlier:
      DELETE FROM flyway_schema_history WHERE installed_rank = <installed_rank>;
    • This removes the record for the missing migration from the Flyway history.

  5. Restart the OSS-Conductor Server:

    • Restart your OSS-Conductor application.
    • Flyway will now attempt to apply the remaining migrations, including V13.1 and V13.2.

  6. Verify Successful Migration:

    • Reconnect to your database and execute the SELECT * FROM flyway_schema_history; query again.
    • Confirm that the V13__workflow_index_columns.sql entry is gone.
    • Confirm that V13.1__workflow_index_columns.sql and V13.2__workflow_index_backfill_update_time.sql entries are present.
    • Check your OSS-Conductor logs for any errors. If the server starts successfully, the issue is resolved.

Key Points:

  • This problem arises when a previously applied migration is removed or altered in a newer version of OSS-Conductor.
  • Deleting the corresponding record from flyway_schema_history synchronizes the database’s migration history with the available migration scripts.
  • Always back up your database before performing any modifications.
  • If you are unsure of any step, contact your database administrator, or the OSS-Conductor support team.

A special thank you to Jeff Bull for his insightful guidance, which was instrumental in pinpointing the root cause of this migration issue and leading to its swift resolution.

1 Like