Bug 1372757 - ERROR: invalid reference to FROM-clause entry for table "smart_proxies"
Summary: ERROR: invalid reference to FROM-clause entry for table "smart_proxies"
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite
Classification: Red Hat
Component: Search
Version: 6.2.0
Hardware: Unspecified
OS: Unspecified
medium
medium vote
Target Milestone: Unspecified
Assignee: Tomer Brisker
QA Contact: Sanket Jagtap
URL:
Whiteboard:
: 1385882 1410673 (view as bug list)
Depends On:
Blocks: 1405496
TreeView+ depends on / blocked
 
Reported: 2016-09-02 14:53 UTC by Swapnil Abnave
Modified: 2021-08-30 12:21 UTC (History)
13 users (show)

Fixed In Version: foreman-1.11.0.62-1
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
: 1405496 (view as bug list)
Environment:
Last Closed: 2017-01-26 10:42:16 UTC
Target Upstream Version:


Attachments (Terms of Use)
hot fix (1.48 KB, patch)
2017-01-16 18:28 UTC, Waldirio M Pinheiro
no flags Details | Diff


Links
System ID Private Priority Status Summary Last Updated
Foreman Issue Tracker 14730 0 None None None 2016-09-06 17:56:14 UTC
Red Hat Knowledge Base (Solution) 2876811 0 None None None 2017-01-19 18:04:46 UTC
Red Hat Product Errata RHBA-2017:0197 0 normal SHIPPED_LIVE Satellite 6.2.7 Async Bug Release 2017-01-26 15:38:38 UTC

Description Swapnil Abnave 2016-09-02 14:53:16 UTC
Description of problem:
While searching for hosts on Satellite WebUI, observing following error message:

Oops, we're sorry but something went wrong ERROR: invalid reference to FROM-clause entry for table "smart_proxies" LINE 1: SELECT smart_proxies.id FROM "smart_proxies" "puppet_ca_prox... ^ HINT: Perhaps you meant to reference the table alias "puppet_ca_proxies_hosts".

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

How reproducible:
Frequent

Additional info:

This has been fixed in upstream:
http://projects.theforeman.org/issues/14730

foreman: 1.11.2

Comment 2 Sanket Jagtap 2016-10-21 11:25:32 UTC
Build :Satellite 6.3 snap 5

Steps:
With locations enabled. Switch context to any location, and search for Host with hostname on the dashboard page.


Error occured on a widget , please see attachment for detail view.



Failure: PG::Error: ERROR:  missing FROM-clause entry for table "models"
LINE 1: ...agtap-sat6.abc.com%' OR "models"."...
                                                             ^
: SELECT  1 AS one FROM "reports" INNER JOIN "hosts" ON "hosts"."id" = "reports"."host_id" AND "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."location_id" = 2 WHERE "reports"."type" IN ('ConfigReport') AND (1=0) AND (status <> 0) AND "reports"."host_id" IN (SELECT "hosts"."id" FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."location_id" = 2 AND (("hosts"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "hosts"."comment" ILIKE '%sjagtap-sat6.abc.com%' OR "models"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "hostgroups"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "hostgroups"."title" ILIKE '%sjagtap-sat6.abc.com%' OR "hostgroups"."title" ILIKE '%sjagtap-sat6.abc.com%' OR "domains"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "realms"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "environments"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "architectures"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "compute_resources"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "images"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."description" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."title" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."major" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."minor" ILIKE '%sjagtap-sat6.abc.com%' OR "nics"."ip" ILIKE '%sjagtap-sat6.abc.com%' OR "nics"."ip" ILIKE '%sjagtap-sat6.abc.com%' OR "nics"."mac" ILIKE '%sjagtap-sat6.abc.com%' OR "taxonomies"."title" ILIKE '%sjagtap-sat6.abc.com%' OR "subnets"."network" ILIKE '%sjagtap-sat6.abc.com%' OR "subnets"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "subnets"."network" ILIKE '%sjagtap-sat6.abc.com%' OR "subnets"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "hosts"."uuid" ILIKE '%sjagtap-sat6.abc.com%' OR "nics"."mac" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."description" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."title" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."major" ILIKE '%sjagtap-sat6.abc.com%' OR "operatingsystems"."minor" ILIKE '%sjagtap-sat6.abc.com%' OR "smart_proxies"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "katello_host_collections"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "katello_content_views"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "katello_environments"."name" ILIKE '%sjagtap-sat6.abc.com%' OR "katello_subscription_facets"."release_version" ILIKE '%sjagtap-sat6.abc.com%' OR "katello_subscription_facets"."service_level" ILIKE '%sjagtap-sat6.abc.com%' OR "katello_subscription_facets"."registered_through" ILIKE '%sjagtap-sat6.abc.com%' OR "katello_subscription_facets"."uuid" ILIKE '%sjagtap-sat6.abc.com%' OR 1=0 OR "discovery_rules"."name" ILIKE '%sjagtap-sat6.abc.com%'))) AND (("reports"."reported_at" >= '2016-10-15')) LIMIT 1

Comment 4 Bryan Kearney 2016-10-21 12:01:21 UTC
Upstream bug assigned to tbrisker@redhat.com

Comment 5 Bryan Kearney 2016-10-21 12:01:25 UTC
Upstream bug component is Search

Comment 6 Tomer Brisker 2016-11-03 08:04:35 UTC
@Snaket - That is a separate bug that has also boon fixed in upstream, I have cloned it to BZ1391365 
Moving this back to ONQA as this issue deals with broken search on the host list page.

Comment 7 Sanket Jagtap 2016-11-25 06:35:10 UTC
Build: Satellite 6.3 snap 6


Steps:
With locations enabled. Switch context to any location, and search for Host with hostname on the hosts page.

No error was seen on hosts page.

Verified.

Comment 8 Tomer Brisker 2016-12-06 13:35:07 UTC
*** Bug 1385882 has been marked as a duplicate of this bug. ***

Comment 10 Bryan Kearney 2016-12-06 13:41:35 UTC
Moving to 6.2.z based on suggestion from Tomer.

Comment 11 Tomer Brisker 2016-12-06 13:53:28 UTC
Note: This only occurs when searching in a location or organization context that has "all capsules" associated with it (under Administer->Locations/Organizations->Edit). 
A workaround is possible by unchecking the "All capsules" checkbox in the above form and manually selecting all capsules.

Comment 13 Tomer Brisker 2017-01-08 09:06:11 UTC
*** Bug 1410673 has been marked as a duplicate of this bug. ***

Comment 14 Sanket Jagtap 2017-01-11 12:55:16 UTC
Build: Satellite 6.2.7 snap 1

Steps:
Checked all capsules for a particular Org/Loc , searched successfully for the host with hostname on hosts page.

Also, Searched Hosts with any context successfully

Comment 16 Waldirio M Pinheiro 2017-01-16 18:25:50 UTC
Dear Diogo,

Please, follow the steps below on Satellite Server

# cd /usr/share/foreman
# git init .
# git add .
# git commit -m 'original'

// Save the attached patch on this path (foreman_inv_ref.patch), then execute command below

# patch -p1 < foreman_inv_ref.patch

// The output should be something similar to
patching file app/models/concerns/hostext/search.rb

// Now, restart the httpd service and try again via webUI
# systemctl restart httpd

Let me know if you need something else.


Best Regards
-- 
Waldirio M Pinheiro | Senior Software Maintenance Engineer

Comment 17 Waldirio M Pinheiro 2017-01-16 18:28:01 UTC
Created attachment 1241361 [details]
hot fix

Comment 20 errata-xmlrpc 2017-01-26 10:42:16 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, 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-2017:0197


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