Bug 1372757

Summary: ERROR: invalid reference to FROM-clause entry for table "smart_proxies"
Product: Red Hat Satellite Reporter: Swapnil Abnave <sabnave>
Component: SearchAssignee: Tomer Brisker <tbrisker>
Status: CLOSED ERRATA QA Contact: Sanket Jagtap <sjagtap>
Severity: medium Docs Contact:
Priority: medium    
Version: 6.2.0CC: bbuckingham, bkearney, chartwel, dnunes, egolov, ehelms, jcallaha, oshtaier, pierre.dony, rbobek, sjagtap, vgunasek, wpinheir
Target Milestone: UnspecifiedKeywords: Triaged
Target Release: Unused   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: foreman-1.11.0.62-1 Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of:
: 1405496 (view as bug list) Environment:
Last Closed: 2017-01-26 10:42:16 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:
Bug Depends On:    
Bug Blocks: 1405496    
Attachments:
Description Flags
hot fix none

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 &lt;&gt; 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" &gt;= '2016-10-15')) LIMIT 1

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

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