Failed pipelines from migrating merge_request_metrics.id to bigint

Related Upstream Issue: https://gitlab.com/gitlab-org/gitlab/-/issues/389516

Failed job example for rspec background_migration pg12: https://jihulab.com/gitlab-cn/gitlab/-/jobs/6996633

log 
  Column id_convert_to_bigint does not exist on merge_request_metrics
# ./lib/gitlab/database/migration_helpers.rb:830:in `block in backfill_conversion_of_integer_to_bigint'
# ./lib/gitlab/database/migration_helpers.rb:826:in `to_h'
# ./lib/gitlab/database/migration_helpers.rb:826:in `backfill_conversion_of_integer_to_bigint'
# ./db/post_migrate/20230127101834_backfill_merge_request_metrics_for_bigint_conversion.rb:10:in `up'
log 
        PG::UndefinedColumn: ERROR:  column "id_convert_to_bigint" of relation "merge_request_metrics" does not exist
      # ./lib/gitlab/database/migration_helpers.rb:745:in `block in revert_initialize_conversion_of_integer_to_bigint'
      # ./lib/gitlab/database/migration_helpers.rb:745:in `each'
      # ./lib/gitlab/database/migration_helpers.rb:745:in `revert_initialize_conversion_of_integer_to_bigint'
      # ./db/migrate/20230127093353_initialize_conversion_of_merge_request_metrics_to_bigint.rb:14:in `down'

Failed job example for db:rollback: https://jihulab.com/gitlab-cn/gitlab/-/jobs/7001066

log 
PG::UndefinedColumn: ERROR:  column "id_convert_to_bigint" of relation "merge_request_metrics" does not exist
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:49:in `exec'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:49:in `block (2 levels) in execute'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:48:in `block in execute'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `execute'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/marginalia-1.11.1/lib/marginalia.rb:71:in `execute_with_marginalia'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/connection_adapters/abstract/schema_statements.rb:654:in `remove_column'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/migration.rb:929:in `block in method_missing'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/migration.rb:897:in `block in say_with_time'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/migration.rb:897:in `say_with_time'
/builds/gitlab-cn/gitlab/vendor/ruby/3.0.0/gems/activerecord-6.1.7.2/lib/active_record/migration.rb:918:in `method_missing'
/builds/gitlab-cn/gitlab/lib/gitlab/database/migration_helpers.rb:745:in `block in revert_initialize_conversion_of_integer_to_bigint'
/builds/gitlab-cn/gitlab/lib/gitlab/database/migration_helpers.rb:745:in `each'
/builds/gitlab-cn/gitlab/lib/gitlab/database/migration_helpers.rb:745:in `revert_initialize_conversion_of_integer_to_bigint'
/builds/gitlab-cn/gitlab/db/migrate/20230127093353_initialize_conversion_of_merge_request_metrics_to_bigint.rb:14:in `down'

Background

https://gitlab.com/gitlab-org/gitlab/-/issues/389516 followed GitLab development doc of "Migrating integer primary keys to bigint" to migrate merge_request_metrics.id to bigint.

Environments

  1. GitLab SaaS (Production)
  2. GitLab pipeline (Test)
  3. GDK (Development)
  4. JiHu SaaS (Production)
  5. JiHu pipeline (Test)
  6. Self-managed (Production)

Step 1: Add new temporary columns

  • Initialize the conversion and start migrating existing data
  • Enqueue batched background migration (code) to migrate the existing data

https://gitlab.com/gitlab-org/gitlab/-/merge_requests/110348/diffs

  • db/migrate/20230127093353_initialize_conversion_of_merge_request_metrics_to_bigint.rb
  • db/post_migrate/20230127101834_backfill_merge_request_metrics_for_bigint_conversion.rb

(without checking com_or_dev_or_test_but_not_jh?, executed in all environments)

Added id_convert_to_bigint to table merge_request_metrics.

Step 2: Swap the columns

https://gitlab.com/gitlab-org/gitlab/-/merge_requests/114441/diffs

(checked com_or_dev_or_test_but_not_jh?, only executed in GitLab SaaS, GitLab pipeline and GDK)

Swapped the type between id_convert_to_bigint and id on table merge_request_metrics.

Step 3: Remove the trigger and old integer columns

https://gitlab.com/gitlab-org/gitlab/-/merge_requests/115544/diffs

(checked com_or_dev_or_test_but_not_jh?, only executed in GitLab SaaS, GitLab pipeline and GDK)

Removed id_convert_to_bigint on table merge_request_metrics.

Analysis

From: #2718 (comment 2233672)

Why can the local test pass?

In my local GDK environment, I use gdk update to migrate the database, which will execute the latest migrations one by one.

In table merge_request_metrics, there is a column id_convert_to_bigint.

Why JH's pipeline failed?

The database of the test environment is initialized through db/structure.sql.

In table merge_request_metrics, column id_convert_to_bigint does not exist.

For db:rollback job:

JiHu Upstream
截屏2023-03-23_18.42.32 截屏2023-03-23_18.42.10
https://jihulab.com/gitlab-cn/gitlab/-/jobs/7001066 https://gitlab.com/gitlab-org/gitlab/-/jobs/3987420240

JH missed the step down in CleanupBigintConversionForMergeRequestMetrics.

But JH still needs to execute: InitializeConversionOfMergeRequestMetricsToBigint, this time,

column "id_convert_to_bigint" of relation "merge_request_metrics" does not exist.

For rspec background_migration pg12 job:

This is an example of failure:

# spec/lib/gitlab/background_migration/disable_legacy_open_source_license_for_projects_less_than_five_mb_spec.rb:25

RSpec.describe Gitlab::BackgroundMigration::DisableLegacyOpenSourceLicenseForProjectsLessThanFiveMb,
               :migration,
               schema: 20221018095434,
               feature_category: :projects do

# test cases ...
end

Such special version schema: 20221018095434 requires the DB to perform up and down frequently.

由 Baodong 编辑于