Bug 1955397 - Satellite 6.8 to 6.9 upgrade fails at the db:migrate stage with error "ERROR: insert or update on table "katello_hostgroup_content_facets" violates foreign key constraint"
Summary: Satellite 6.8 to 6.9 upgrade fails at the db:migrate stage with error "ERROR:...
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite
Classification: Red Hat
Component: Repositories
Version: 6.9.0
Hardware: All
OS: All
high
high vote
Target Milestone: 6.9.3
Assignee: Ian Ballou
QA Contact: Tasos Papaioannou
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2021-04-30 03:30 UTC by Sayan Das
Modified: 2021-07-01 14:58 UTC (History)
9 users (show)

Fixed In Version: tfm-rubygem-katello-3.18.1.30-1
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
: 1962860 (view as bug list)
Environment:
Last Closed: 2021-07-01 14:56:48 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Foreman Issue Tracker 32498 0 High Closed Katello 3.16 to 3.17 upgrade fails at the db:migrate stage with error "ERROR: insert or update on table "katello_hostgr... 2021-05-06 21:12:50 UTC
Red Hat Knowledge Base (Solution) 6003311 0 None None None 2021-04-30 03:32:17 UTC
Red Hat Product Errata RHBA-2021:2636 0 None None None 2021-07-01 14:57:24 UTC

Description Sayan Das 2021-04-30 03:30:49 UTC
Description of problem:

While attempting to upgrade the Red Hat Satellite server 6.8 to 6.9, It fails at the db:migrate stage with either of these two errors.

2021-04-28 11:23:10 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: PG::ForeignKeyViolation: ERROR:  insert or update on table "katello_hostgroup_content_facets" violates foreign key constraint "fk_rails_9cae1dc9a3"
2021-04-28 11:23:10 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: DETAIL:  Key (content_view_id)=(4) is not present in table "katello_content_views".

or,

2021-04-26 15:06:49 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: PG::ForeignKeyViolation: ERROR:  insert or update on table "katello_hostgroup_content_facets" violates foreign key constraint "fk_rails_159087f51c"
2021-04-26 15:06:49 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: DETAIL:  Key (lifecycle_environment_id)=(9) is not present in table "katello_environments".


Version-Release number of selected component (if applicable):

Satellite 6.9 [ 6.8 to 6.9 upgrade ]


How reproducible:

Under specific circumstances i.e. the missing foreign key constraints might be the cause and somehow it bypassed the "dependent:" constraint  in rails


Steps to reproduce:

1. On Satellite 6.8 with CV\LCE\Hostgroups preconfigured

2. Associate a hostgroup with one CV and LCE

3. Find a way to delete that LCE or CV so that they get removed from their respective tables but the hostgroups table in DB still holds the association.

   Example:

   For CV --> Katello::ContentView.where(id: <cv id>).delete_all
or, 
   For LCE --> Katello::KTEnvironment.where(id: <lce id>).delete_all

4. Perform the upgrade to 6.9


Actual Results:

db:migrate stage will fail at following 

2021-04-28 11:23:10 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: PG::ForeignKeyViolation: ERROR:  insert or update on table "katello_hostgroup_content_facets" violates foreign key constraint "fk_rails_9cae1dc9a3"
2021-04-28 11:23:10 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: DETAIL:  Key (content_view_id)=(4) is not present in table "katello_content_views".

or,

2021-04-26 15:06:49 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: PG::ForeignKeyViolation: ERROR:  insert or update on table "katello_hostgroup_content_facets" violates foreign key constraint "fk_rails_159087f51c"
2021-04-26 15:06:49 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: DETAIL:  Key (lifecycle_environment_id)=(9) is not present in table "katello_environments".


Expected results:

Before the db:migrate happens, these inconsistencies\stale associations should be removed from hostgroups table and then the db:migrate should be executed.



Additional Information:

Manual Solution present in https://access.redhat.com/solutions/6003311

Comment 3 Ian Ballou 2021-05-03 21:18:06 UTC
Created redmine issue https://projects.theforeman.org/issues/32498 from this bug

Comment 6 Bryan Kearney 2021-05-09 16:20:47 UTC
Upstream bug assigned to iballou

Comment 7 Bryan Kearney 2021-05-09 16:20:49 UTC
Moving this bug to POST for triage into Satellite since the upstream issue https://projects.theforeman.org/issues/32498 has been resolved.

Comment 9 Tasos Papaioannou 2021-06-08 14:05:51 UTC
Verified on 6.9.3 snap 1.0.

Steps:

1. On Satellite 6.8.6, create a lifecycle environment, content view, and host group.
2. Update the host group in the database to point to a non-existent content view.

***

# su - postgres -c 'psql foreman'

foreman=# select id, name, content_view_id, lifecycle_environment_id from hostgroups;
 id |      name       | content_view_id | lifecycle_environment_id 
----+-----------------+-----------------+--------------------------
  1 | test-host-group |               2 |                        2
(1 row)

foreman=# select id, name from katello_content_views;
 id |           name            
----+---------------------------
  1 | Default Organization View
  2 | test-cv
(2 rows)

foreman=# update hostgroups set content_view_id=3;

foreman=# select id, name, content_view_id, lifecycle_environment_id from hostgroups;
 id |      name       | content_view_id | lifecycle_environment_id 
----+-----------------+-----------------+--------------------------
  1 | test-host-group |               3 |                        2
(1 row)


3. Upgrade to 6.9.3:

# satellite-maintain upgrade list-versions
# satellite-maintain service stop
# satellite-maintain upgrade check --target-version 6.9 --whitelist="repositories-validate,repositories-setup"
# satellite-maintain upgrade run --target-version 6.9 --whitelist="foreman-fact-names,repositories-validate,repositories-setup"

Verify successful upgrade, and warning logged in /var/log/foreman/production.log:

2021-06-07T16:56:53 [W|app|] Unable to save content facet hostgroup for #<Hostgroup id: 1, name: "test-host-group", [...]> due to bad hostgroup data.

Comment 18 errata-xmlrpc 2021-07-01 14:56:48 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory (Satellite 6.9.3 Async Bug Fix Update), and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHBA-2021:2636


Note You need to log in before you can comment on or make changes to this bug.