Failing background migrations

Hi,

after upgrading gitlab-ce from 15.3.2 to 15.11.0 via RPM on RHEL8, I have two failing background migrations.

CopyColumnUsingBackgroundMigrationJob: ci_build_needs

CopyColumnUsingBackgroundMigrationJob: sent_notifications

Both are more or less failing with a similar error:

ERROR:  column \"id_convert_to_bigint\" of relation \"ci_build_needs\" does not exist\nLINE 1: ...db_config_name:main*/ UPDATE \"ci_build_needs\" SET \"id_conver...\n                                                             ^\n","exception.backtrace":["lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `public_send'","lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `block in write_using_load_balancer'","lib/gitlab/database/load_balancing/load_balancer.rb:127:in `block in read_write'","lib/gitlab/database/load_balancing/load_balancer.rb:198:in `retry_with_backoff'","lib/gitlab/database/load_balancing/load_balancer.rb:116:in `read_write'","lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `write_using_load_balancer'","lib/gitlab/database/load_balancing/connection_proxy.rb:62:in `block (2 levels) in \u003cclass:ConnectionProxy\u003e'","lib/gitlab/background_migration/copy_column_using_background_migration_job.rb:25:in `block in perform'","lib/gitlab/background_migration/batched_migration_job.rb:105:in `block (2 levels) in each_sub_batch'","lib/gitlab/database/background_migration/batch_metrics.rb:22:in `instrument_operation'","lib/gitlab/background_migration/batched_migration_job.rb:104:in `block in each_sub_batch'","app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'","app/models/concerns/each_batch.rb:99:in `block in each_batch'","app/models/concerns/each_batch.rb:69:in `step'","app/models/concerns/each_batch.rb:69:in `each_batch'","lib/gitlab/background_migration/batched_migration_job.rb:103:in `each_sub_batch'","lib/gitlab/background_migration/copy_column_using_background_migration_job.rb:24:in `perform'","lib/gitlab/database/background_migration/batched_migration_wrapper.rb:81:in `execute_batched_migration_job'","lib/gitlab/database/background_migration/batched_migration_wrapper.rb:63:in `execute_job'","lib/gitlab/database/background_migration/batched_migration_wrapper.rb:50:in `execute_batch'","lib/gitlab/database/background_migration/batched_migration_wrapper.rb:25:in `perform'","lib/gitlab/database/background_migration/batched_migration_runner.rb:30:in `run_migration_job'","app/workers/database/batched_background_migration/execution_worker.rb:103:in `run_migration_job'","app/workers/database/batched_background_migration/execution_worker.rb:56:in `block (2 levels) in perform_work'","app/services/concerns/exclusive_lease_guard.rb:29:in `try_obtain_lease'","app/workers/database/batched_background_migration/execution_worker.rb:55:in `block in perform_work'","lib/gitlab/database/shared_model.rb:34:in `using_connection'","app/workers/database/batched_background_migration/execution_worker.rb:50:in `perform_work'","app/workers/concerns/limited_capacity/worker.rb:94:in `perform_registered'","app/workers/concerns/limited_capacity/worker.rb:65:in `perform'","lib/gitlab/database/load_balancing/sidekiq_server_middleware.rb:26:in `call'","lib/gitlab/sidekiq_middleware/duplicate_jobs/strategies/none.rb:14:in `perform'","lib/gitlab/sidekiq_middleware/duplicate_jobs/duplicate_job.rb:44:in `perform'","lib/gitlab/sidekiq_middleware/duplicate_jobs/server.rb:8:in `call'","lib/gitlab/sidekiq_middleware/worker_context.rb:9:in `wrap_in_optional_context'","lib/gitlab/sidekiq_middleware/worker_context/server.rb:19:in `block in call'","lib/gitlab/application_context.rb:118:in `block in use'","lib/gitlab/application_context.rb:118:in `use'","lib/gitlab/application_context.rb:57:in `with_context'","lib/gitlab/sidekiq_middleware/worker_context/server.rb:17:in `call'","lib/gitlab/sidekiq_status/server_middleware.rb:7:in `call'","lib/gitlab/sidekiq_versioning/middleware.rb:9:in `call'","lib/gitlab/sidekiq_middleware/query_analyzer.rb:7:in `block in call'","lib/gitlab/database/query_analyzer.rb:37:in `within'","lib/gitlab/sidekiq_middleware/query_analyzer.rb:7:in `call'","lib/gitlab/sidekiq_middleware/admin_mode/server.rb:14:in `call'","lib/gitlab/sidekiq_middleware/instrumentation_logger.rb:9:in `call'","lib/gitlab/sidekiq_middleware/batch_loader.rb:7:in `call'","lib/gitlab/sidekiq_middleware/extra_done_log_metadata.rb:7:in `call'","lib/gitlab/sidekiq_middleware/request_store_middleware.rb:10:in `block in call'","lib/gitlab/with_request_store.rb:17:in `enabling_request_store'","lib/gitlab/with_request_store.rb:10:in `with_request_store'","lib/gitlab/sidekiq_middleware/request_store_middleware.rb:9:in `call'","lib/gitlab/sidekiq_middleware/server_metrics.rb:76:in `block in call'","lib/gitlab/sidekiq_middleware/server_metrics.rb:103:in `block in instrument'","lib/gitlab/metrics/background_transaction.rb:33:in `run'","lib/gitlab/sidekiq_middleware/server_metrics.rb:103:in `instrument'","lib/gitlab/sidekiq_middleware/server_metrics.rb:75:in `call'","lib/gitlab/sidekiq_middleware/monitor.rb:10:in `block in call'","lib/gitlab/sidekiq_daemon/monitor.rb:46:in `within_job'","lib/gitlab/sidekiq_middleware/monitor.rb:9:in `call'","lib/gitlab/sidekiq_middleware/size_limiter/server.rb:13:in `call'","lib/gitlab/sidekiq_logging/structured_logger.rb:21:in `call'"],"exception.cause_class":"PG::UndefinedColumn","exception.sql":"/*application:sidekiq,correlation_id:fa6c494b55c29dbdd841b55804c0f315,jid:0019fe071b838484e1642637,endpoint_id:Database::BatchedBackgroundMigration::MainExecutionWorker,db_config_name:main*/ UPDATE \"ci_build_needs\" SET \"id_convert_to_bigint\" = \"id\" WHERE \"ci_build_needs\".\"id\" BETWEEN $1 AND $2 AND \"ci_build_needs\".\"id\" \u003e= $3","db_duration_s":0.036358}

I am not sure why the column id_convert_to_bigint never got created.
How would I go about fixing this though?

Thanks
Kristian

Same problem with “sent_notifications” here after upgrade from 15.6.x to 15.11.x.

Could this change here be related? But that’s already merged?

Exactly the same problem after upgrade from 15.6 → 15.11.
The same failed migrations “ci_build_needs” and “sent_notifications” and the same error.

One of the most important questions - does it have any impact on the working processes?

Hi,

from what I have noticed is that the migrations got canceled due to the scan_file_path issue while upgrading to 15.11.0:

`Running handlers complete
[2023-04-24T05:44:43-04:00] ERROR: Exception handlers complete
Infra Phase failed. 5 resources updated in 02 minutes 49 seconds
[2023-04-24T05:44:43-04:00] FATAL: Stacktrace dumped to /opt/gitlab/embedded/cookbooks/cache/cinc-stacktrace.out
[2023-04-24T05:44:43-04:00] FATAL: ---------------------------------------------------------------------------------------
[2023-04-24T05:44:43-04:00] FATAL: PLEASE PROVIDE THE CONTENTS OF THE stacktrace.out FILE (above) IF YOU FILE A BUG REPORT
[2023-04-24T05:44:43-04:00] FATAL: ---------------------------------------------------------------------------------------
[2023-04-24T05:44:43-04:00] FATAL: Mixlib::ShellOut::ShellCommandFailed: rails_migration[gitlab-rails] (gitlab::database_migrations line 51) had an error: Mixlib::ShellOut::ShellCommandFailed: bash_hide_env[migrate gitlab-rails database] (gitlab::database_migrations line 18) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received ‘1’
---- Begin output of “bash” ----
STDOUT: rake aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::UndefinedColumn: ERROR: column “scan_file_path” does not exist
`
Not sure if this is unique to our setup or if this failed for everyone.
Also not sure how to rerun the canceled migrations.

Thanks

Hi,
when upgrading from 15.8.2 to 15.11.0, migration problems

select job_class_name, table_name, column_name, job_arguments from batched_background_migrations where status <> 3;

            job_class_name             |     table_name     | column_name |           job_arguments
---------------------------------------+--------------------+-------------+------------------------------------
 CopyColumnUsingBackgroundMigrationJob | sent_notifications | id          | [["id"], ["id_convert_to_bigint"]]
 CopyColumnUsingBackgroundMigrationJob | ci_build_needs     | id          | [["id"], ["id_convert_to_bigint"]]
(2 rows)

Attempting to complete the migration with gitlab-rake gitlab:background_migrations:finalize[CopyColumnUsingBackgroundMigrationJob,sent_notifications,id,'[["id"]\, ["id_convert_to_bigint"]]'] produces the following message:

rake aborted!
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "id_convert_to_bigint" of relation "sent_notifications" does not exist
LINE 1: ...onfig_name:main*/ UPDATE "sent_notifications" SET "id_conver...
                                                             ^
/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/copy_column_using_background_migration_job.rb:25:in `block in perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/batched_migration_job.rb:105:in `block (2 levels) in each_sub_batch'
/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/background_migration/batched_migration_job.rb:104:in `block in each_sub_batch'
/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/batched_migration_job.rb:103:in `each_sub_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/copy_column_using_background_migration_job.rb:24:in `perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:81:in `execute_batched_migration_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:63: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::UndefinedColumn: ERROR:  column "id_convert_to_bigint" of relation "sent_notifications" does not exist
LINE 1: ...onfig_name:main*/ UPDATE "sent_notifications" SET "id_conver...
                                                             ^
/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/copy_column_using_background_migration_job.rb:25:in `block in perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/batched_migration_job.rb:105:in `block (2 levels) in each_sub_batch'
/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/background_migration/batched_migration_job.rb:104:in `block in each_sub_batch'
/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/batched_migration_job.rb:103:in `each_sub_batch'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/background_migration/copy_column_using_background_migration_job.rb:24:in `perform'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:81:in `execute_batched_migration_job'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/background_migration/batched_migration_wrapper.rb:63: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
(See full trace by running task with --trace)

I tried to rerun a db-migration to create missing columns. That worked without errors but also didn’t create any missing column:
gitlab-rake db:migrate --trace

Your re-execution of the background-migration failed for me with the same error.

Hi,

I was able to create the missing columns by doing the following for the send_notifications for example:

• Edit 20230130175512_initialize_conversion_of_sent_notifications_to_bigint.rb in /opt/gitlab/embedded/service/gitlab-rails/db/migrate and comment the “def down”:

# frozen_string_literal: true

class InitializeConversionOfSentNotificationsToBigint < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  TABLE = :sent_notifications
  COLUMNS = %i[id]

  def up
    initialize_conversion_of_integer_to_bigint(TABLE, COLUMNS)
  end

#  def down
#    revert_initialize_conversion_of_integer_to_bigint(TABLE, COLUMNS)
#  end
end

• Run “gitlab-rake db:migrate:redo VERSION=20230130175512”
• Revert edit on the above file
• Check if the column was added correctly via gitlab-psql
• restart background migration

However I am not sure if it is a good idea to make changes like this.
I’d prefer to see an official way to fix this or a statement that this will be fixed in the next release.

Thanks
Kristian

Thank you, your suggestion helped solve the problem.

Thanks, the fix worked OK.

More info: Upgrade from 15.7.0 to 15.11.0.

Hi @KWM83 - we have just opened a bug issue regarding this problem as we are currently working through this with one of our customers Batched Background migrations fail on upgrade from 15.7.0 to 15.11.0 (#408835) · Issues · GitLab.org / GitLab · GitLab.

Any other details will be posted there when available.

Many thanks!

This fix gets rid of sent_notifications task but leaves ci_build_needs unfixed.
I needed to do similar steps for 20230130104819_initialize_conversion_of_ci_build_needs_to_bigint.rb file and redo 20230130104819 version of migration to get rid of 2nd stuck migration.