DB-Migration failure during GitLab-Upgrade from 16.3.4 to 16.4.0

Summary

Database migration fails during upgrade from version 16.3.4 to 16.4.0 (20230823145126 SwapNotesIdToBigintForSelfManaged).

Important notes:

We once used the self-compiled installation method and migrated to Docker by using the built-in Backup mechanism.
We use an external Postgres database based on version 15.3

Steps to reproduce

We had version 16.3.4 running inside Docker using gitlab/gitlab-ce:16.3.4-ce.0. Started our setup with updated image gitlab/gitlab-ce:16.4.0-ce.0. Migration error occurs during automatic gitlab-ctl reconfigure at container start.

What is the current bug behavior?

Migration error occurs and container exits.

What is the expected correct behavior?

Migration is applied successfully and GitLab is running.

Relevant logs

Running db:migrate rake task main: == [advisory_lock_connection] object_id: 167580, pg_backend_pid: 4212 main: == 20230823145126 SwapNotesIdToBigintForSelfManaged: migrating ================ main: -- column_exists?("notes", "id_convert_to_bigint") main: -> 0.0770s main: -- columns("notes") main: -> 0.0026s main: -- transaction_open?() main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0009s main: -- index_exists?("notes", :id_convert_to_bigint, {:unique=>true, :name=>"index_notes_on_id_convert_to_bigint", :algorithm=>:concurrently}) main: -> 0.0123s main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: notes, column_name: id_convert_to_bigint main: -- transaction_open?() main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0008s main: -- index_exists?("notes", [:author_id, :created_at, :id_convert_to_bigint], {:name=>:index_notes_on_author_id_created_at_id_convert_to_bigint, :algorithm=>:concurrently}) main: -> 0.0115s main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: notes, column_name: [:author_id, :created_at, :id_convert_to_bigint] main: -- transaction_open?() main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0008s main: -- index_exists?("notes", [:id_convert_to_bigint], {:where=>"confidential = true", :name=>:index_notes_on_id_convert_to_bigint_where_confidential, :algorithm=>:concurrently}) main: -> 0.0111s main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: notes, column_name: [:id_convert_to_bigint] main: -- transaction_open?() main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0009s main: -- index_exists?("notes", [:id_convert_to_bigint], {:where=>"internal = true", :name=>:index_notes_on_id_convert_to_bigint_where_internal, :algorithm=>:concurrently}) main: -> 0.0123s main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: notes, column_name: [:id_convert_to_bigint] main: -- transaction_open?() main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0008s main: -- index_exists?("notes", [:project_id, :id_convert_to_bigint], {:where=>"NOT system", :name=>:index_notes_on_project_id_id_convert_to_bigint_system_false, :algorithm=>:concurrently}) main: -> 0.0116s main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: notes, column_name: [:project_id, :id_convert_to_bigint] main: -- transaction_open?() main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0008s main: -- index_exists?("notes", [:id_convert_to_bigint, :noteable_type], {:where=>"note ~~ '%@%'::text", :name=>:note_mentions_temp_index_convert_to_bigint, :algorithm=>:concurrently}) main: -> 0.0118s main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: notes, column_name: [:id_convert_to_bigint, :noteable_type] main: -- transaction_open?() main: -> 0.0000s main: -- execute("ALTER TABLE notes RENAME COLUMN id TO id_tmp") main: -> 0.0006s main: -- execute("ALTER TABLE notes RENAME COLUMN id_convert_to_bigint TO id") main: -> 0.0004s main: -- execute("ALTER TABLE notes RENAME COLUMN id_tmp TO id_convert_to_bigint") main: -> 0.0005s main: -- quote_table_name("trigger_080e73845bfd") main: -> 0.0000s main: -- execute("ALTER FUNCTION \"trigger_080e73845bfd\" RESET ALL") main: -> 0.0007s main: -- execute("ALTER SEQUENCE notes_id_seq OWNED BY notes.id") main: -> 0.0005s main: -- change_column_default("notes", :id, #) main: -> 0.0031s main: -- change_column_default("notes", :id_convert_to_bigint, 0) main: -> 0.0030s main: -- execute("ALTER TABLE notes DROP CONSTRAINT notes_pkey") rake aborted! StandardError: An error has occurred, all later migrations canceled:

PG::DependentObjectsStillExist: ERROR: cannot drop constraint notes_pkey on table notes because other objects depend on it DETAIL: constraint fk_rails_d83a918cb1 on table system_note_metadata depends on index notes_pkey HINT: Use DROP ... CASCADE to drop the dependent objects too. /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:111:in block in swap' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in run_block' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:134:in block in run_block_with_lock_timeout' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:129:in run_block_with_lock_timeout' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:97:in run' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in with_lock_retries' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:99:in with_lock_retries' /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:92:in swap' /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:70: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' /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:123:in configure_database' /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:101:in block (3 levels) in <top (required)>' /opt/gitlab/embedded/bin/bundle:25:in load' /opt/gitlab/embedded/bin/bundle:25:in `'

Caused by: ActiveRecord::StatementInvalid: PG::DependentObjectsStillExist: ERROR: cannot drop constraint notes_pkey on table notes because other objects depend on it DETAIL: constraint fk_rails_d83a918cb1 on table system_note_metadata depends on index notes_pkey HINT: Use DROP ... CASCADE to drop the dependent objects too. /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:111:in block in swap' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in run_block' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:134:in block in run_block_with_lock_timeout' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:129:in run_block_with_lock_timeout' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:97:in run' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in with_lock_retries' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:99:in with_lock_retries' /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:92:in swap' /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:70: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' /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:123:in configure_database' /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:101:in block (3 levels) in <top (required)>' /opt/gitlab/embedded/bin/bundle:25:in load' /opt/gitlab/embedded/bin/bundle:25:in `'

Caused by: PG::DependentObjectsStillExist: ERROR: cannot drop constraint notes_pkey on table notes because other objects depend on it DETAIL: constraint fk_rails_d83a918cb1 on table system_note_metadata depends on index notes_pkey HINT: Use DROP ... CASCADE to drop the dependent objects too. /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:111:in block in swap' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in run_block' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:134:in block in run_block_with_lock_timeout' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:129:in run_block_with_lock_timeout' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:97:in run' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in with_lock_retries' /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:99:in with_lock_retries' /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:92:in swap' /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:70: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' /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:123:in configure_database' /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:101:in block (3 levels) in <top (required)>' /opt/gitlab/embedded/bin/bundle:25:in load' /opt/gitlab/embedded/bin/bundle:25:in `' Tasks: TOP => db:migrate (See full trace by running task with --trace)

Details of package version

Provide the package version installation details


external_url "#{File.read("/run/secrets/gitlab-external-url-with-optional-port").gsub("\n", "")}"
gitlab_rails["time_zone"] = "Europe/Berlin"
gitlab_rails["initial_root_password"] = File.read("/run/secrets/gitlab-initial-root-password").gsub("\n", "")
gitlab_rails["initial_shared_runners_registration_token"] = File.read("/run/secrets/gitlab-shared-runners-registration-token").gsub("\n", "")
postgresql["enable"] = false
gitlab_rails["db_adapter"] = "postgresql"
gitlab_rails["db_encoding"] = "unicode"
gitlab_rails["db_host"] = ENV["DATABASE_HOSTNAME"]
gitlab_rails["db_database"] = File.read("/run/secrets/gitlab-database-name").gsub("\n", "")
gitlab_rails["db_username"] = File.read("/run/secrets/gitlab-database-username").gsub("\n", "")
gitlab_rails["db_password"] = File.read("/run/secrets/gitlab-database-password").gsub("\n", "")
redis["enable"] = false
gitlab_rails["redis_host"] = ENV["REDIS_HOSTNAME"]
gitlab_rails["redis_port"] = 6379
gitlab_rails["redis_password"] = File.read("/run/secrets/gitlab-redis-password").gsub("\n", "")
nginx["client_max_body_size"] = "1024m"
letsencrypt["enable"] = false
nginx["redirect_http_to_https"] = true
nginx["ssl_certificate"] = "/etc/gitlab/ssl/https-certificate.crt"
nginx["ssl_certificate_key"] = "/run/secrets/gitlab-https-certificate-key"
nginx["ssl_password_file"] = "/run/secrets/gitlab-https-certificate-key-passphrase"
registry_external_url "https://#URL#:5050"
registry["enable"] = true
registry_nginx["redirect_http_to_https"] = true
registry_nginx["ssl_certificate"] = "/etc/gitlab/ssl/https-certificate.crt"
registry_nginx["ssl_certificate_key"] = "/run/secrets/gitlab-https-certificate-key"
registry_nginx["ssl_password_file"] = "/run/secrets/gitlab-https-certificate-key-passphrase"
gitlab_rails["registry_enabled"] = true
gitlab_rails["registry_host"] = "#URL#"
gitlab_rails["omniauth_enabled"] = true
gitlab_rails["omniauth_allow_single_sign_on"] = ["openid_connect"]
gitlab_rails["omniauth_block_auto_created_users"] = false
gitlab_rails["omniauth_providers"] = [
  {
    name: "openid_connect",
    icon: "#ICON#",
    label: "#LABEL#",
    args: {
      name: "openid_connect",
      strategy_class: "OmniAuth::Strategies::OpenIDConnect",
      scope: ["openid", "profile", "email"],
      response_type: "code",
      issuer:  "https://login.microsoftonline.com/#{File.read("/run/secrets/gitlab-azure-ad-oidc-tenant-id").gsub("\n", "")}/v2.0",
      client_auth_method: "query",
      discovery: true,
      uid_field: "preferred_username",
      gitlab_username_claim: "uid",
      pkce: true,
      client_options: {
        identifier: File.read("/run/secrets/gitlab-azure-ad-oidc-client-id").gsub("\n", ""),
        secret: File.read("/run/secrets/gitlab-azure-ad-oidc-client-secret").gsub("\n", ""),
        redirect_uri: "#{external_url}/users/auth/openid_connect/callback"
      }
    }
  }
]
gitlab_rails["omniauth_auto_link_user"] = ["openid_connect"]
gitlab_rails["omniauth_sync_profile_from_provider"] = ["openid_connect"]
gitlab_rails["omniauth_sync_profile_attributes"] = ["name", "email", "location"]
gitlab_rails["gitlab_default_theme"] = 7
gitlab_rails["gitlab_default_projects_features_merge_requests"] = true
gitlab_rails["gitlab_default_projects_features_issues"] = false
gitlab_rails["gitlab_default_projects_features_wiki"] = false
gitlab_rails["gitlab_default_projects_features_snippets"] = false
gitlab_rails["gitlab_default_projects_features_builds"] = false
gitlab_rails["gitlab_default_projects_features_container_registry"] = false
gitlab_rails["smtp_enable"] = true
gitlab_rails["smtp_address"] = "smtp.office365.com"
gitlab_rails["smtp_port"] = 587
gitlab_rails["smtp_user_name"] = File.read("/run/secrets/gitlab-smtp-email-address").gsub("\n", "")
gitlab_rails["smtp_password"] = File.read("/run/secrets/gitlab-smtp-password").gsub("\n", "")
gitlab_rails["smtp_domain"] = "#DOMAIN#"
gitlab_rails["smtp_authentication"] = "login"
gitlab_rails["smtp_enable_starttls_auto"] = true
gitlab_rails["smtp_openssl_verify_mode"] = "peer"
gitlab_rails["gitlab_email_from"] = File.read("/run/secrets/gitlab-smtp-email-address").gsub("\n", "")
gitlab_rails["gitlab_email_reply_to"] = "noreply@#DOMAIN#"

Note

For anyone experiencing this issue, there's a workaround mentioned in: #8227 (comment 1578977570)

Click to expand

Check if fk_d83a918cb1 is created in system_note_metadata.

P.S If you tried to update to 16.4.0 before and it failed, you probably have this FK already created.

\d system_note_metadata
                                                Table "public.system_note_metadata"
          Column           |            Type             | Collation | Nullable |                     Default
---------------------------+-----------------------------+-----------+----------+--------------------------------------------------
 id                        | integer                     |           | not null | nextval('system_note_metadata_id_seq'::regclass)
 note_id_convert_to_bigint | integer                     |           | not null | 0
 ...
Indexes:
    ...
Foreign-key constraints:
    "fk_d83a918cb1" FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE
    "fk_fbd87415c9" FOREIGN KEY (description_version_id) REFERENCES description_versions(id) ON DELETE SET NULL
    "fk_rails_d83a918cb1" FOREIGN KEY (note_id_convert_to_bigint) REFERENCES notes(id) ON DELETE CASCADE
Referenced by:
    ...
Triggers:
    ...

If fk_d83a918cb1 is already created, then find and drop the problematic FK. The faulting FK should match the same name presented in the error logs:

-- PG::DependentObjectsStillExist: ERROR:  cannot drop constraint notes_pkey on table notes because other objects depend on it
-- DETAIL:  constraint fk_rails_d83a918cb1 on table system_note_metadata depends on index notes_pkey
-- HINT:  Use DROP ... CASCADE to drop the dependent objects too.

SELECT name FROM postgres_foreign_keys
WHERE constrained_table_name = 'system_note_metadata'
AND referenced_table_name = 'notes'
AND name != 'fk_d83a918cb1';

        conname
-----------------------
 fk_rails_d83a918cb1
(1 row)

-- Drop the FK
ALTER TABLE system_note_metadata DROP CONSTRAINT <fk_name>;

Try to update to v16.4.0 again

Edited Nov 04, 2024 by Leonardo da Rosa