PG::UniqueViolation: ERROR: could not create unique index "index_sent_notifications_on_id_convert_to_bigint"
Hello Gitlab community
I am trying to upgrade my self-hosted gitlab from 16.7.4 to 16.8.1, docker container of gitlab running on ec2-instance I am blocked due to the index creation failed for "index_sent_notifications_on_id_convert_to_bigint" at the Recipe: gitlab::database_migrations
Recipe: gitlab::database_migrations
* ruby_block[check remote PG version] action nothing (skipped due to action :nothing)
* rails_migration[gitlab-rails] action run[2024-02-07T20:30:44+00:00] WARN: gitlab-rails does not have a log_group or default logdir mode defined. Setting to 0700.
* bash_hide_env[migrate gitlab-rails database] action run
[execute] Running db:migrate rake task
main: == [advisory_lock_connection] object_id: 114680, pg_backend_pid: 502
main: == 20230924134300 FinalizeUuidBackfilling: migrating ==========================
main: -- transaction_open?(nil)
.
.
.
main: == 20240105120320 DisableFastupdateOnIssueSearchData: migrated (0.4451s) ======rake aborted!
StandardError: An error has occurred, all later migrations canceled:
PG::UniqueViolation: ERROR: could not create unique index "index_sent_notifications_on_id_convert_to_bigint"
DETAIL: Key (id_convert_to_bigint)=(0) is duplicated.
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:122:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/timeout_helpers.rb:31:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:121:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240107154805_sent_notifications_self_install_id_swap.rb:31:in `swap'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240107154805_sent_notifications_self_install_id_swap.rb:19: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:138:in `configure_database'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:107:in `configure_pg_databases'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:94: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:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: could not create unique index "index_sent_notifications_on_id_convert_to_bigint"
DETAIL: Key (id_convert_to_bigint)=(0) is duplicated.
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:122:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/timeout_helpers.rb:31:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:121:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240107154805_sent_notifications_self_install_id_swap.rb:31:in `swap'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240107154805_sent_notifications_self_install_id_swap.rb:19: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:138:in `configure_database'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:107:in `configure_pg_databases'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:94: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::UniqueViolation: ERROR: could not create unique index "index_sent_notifications_on_id_convert_to_bigint"
DETAIL: Key (id_convert_to_bigint)=(0) is duplicated.
================================================================================
Error executing action `run` on resource 'bash_hide_env[migrate gitlab-rails database]'
================================================================================
Mixlib::ShellOut::ShellCommandFailed
------------------------------------
Expected process to exit with [0], but received '1'
---- Begin output of "bash" ----
Is there any way to solve this index creation failed issue? Could we run some db queries prior to upgrade for fixing the index?
Thanks in advance!!