Bug 1955397

Summary:	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"
Product:	Red Hat Satellite	Reporter:	Sayan Das <saydas>
Component:	Repositories	Assignee:	Ian Ballou <iballou>
Status:	CLOSED ERRATA	QA Contact:	Tasos Papaioannou <tpapaioa>
Severity:	high	Docs Contact:
Priority:	high
Version:	6.9.0	CC:	ahumbe, ajambhul, hyu, iballou, jjeffers, kkohli, mmccune, pcreech, smajumda
Target Milestone:	6.9.3	Keywords:	PrioBumpField, PrioBumpGSS, Triaged, Upgrades
Target Release:	Unused
Hardware:	All
OS:	All
Whiteboard:
Fixed In Version:	tfm-rubygem-katello-3.18.1.30-1	Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:
Clones:	1962860 (view as bug list)		Environment:
Last Closed:	2021-07-01 14:56:48 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

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