Stalled BackfillProjectIdForProjectsWithPipelineVariables migration due to statement timeout
Summary
We recently upgraded our self-hosted omnibus instance from 17.10.4 to 17.11.1. Everything went smoothly except we have the BackfillProjectIdForProjectsWithPipelineVariables: p_ci_pipeline_variables background migration stuck at 0.00%
Relevant logs and/or screenshots
I tried finalizing with gitlab-rake on a staging copy which is behaving the same way:
$ sudo gitlab-rake gitlab:background_migrations:finalize[BackfillProjectIdForProjectsWithPipelineVariables,p_ci_pipeline_variables,project_id,[]]
rake aborted!
ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:107:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:107:in `block in read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:228:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:106:in `read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:48:in `select_all'
/opt/gitlab/embedded/service/gitlab-rails/gems/activerecord-gitlab/lib/active_record/gitlab_patches/rescue_from.rb:31:in `exec_queries'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:139:in `block in distinct_each_batch'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:132:in `step'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:132:in `distinct_each_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/batching_strategies/loose_index_scan_batching_strategy.rb:26:in `next_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:105:in `find_next_batch_range'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:94:in `find_or_create_next_batched_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:29:in `run_migration_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:146: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:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'
Caused by:
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:107:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:107:in `block in read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:228:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:106:in `read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:48:in `select_all'
/opt/gitlab/embedded/service/gitlab-rails/gems/activerecord-gitlab/lib/active_record/gitlab_patches/rescue_from.rb:31:in `exec_queries'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:139:in `block in distinct_each_batch'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:132:in `step'
/opt/gitlab/embedded/service/gitlab-rails/app/models/concerns/each_batch.rb:132:in `distinct_each_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/batching_strategies/loose_index_scan_batching_strategy.rb:26:in `next_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:105:in `find_next_batch_range'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:94:in `find_or_create_next_batched_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:29:in `run_migration_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_runner.rb:146: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:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'
Tasks: TOP => gitlab:background_migrations:finalize
(See full trace by running task with --trace)Our p_ci_pipeline_variables table has 3,639,789 rows.
Relevant logs and/or screenshots
Results of GitLab environment info
Expand for output related to GitLab environment info
$ sudo gitlab-rake gitlab:env:info System information System: Proxy: no Current User: git Using RVM: no Ruby Version: 3.2.5 Gem Version: 3.6.6 Bundler Version:2.6.5 Rake Version: 13.0.6 Redis Version: 7.2.7 Sidekiq Version:7.3.9 Go Version: unknown GitLab information Version: 17.11.1-ee Revision: cd4c186e8bc Directory: /opt/gitlab/embedded/service/gitlab-rails DB Adapter: PostgreSQL DB Version: 14.17 URL: https://git.code-staging.devdrupal.org HTTP Clone URL: https://git.code-staging.devdrupal.org/some-group/some-project.git SSH Clone URL: git@gitlabstg1-aws.drupalsystems.org:some-group/some-project.git Elasticsearch: yes Geo: yes Geo node: Primary Using LDAP: no Using Omniauth: yes Omniauth Providers: jwt GitLab Shell Version: 14.41.0 Repository storages: - default: unix:/var/opt/gitlab/gitaly/gitaly.socket GitLab Shell path: /opt/gitlab/embedded/service/gitlab-shell Gitaly - default Address: unix:/var/opt/gitlab/gitaly/gitaly.socket - default Version: 17.11.1 - default Git Version: 2.49.0.gl1
Results of GitLab application Check
Expand for output related to the GitLab application check
(For installations with omnibus-gitlab package run and paste the output of: \\\`sudo gitlab-rake gitlab:check SANITIZE=true\\\`)
All look like they are passing. Too many projects to paste, and the formatting seems to be getting mangled anyway.
Possible fixes
Edited by Neil Drumm