Red Hat Bugzilla – Bug 1402513
Can not delete host if there are SCAP reports for that host
Last modified: 2018-10-16 14:53:07 EDT
Description of problem: If a host has any SCAP reports and you try to delete said host from the Hosts -> All Hosts view or by click on the host and select the delete option then you get the following error message: error: PG::Error: ERROR: update or delete on table "hosts" violates foreign key constraint "reports_host_id_fk" on table "reports" DETAIL: Key (id)=(43) is still referenced from table "reports". : DELETE FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."id" = $1 Version-Release number of selected component (if applicable): 6.2.4 How reproducible: Always if a host has any SCAP reports. Steps to Reproduce: 1. attempt to delete a host that has any SCAP reports Actual results: Host deletion fails with cryptic message Expected results: The host and the SCAP reports get deleted without error. OR, an option pops up to also delete the SCAP reports or preserve them even after host has been deleted.
@Marek and @Shlomi, My apologies. I misread Shlomi's note and thought they had tried it on a newer version of Satellite then I encountered the issue on. I will run through the procedure again and attach a production log.
@Marek and @Shlomi, When I wen't back to reproduce again I could not. It worked as expected. You can close this out. Apologies for the ghost.
Thanks for letting us know. I'm closing as NOTABUG, please feel free to reopen if you find the reproducer.
Experiencing this same issue on Satellite 6.2.6, when trying to delete hosts that have any SCAP reports. We have to go in and delete all reports for the host before it will allow us to delete the host itself. This is a problem since we have permissions configured to prevent users from deleting SCAP reports (we don't want them mucking about security reports for active systems). This error pops up for both admin users and regular users. It seems that the host deletion process should remove the SCAP reports before trying to delete the host? [root@uatdocker01 ~]# subscription-manager unregister ERROR: update or delete on table "hosts" violates foreign key constraint "reports_host_id_fk" on table "reports" DETAIL: Key (id)=(119) is still referenced from table "reports".
Follow up: I believe discovered the cause. Upgrading a capsule server (using "satellite-installer --scenario capsule --upgrade") that receives SCAP reports seems to be the root cause -- any reports created before the upgrade cause the foreign key violation error. If I go into the postgresql database and issue SQL to delete all reports created before the capsule upgrade, the rest of the reports get deleted automatically when the host is unregistered.
Justin, please see the linked BZ #1334035 (comment 2). The workaround or fix from there might help.
Please let us know about you findings. If it does not help, I'll reopen the BZ and try your reproducing steps.
@Marek, 1. get scap reports for a host 2. attempt to delete the host 3. it will error trying to delete the host 4. delete all the scap reports for the VM 5. attempt to delete the host 6. it works then
Since I'm awful at anonymizing: I ran into this today. The error I was getting: (Truncated, and "anonymized", from an ansible script) [NOTIFICATION], [2017-10-03 18:42:09], [Writing FQDN katello-fact] [[94mRUNNING[0m], [2017-10-03 18:42:10], [Disassociating host id 1205 for host YYYYY.YYYYY.com] [[94mRUNNING[0m], [2017-10-03 18:42:11], [Deleting host id 1205 for host YYYYY.YYYYY.com] An error occured: HTTP Error 500: Internal Server Error url: https://XXXXX.XXXXX.com:443/api/v2/hosts//1205 code: 500 error: { "error": { "message": "ERROR: update or delete on table \"hosts\" violates foreign key constraint \"reports_host_id_fk\" on table \"reports\"\nDETAIL: Key (id)=(1205) is still referenced from table \"reports\".\n" } }
Created redmine issue https://projects.theforeman.org/issues/24269 from this bug
After hours of debugging I finally figured out the reproducing steps. This is present even in recent versions but it only happens when host has 0 config reports and at least 1 arf_report. Due to a bug in a code, if we see at least 1 config report, all reports (including arf_reports) are removed prior host deletion. If there's 0 config reports, reports table remains untouched, hence arf_reports remain in the table and foreign key causes the 500.
Upstream bug assigned to mhulan@redhat.com
Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/24269 has been resolved.
The fix has landed in Foreman 1.20 (current develop branch)
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, 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/RHSA-2018:2927