Overview
The following error occurred when I tried to upgrade GitLab from 16.3.4 to 16.7.2.
main: == 20230809210550 SwapIssueUserMentionsNoteIdToBigintForSelfManaged: migrating
WARNING: you don't own a lock of type ExclusiveLock
rails aborted!
ActiveRecord::ConcurrentMigrationError:
Failed to release advisory lock
/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'
Caused by:
StandardError: An error has occurred, all later migrations canceled:
PG::UniqueViolation: ERROR: could not create unique index "tmp_issue_user_mentions_on_issue_id_index"
DETAIL: Key (issue_id)=(40590) 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:26: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/20230809210550_swap_issue_user_mentions_note_id_to_bigint_for_self_managed.rb:37:in `swap'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230809210550_swap_issue_user_mentions_note_id_to_bigint_for_self_managed.rb:15: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'
Caused by:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: could not create unique index "tmp_issue_user_mentions_on_issue_id_index"
DETAIL: Key (issue_id)=(40590) 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:26: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/20230809210550_swap_issue_user_mentions_note_id_to_bigint_for_self_managed.rb:37:in `swap'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230809210550_swap_issue_user_mentions_note_id_to_bigint_for_self_managed.rb:15: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'
Caused by:
PG::UniqueViolation: ERROR: could not create unique index "tmp_issue_user_mentions_on_issue_id_index"
DETAIL: Key (issue_id)=(40590) 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:26: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/20230809210550_swap_issue_user_mentions_note_id_to_bigint_for_self_managed.rb:37:in `swap'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230809210550_swap_issue_user_mentions_note_id_to_bigint_for_self_managed.rb:15: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'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
Error location is here.
My GitLab info
- Using gitlab-ce self-hosted
- Run Docker in Swarm
Result of gitlab-rake gitlab:env:info
root@f0e29c756661:/# gitlab-rake gitlab:env:info
System information
System:
Current User: git
Using RVM: no
Ruby Version: 3.0.6p216
Gem Version: 3.4.18
Bundler Version:2.4.18
Rake Version: 13.0.6
Redis Version: 7.0.12
Sidekiq Version:6.5.7
Go Version: unknown
GitLab information
Version: 16.3.4
Revision: a86e56e774c
Directory: /opt/gitlab/embedded/service/gitlab-rails
DB Adapter: PostgreSQL
DB Version: 13.12
URL: https://gitlab.example.com
HTTP Clone URL: https://gitlab.example.com/some-group/some-project.git
SSH Clone URL: git@gitlab.example.com:some-group/some-project.git
Using LDAP: no
Using Omniauth: yes
Omniauth Providers: saml
GitLab Shell
Version: 14.26.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: 16.3.4
- default Git Version: 2.41.0.gl1
My upgrade step
- Update gitlab container
- Upgrade docker image from
gitlab/gitlab-ce:16.3.4-ce.0togitlab/gitlab-ce:16.7.0-ce.0 - omnibus configuration hasn’t changed with this upgrade
- Upgrade docker image from
- Run
gitlab-rails db:migratein gitlab container
My detailed survey
# Number of records for issue_id=40590
irb(main):003:0> IssueUserMention.where(issue_id: 40590).count
=> 22
# More detailed breakdown
irb(main):001:0> IssueUserMention.where(issue_id: 40590).pluck(:id, :issue_id, :note_id, :mentioned_users_ids, :mentioned_projects_ids, :mentioned_groups_ids)
=>
[[2470, 40590, 4318840, [360], nil, nil],
[2471, 40590, 4319029, [360], nil, nil],
[2199, 40590, 4286131, [355], nil, nil],
[2194, 40590, 4285924, [355], nil, nil],
[2469, 40590, 4318798, [556], nil, nil],
[2356, 40590, 4310830, [355], nil, nil],
[2450, 40590, 4317898, [556], nil, nil],
[2466, 40590, 4318640, [360], nil, nil],
[2370, 40590, 4311128, [556], nil, nil],
[2441, 40590, 4316953, [556], nil, nil],
[2445, 40590, 4317876, [134, 209, 360], nil, nil],
[2434, 40590, 4316245, [355], nil, nil],
[2366, 40590, 4311026, [556, 355], nil, nil],
[2355, 40590, 4310743, [556, 34], nil, nil],
[2439, 40590, 4316913, [556], nil, nil],
[2460, 40590, 4317962, [34], nil, nil],
[2440, 40590, 4316932, [355], nil, nil],
[2453, 40590, 4317918, [355], nil, nil],
[2465, 40590, 4318604, [355], nil, nil],
[2455, 40590, 4317935, [556], nil, nil],
[2463, 40590, 4318480, [360, 556], nil, nil],
[2198, 40590, 4286130, [355], nil, nil]]
# created_at for issue_id=40590
irb(main):006:0> Issue.find(40590).created_at
=> Fri, 11 Mar 2022 11:45:08.014720000 JST +09:00
# Number of issue_user_mentions with duplicate issue_id
irb(main):027:0> ActiveRecord::Base.connection.select_all("SELECT issue_id, COUNT(issue_id) FROM issue_user_mentions GROUP BY issue_id HAVING COUNT(issue_id) > 1").count
=> 740
# Oldest and newest records of the above
irb(main):001:0> ActiveRecord::Base.connection.select_all(<<~SQL).rows.flatten
SELECT MAX(i.issue_id), MIN(i.issue_id) FROM
(
SELECT issue_id, COUNT(issue_id) FROM issue_user_mentions GROUP BY issue_id HAVING COUNT(issue_id) > 1
) AS i
SQL
=> [42395, 1269]
irb(main):002:0> Issue.find(1269).created_at
=> Mon, 11 Apr 2016 15:35:46.000000000 JST +09:00 # We used GitLab v8.x or v9.x (maybe...)
irb(main):003:0> Issue.find(42395).created_at
=> Tue, 26 Dec 2023 16:49:08.367939000 JST +09:00 # We used GitLab v16.3.4
In this case, how do I modify the records to make gitlab-rails db:migrate pass?
Thank you.