FYI, if upgrading from an older build, there is a new flyway migration that requires outOfOrder to be set

[arketec]

Recently, this repo was updated to start using conductor 13 and postgres-persistance now includes a new Flyway migration script:

https://github.com/Netflix/conductor-community/blob/main/persistence/postgres-persistence/src/main/resources/db/migration_postgres/V8__indexing.sql

since this version of conductor uses Orkes' postgres archive, it uses a Flyway migration here:

https://github.com/orkes-io/orkes-conductor-community/blob/main/archive/src/main/resources/db/migration_archive_postgres/V99__initial_schema.sql

This is what causes the issue:

org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected resolved migration not applied to database: 8. To ignore this migration, set -ignoreIgnoredMigrations=true. To allow executing this migration, set -outOfOrder=true.

here is what my Flyway migration history looked like before the update:

 installed_rank | version |             description             | type |                   script                    |  checksum   | installed_by |        installed_on        | execution_time | success 
----------------+---------+-------------------------------------+------+---------------------------------------------+-------------+--------------+----------------------------+----------------+---------
              1 | 1       | initial schema                      | SQL  | V1__initial_schema.sql                      |  1899847952 | postgres     | 2023-05-04 21:25:58.964352 |            246 | t
              2 | 2       | 1009 Fix PostgresExecutionDAO Index | SQL  | V2__1009_Fix_PostgresExecutionDAO_Index.sql | -1672007158 | postgres     | 2023-05-04 21:25:59.229008 |              7 | t
              3 | 3       | correlation id index                | SQL  | V3__correlation_id_index.sql                | -1477530519 | postgres     | 2023-05-04 21:25:59.246972 |             13 | t
              4 | 4       | new qm index with priority          | SQL  | V4__new_qm_index_with_priority.sql          |     6887503 | postgres     | 2023-05-04 21:25:59.267559 |             11 | t
              5 | 5       | new queue message pk                | SQL  | V5__new_queue_message_pk.sql                |  1638999284 | postgres     | 2023-05-04 21:25:59.288754 |             11 | t
              6 | 6       | update pk                           | SQL  | V6__update_pk.sql                           |   -88978710 | postgres     | 2023-05-04 21:25:59.317638 |             90 | t
              7 | 7       | new qm index desc priority          | SQL  | V7__new_qm_index_desc_priority.sql          |   259619550 | postgres     | 2023-05-04 21:25:59.416477 |              9 | t
              8 | 99      | initial schema                      | SQL  | V99__initial_schema.sql                     |   440005581 | postgres     | 2023-05-04 21:25:59.433166 |             34 | t
(8 rows)

There migration V8 is not there as it was added after we deployed orkes-conductor-community.

To fix this, i first tried to add this to docker/config/config.properties:

spring.flyway.out-of-order=true

But that did not seem to work, we still got the same error even with that setting applied.

I tried other settings to no avail, and finally tried adding that setting to this file here:
https://github.com/orkes-io/orkes-conductor-community/blob/main/archive/src/main/java/io/orkes/conductor/dao/postgres/archive/PostgresArchiveDAOConfiguration.java

public Flyway flywayForPrimaryDb() {
        return Flyway.configure()
                .locations(
                        "classpath:db/migration_postgres",
                        "classpath:db/migration_archive_postgres")
                .schemas("public")
                .dataSource(dataSource)
                .baselineOnMigrate(true)
                .outOfOrder(true) // added this line here
                .mixed(true)
                .load();
    }

This worked, but it feels a little hacky, but since the new migration does not change anything related to V99, it won't harm anything

Now, the migration history after the update:

 installed_rank | version |             description             | type |                   script                    |  checksum   | installed_by |        installed_on        | execution_time | success 
----------------+---------+-------------------------------------+------+---------------------------------------------+-------------+--------------+----------------------------+----------------+---------
              1 | 1       | initial schema                      | SQL  | V1__initial_schema.sql                      |  1899847952 | postgres     | 2023-05-04 21:25:58.964352 |            246 | t
              2 | 2       | 1009 Fix PostgresExecutionDAO Index | SQL  | V2__1009_Fix_PostgresExecutionDAO_Index.sql | -1672007158 | postgres     | 2023-05-04 21:25:59.229008 |              7 | t
              3 | 3       | correlation id index                | SQL  | V3__correlation_id_index.sql                | -1477530519 | postgres     | 2023-05-04 21:25:59.246972 |             13 | t
              4 | 4       | new qm index with priority          | SQL  | V4__new_qm_index_with_priority.sql          |     6887503 | postgres     | 2023-05-04 21:25:59.267559 |             11 | t
              5 | 5       | new queue message pk                | SQL  | V5__new_queue_message_pk.sql                |  1638999284 | postgres     | 2023-05-04 21:25:59.288754 |             11 | t
              6 | 6       | update pk                           | SQL  | V6__update_pk.sql                           |   -88978710 | postgres     | 2023-05-04 21:25:59.317638 |             90 | t
              7 | 7       | new qm index desc priority          | SQL  | V7__new_qm_index_desc_priority.sql          |   259619550 | postgres     | 2023-05-04 21:25:59.416477 |              9 | t
              8 | 99      | initial schema                      | SQL  | V99__initial_schema.sql                     |   440005581 | postgres     | 2023-05-04 21:25:59.433166 |             34 | t
              9 | 8       | indexing                            | SQL  | V8__indexing.sql                            | -1748499383 | postgres     | 2023-09-28 23:16:34.612867 |            161 | t
(9 rows)

and conductor server loads correctly