Failing migration v0.44.0 -> v0.57.1

[killthekitten]

We are upgrading from an older version with a few breaking changes along the way, and encountered an error in V0_44_4_002__MigrateNonBreakingChangeToEnum migration that stops the bootloader process:

SQL [select id from connection where non_breaking_change_preference = cast(? as "non_breaking_change_preference_type")]; ERROR: operator does not exist: character varying = non_breaking_change_preference_type

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

It comes from a typecast in migrateExistingDisableConnection.

The error comes up in a staging environment where we installed v0.44.0 from scratch, added a couple connectors and switched to v0.57.1, so the DB is as fresh and untouched as it can get. The DB runs GCP Cloud SQL, this is PostgreSQL 14.10.

Update: same effect on 13.10.

airbyte-bootloader                | 2024-04-04 16:14:19 INFO o.f.c.i.l.s.Slf4jLog(info):37 - Successfully validated 111 migrations (execution time 00:00.052s)
airbyte-bootloader                | 2024-04-04 16:14:19 INFO o.f.c.i.l.s.Slf4jLog(info):37 - Current version of schema "public": 0.44.4.001
airbyte-bootloader                | 2024-04-04 16:14:19 INFO o.f.c.i.l.s.Slf4jLog(info):37 - Migrating schema "public" to version "0.44.4.002 - MigrateNonBreakingChangeToEnum"
airbyte-bootloader                | 2024-04-04 16:14:19 ERROR o.f.c.i.l.s.Slf4jLog(error):45 - Migration of schema "public" to version "0.44.4.002 - MigrateNonBreakingChangeToEnum" failed! Changes successfully rolled back.
airbyte-bootloader                | 2024-04-04 16:14:19 ERROR i.a.b.Application(main):25 - Unable to bootstrap Airbyte environment.
airbyte-bootloader                | org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration failed !
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:382) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:272) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:55) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:271) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:139) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:73) ~[flyway-database-postgresql-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.database.postgresql.PostgreSQLAdvisoryLockTemplate.lambda$execute$0(PostgreSQLAdvisoryLockTemplate.java:56) ~[flyway-database-postgresql-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:55) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:56) ~[flyway-database-postgresql-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:96) ~[flyway-database-postgresql-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:144) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:139) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:97) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:202) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:200) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.Flyway.migrate(Flyway.java:147) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at io.airbyte.db.instance.FlywayDatabaseMigrator.migrate(FlywayDatabaseMigrator.java:45) ~[io.airbyte.airbyte-db-db-lib-0.57.1.jar:?]
airbyte-bootloader                |     at io.airbyte.bootloader.Bootloader.runFlywayMigration(Bootloader.java:270) ~[io.airbyte-airbyte-bootloader-0.57.1.jar:?]
airbyte-bootloader                |     at io.airbyte.bootloader.Bootloader.load(Bootloader.java:117) ~[io.airbyte-airbyte-bootloader-0.57.1.jar:?]
airbyte-bootloader                |     at io.airbyte.bootloader.Application.main(Application.java:22) [io.airbyte-airbyte-bootloader-0.57.1.jar:?]
airbyte-bootloader                | Caused by: org.flywaydb.core.api.FlywayException: Migration failed !
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.executeOnce(JavaMigrationExecutor.java:76) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.lambda$execute$0(JavaMigrationExecutor.java:54) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.execute(JavaMigrationExecutor.java:53) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:374) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     ... 20 more
airbyte-bootloader                | Caused by: org.jooq.exception.DataAccessException: SQL [select id from connection where non_breaking_change_preference = cast(? as "non_breaking_change_preference_type")]; ERROR: operator does not exist: character varying = non_breaking_change_preference_type
airbyte-bootloader                |   Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
airbyte-bootloader                |   Position: 64
airbyte-bootloader                |     at org.jooq_3.19.1.POSTGRES.debug(Unknown Source) ~[?:?]
airbyte-bootloader                |     at org.jooq.impl.Tools.translate(Tools.java:3528) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.Tools.translate(Tools.java:3516) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:812) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:362) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:301) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.SelectImpl.fetchLazy(SelectImpl.java:3251) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.ResultQueryTrait.lambda$fetchStream$1(ResultQueryTrait.java:329) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at java.base/java.util.stream.AbstractPipeline.sourceSpliterator(AbstractPipeline.java:405) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.AbstractPipeline.evaluateToArrayNode(AbstractPipeline.java:260) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.ReferencePipeline.toArray(ReferencePipeline.java:616) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.ReferencePipeline.toArray(ReferencePipeline.java:622) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.ReferencePipeline.toList(ReferencePipeline.java:627) ~[?:?]
airbyte-bootloader                |     at io.airbyte.db.instance.configs.migrations.V0_44_4_002__MigrateNonBreakingChangeToEnum.migrateExistingDisableConnection(V0_44_4_002__MigrateNonBreakingChangeToEnum.java:101) ~[io.airbyte.airbyte-db-db-lib-0.57.1.jar:?]
airbyte-bootloader                |     at io.airbyte.db.instance.configs.migrations.V0_44_4_002__MigrateNonBreakingChangeToEnum.migrate(V0_44_4_002__MigrateNonBreakingChangeToEnum.java:60) ~[io.airbyte.airbyte-db-db-lib-0.57.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.executeOnce(JavaMigrationExecutor.java:62) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.lambda$execute$0(JavaMigrationExecutor.java:54) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.execute(JavaMigrationExecutor.java:53) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:374) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     ... 20 more
airbyte-bootloader                | Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = non_breaking_change_preference_type
airbyte-bootloader                |   Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
airbyte-bootloader                |   Position: 64
airbyte-bootloader                |     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712) ~[postgresql-42.7.1.jar:42.7.1]
airbyte-bootloader                |     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400) ~[postgresql-42.7.1.jar:42.7.1]
airbyte-bootloader                |     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367) ~[postgresql-42.7.1.jar:42.7.1]
airbyte-bootloader                |     at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) ~[postgresql-42.7.1.jar:42.7.1]
airbyte-bootloader                |     at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) ~[postgresql-42.7.1.jar:42.7.1]
airbyte-bootloader                |     at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.7.1.jar:42.7.1]
airbyte-bootloader                |     at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177) ~[postgresql-42.7.1.jar:42.7.1]
airbyte-bootloader                |     at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.1.0.jar:?]
airbyte-bootloader                |     at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:?]
airbyte-bootloader                |     at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4848) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:236) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:301) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.SelectImpl.fetchLazy(SelectImpl.java:3251) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at org.jooq.impl.ResultQueryTrait.lambda$fetchStream$1(ResultQueryTrait.java:329) ~[jooq-3.19.1.jar:?]
airbyte-bootloader                |     at java.base/java.util.stream.AbstractPipeline.sourceSpliterator(AbstractPipeline.java:405) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.AbstractPipeline.evaluateToArrayNode(AbstractPipeline.java:260) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.ReferencePipeline.toArray(ReferencePipeline.java:616) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.ReferencePipeline.toArray(ReferencePipeline.java:622) ~[?:?]
airbyte-bootloader                |     at java.base/java.util.stream.ReferencePipeline.toList(ReferencePipeline.java:627) ~[?:?]
airbyte-bootloader                |     at io.airbyte.db.instance.configs.migrations.V0_44_4_002__MigrateNonBreakingChangeToEnum.migrateExistingDisableConnection(V0_44_4_002__MigrateNonBreakingChangeToEnum.java:101) ~[io.airbyte.airbyte-db-db-lib-0.57.1.jar:?]
airbyte-bootloader                |     at io.airbyte.db.instance.configs.migrations.V0_44_4_002__MigrateNonBreakingChangeToEnum.migrate(V0_44_4_002__MigrateNonBreakingChangeToEnum.java:60) ~[io.airbyte.airbyte-db-db-lib-0.57.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.executeOnce(JavaMigrationExecutor.java:62) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.lambda$execute$0(JavaMigrationExecutor.java:54) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.execute(JavaMigrationExecutor.java:53) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:374) ~[flyway-core-10.7.1.jar:?]
airbyte-bootloader                |     ... 20 more
airbyte-bootloader                | INFO StatusConsoleListener Loading mask data from '/seed/specs_secrets_mask.yaml
airbyte-bootloader                | WARN StatusConsoleListener Unable to register Log4j shutdown hook because JVM is shutting down. Using SimpleLogger

[killthekitten]

Closed in favour of #36855.