PG::UniqueViolation: ERROR: could not create unique index "finding_link_url_idx"

Summary

We have had multiple tickets from customers whose upgrades to Gitlab %14.7 failed.

GitLab team members can access some of them at:

• 264275
• 264089
• 263617
• 264300
• 265116
• 267900
• Federal 2236 (restricted)

Customers will be affected if they have duplicate records in the vulnerability_finding_links table. It was intended that all records in that table would be deleted by the 14.6 migration, and then a unique index could be applied in 14.7.

== 20210811193033 AddUniqueIndexToVulnerabilityFindingLinks: migrating ========
-- transaction_open?()
-> 0.0000s 
[snip]
-- add_index(:vulnerability_finding_links, [:vulnerability_occurrence_id, :url], {:unique=>true, :where=>"name is null", :name=>"finding_link_url_idx", :algorithm=>:concurrently})
-- execute("RESET statement_timeout")
-> 0.0004s
STDERR:
rake aborted!
StandardError: An error has occurred, all later migrations canceled:
PG::UniqueViolation: ERROR: could not create unique index "finding_link_url_idx"
DETAIL: Key (vulnerability_occurrence_id, url)=(26274, https://****/****/***?id=***) is duplicated.
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:154:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:375:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:153:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/migrate/20210811193033_add_unique_index_to_vulnerability_finding_links.rb:11:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:31:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:63: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: 

This migration was introduced in %14.7 by !67993 (merged)

Prior to this running, !76580 (merged) should have been applied:

This MR removes all rows from the vulnerability_finding_links table.

Hypothesis: if a customer does a zero downtime upgrade from 14.5 or earlier to 14.7, the migrations won't run in the right order. (Not the case - the two migrations are sequentially numbered in the wrong order, and so any upgrade can run into this bug)

Workaround

Customers have worked around it in the tickets by truncating the table. It'd be preferable to use the tested solution for this, which is the %14.6 migration !76580 (merged)

# 14.6 migration to remove records
# revised from :up to :redo to be more effective
sudo gitlab-rake db:migrate:redo VERSION=20211210173137

# 14.7.5 migration
sudo gitlab-rake db:migrate:up VERSION=20210811193033

# finish DB migrations
sudo gitlab-rake db:migrate

Steps to reproduce

Example Project

What is the current bug behavior?

Unique index cannot be applied if records are not unique.

What is the expected correct behavior?

Migration applies. Perhaps a pre-req was to remove non-unique records.

Relevant logs and/or screenshots

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info

(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check

(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)
(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)
(we will only investigate if the tests are passing)

Possible fixes