Bug #32673
Migrations fail with 'could not create unique index "index_lookup_values_on_lookup_key_id_and_match"' error
Description
Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1960530
Description of problem:
While attempting to upgrade the Foreman from 2.1 to 2.2 version, It fails at the db:migrate stage with this error
PG::UniqueViolation: ERROR: could not create unique index "index_lookup_values_on_lookup_key_id_and_match"
DETAIL: Key (lookup_key_id, match)=(146, fqdn=myhost.example.com) is duplicated.
Version-Release number of selected component (if applicable):
Foreman 2.2 [ 2.1 to 2.2 upgrade ]
How reproducible:
Under specific circumstances i.e. when lookup_values table has duplicate record
Steps to reproduce:
1. Install and configure Foreman 2.1
2. Import some puppet modules and create some puppet smart class overrides
3. Find a way to create a duplicate override record of one of the puppet smart class parameters inside lookup_values table
4. Perform the upgrade to 2.2
Actual Results:
db:migrate stage will fail at following
StandardError: An error has occurred, this and all later migrations canceled:
PG::UniqueViolation: ERROR: could not create unique index "index_lookup_values_on_lookup_key_id_and_match"
DETAIL: Key (lookup_key_id, match)=(146, fqdn=myhost.example.com) is duplicated.
/opt/theforeman/tfm/root/usr/share/gems/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `async_exec'
/opt/theforeman/tfm/root/usr/share/gems/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `block (2 levels) in execute'
/opt/theforeman/tfm/root/usr/share/gems/gems/activesupport-6.0.3.4/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
Expected results:
Before the db:migrate happens, these inconsistencies should be properly detected, printed and if needed cleared up.
Additional Information:
Manual Solution present i.e. to identify the duplicate record and delete the one which is not needed.
See https://community.theforeman.org/t/upgrade-from-foreman-2-1-to-2-2-failed-with-postgres-error/21443 for more details
Related issues
Associated revisions
History
#1
Updated by Ondřej Ezr almost 2 years ago
- Related to Refactor #30207: Test LookupController in isolation added
#2
Updated by The Foreman Bot almost 2 years ago
- Assignee set to Ondřej Ezr
- Status changed from New to Ready For Testing
- Pull request https://github.com/theforeman/foreman/pull/8558 added
#3
Updated by The Foreman Bot almost 2 years ago
- Fixed in Releases 3.0.0 added
#4
Updated by Ondřej Ezr almost 2 years ago
- Status changed from Ready For Testing to Closed
Applied in changeset foreman|122975ddbe035bdc2663cc0207c8d666dae2df4b.
#5
Updated by Tomer Brisker almost 2 years ago
- Subject changed from Migrations fail with 'could not create unique index "index_lookup_values_on_lookup_key_id_and_match"' error to Migrations fail with 'could not create unique index "index_lookup_values_on_lookup_key_id_and_match"' error
- Fixed in Releases 2.4.1, 2.5.1 added
Fixes #32673 - deduplicate records prior uniq index
In 84cb565af244f56029471700bb313d46c45e5fcf we've introduced unique
index in database, because there is a way in Rails to bypass uniqueness
validation, if the records are being introduced at the same time.
We forgot to deduplicate the records prior introducing this migration so
some installations are failing to run the migration.