Project

General

Profile

Bug #32673

Migrations fail with 'could not create unique index "index_lookup_values_on_lookup_key_id_and_match"' error

Added by Ondřej Ezr almost 2 years ago. Updated almost 2 years ago.

Status:
Closed
Priority:
High
Assignee:
Category:
DB migrations
Target version:
-
Difficulty:
Triaged:
No
Bugzilla link:
Fixed in Releases:
Found in Releases:
Red Hat JIRA:

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

Related to Foreman - Refactor #30207: Test LookupController in isolationClosed

Associated revisions

Revision 122975dd (diff)
Added by Ondřej Ezr almost 2 years ago

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.

History

#1 Updated by Ondřej Ezr almost 2 years ago

#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

#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

Also available in: Atom PDF