Error upgrading Omnibus install to 16.8

When upgrading my Omnibus installation to 16.8 I got migration errors when running gitlab-ctl reconfigure

If I later run gitlab-rails db:migrate I get the following error:

main: == [advisory_lock_connection] object_id: 114620, pg_backend_pid: 642074
main: == 20240108072319 AddFkToCiBuildTraceMetadataOnPartitionIdAndTraceArtifactId2: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE ci_build_trace_metadata VALIDATE CONSTRAINT fk_21d25cac1a_p;")
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == [advisory_lock_connection] object_id: 114620, pg_backend_pid: 642074
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::ForeignKeyViolation: ERROR:  insert or update on table "ci_build_trace_metadata" violates foreign key constraint "fk_21d25cac1a_p"
DETAIL:  Key (partition_id, trace_artifact_id)=(100, 139428) is not present in table "ci_job_artifacts".
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:290:in `block in add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/timeout_helpers.rb:31:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:289:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240108072319_add_fk_to_ci_build_trace_metadata_on_partition_id_and_trace_artifact_id2.rb:15:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:37:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:36:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'

Caused by:
ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  insert or update on table "ci_build_trace_metadata" violates foreign key constraint "fk_21d25cac1a_p"
DETAIL:  Key (partition_id, trace_artifact_id)=(100, 139428) is not present in table "ci_job_artifacts".
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:290:in `block in add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/timeout_helpers.rb:31:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:289:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240108072319_add_fk_to_ci_build_trace_metadata_on_partition_id_and_trace_artifact_id2.rb:15:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:37:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:36:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'

Caused by:
PG::ForeignKeyViolation: ERROR:  insert or update on table "ci_build_trace_metadata" violates foreign key constraint "fk_21d25cac1a_p"
DETAIL:  Key (partition_id, trace_artifact_id)=(100, 139428) is not present in table "ci_job_artifacts".
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:290:in `block in add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/timeout_helpers.rb:31:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:289:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240108072319_add_fk_to_ci_build_trace_metadata_on_partition_id_and_trace_artifact_id2.rb:15:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:37:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:36:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Also, in the Admin Area, I can see my instance as unhealthy and showing pending migrations.

Any hints?

I also ran into this error after upgrading. I had to roll back to a server backup. Does anyone have a solution for this error?

I solved it by manually deleting a couple of offending rows in my DB.

If you look closely at the error log, mine said:

PG::ForeignKeyViolation: ERROR: insert or update on table “ci_build_trace_metadata” violates foreign key constraint “fk_21d25cac1a_p”
DETAIL: Key (partition_id, trace_artifact_id)=(100, 139428) is not present in table “ci_job_artifacts”.

So I checked the table ci_build_trace_metadata and looked for the row with partition_id = 100 and trace_artifact_id = 139428 and deleted it.

After that, I failed once more, and I deleted an additional row. With those rows deleted, the migration run properly and I could complete the upgrade.

Thanks @pgb that was perfect! In my case, it affected more than two records. I wrote a query to track down the affected records, and it turned out to be only three.

SELECT a.partition_id, a.trace_artifact_id
  FROM ci_build_trace_metadata a
  LEFT OUTER JOIN ci_job_artifacts b ON a.partition_id = b.partition_id AND a.trace_artifact_id = b.id
  WHERE b.partition_id IS NULL AND b.id IS NULL
  AND a.trace_artifact_id IS NOT NULL;

It seems that Migration errors upgrading to 16.8 (#8386) · Issues · GitLab.org / omnibus-gitlab · GitLab describes the behavior. Please review and share your thoughts/workarounds into the issue, too. Thanks.

@dnsmichi it looks like that issue is already closed.