Background migration error

flf · June 28, 2023, 12:50pm

Installation: self-managed GitLab Omnibus
Version: 15.11.6

After multiple upgrades from 12.8.1 to 15.11.6 the Background Migration job “NullifyOrphanRunnerIdOnCiBuilds: ci_builds” has failed at some point and it is stuck running at 73.00%. After some time it fails and if I retry to execute that job it remains stuck at 73.00% and fails again.

This is the job running:

And this is the message showed:

Do you know any solution for this?

flf · June 29, 2023, 11:37am

After some research and trying to finalize manually the migration the following error message is showed:

WARNING: Active Record does not support composite primary key.

ci_builds has composite primary key. Composite primary key is ignored.
rake aborted!
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  zero-length delimited identifier at or near """"
LINE 1: ...COALESCE("lock_version", 0) + 1 WHERE "ci_builds"."" IN (SEL...
                                                             ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:127:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:198:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:116:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:62:in `block (2 levels) in <class:ConnectionProxy>'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/nullify_orphan_runner_id_on_ci_builds.rb:15:in `block (2 levels) in perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batch_metrics.rb:22:in `instrument_operation'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batch_metrics.rb:16:in `time_operation'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/nullify_orphan_runner_id_on_ci_builds.rb:14:in `block in perform'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:99:in `block in each_batch'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:69:in `step'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:69:in `each_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/nullify_orphan_runner_id_on_ci_builds.rb:13:in `perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:89:in `execute_legacy_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:65:in `execute_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:50:in `execute_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:25:in `perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:30:in `run_migration_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:140:in `run_migration_while'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:80:in `finalize'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:10:in `finalize'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/background_migrations.rake:72:in `finalize_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/background_migrations.rake:18:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'

Caused by:
PG::SyntaxError: ERROR:  zero-length delimited identifier at or near """"
LINE 1: ...COALESCE("lock_version", 0) + 1 WHERE "ci_builds"."" IN (SEL...
                                                             ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:127:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:198:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:116:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:62:in `block (2 levels) in <class:ConnectionProxy>'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/nullify_orphan_runner_id_on_ci_builds.rb:15:in `block (2 levels) in perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batch_metrics.rb:22:in `instrument_operation'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batch_metrics.rb:16:in `time_operation'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/nullify_orphan_runner_id_on_ci_builds.rb:14:in `block in perform'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:99:in `block in each_batch'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:69:in `step'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:69:in `each_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/nullify_orphan_runner_id_on_ci_builds.rb:13:in `perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:89:in `execute_legacy_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:65:in `execute_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:50:in `execute_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:25:in `perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:30:in `run_migration_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:140:in `run_migration_while'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:80:in `finalize'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:10:in `finalize'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/background_migrations.rake:72:in `finalize_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/background_migrations.rake:18:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => gitlab:background_migrations:finalize

flf · June 30, 2023, 10:37am

After finding no solution because it seems like the command is written using double quotes over an empty string I solved it truncating the ci_build tables and executing the finalize command for that migration:

gitlab-rake gitlab:background_migrations:finalize[NullifyOrphanRunnerIdOnCiBuilds,ci_builds,id,[]]

mbobin · July 5, 2023, 9:01am

Data removal is dangerous since it can remove rows from other tables and leave artifact files on disk, a safer approach is to mark the migration as successfully executed: Batched background NullifyOrphanRunnerIdOnCiBuilds is failing to complete (#415724) · Issues · GitLab.org / GitLab · GitLab

flf · July 5, 2023, 9:16am

And how can I do it using gitlab-rake commands? I cannot see the way of doing it in the link you’ve sent.

Thanks for your answer !

mbobin · July 5, 2023, 9:31am

For this you need to access the rails console, it can’t be done using rake commands.

asifmohammed.infanio · August 9, 2023, 9:56pm

@flf can you please send me the steps how you resolved it!

flf · August 11, 2023, 12:39pm

What I did is something considered as a bad practice as @mbobin said, but I simply truncate the ci_build table, removing all the rows, and after that, executing the following Rake command:

gitlab-rake gitlab:background_migrations:finalize[NullifyOrphanRunnerIdOnCiBuilds,ci_builds,id,[]]

mbobin · August 11, 2023, 1:04pm

You can skip this migration by running this snippet in the rails console:

# Start the rails console

connection = Ci::ApplicationRecord.connection

Gitlab::Database::SharedModel.using_connection(connection) do
  migration = Gitlab::Database::BackgroundMigration::BatchedMigration.find_for_configuration(
    Gitlab::Database.gitlab_schemas_for_connection(connection), 'NullifyOrphanRunnerIdOnCiBuilds', :ci_builds, :id, [])

  # mark all jobs completed
  migration.batched_jobs.update_all(status: Gitlab::Database::BackgroundMigration::BatchedJob.state_machine.states[:succeeded].value)
  migration.update_attribute(:status, Gitlab::Database::BackgroundMigration::BatchedMigration.state_machine.states[:finished].value)
end

Copied from Batched background NullifyOrphanRunnerIdOnCiBuilds is failing to complete (#415724) · Issues · GitLab.org / GitLab · GitLab