Red Hat Satellite engineering is moving the tracking of its product development work on Satellite to Red Hat Jira (issues.redhat.com). If you're a Red Hat customer, please continue to file support cases via the Red Hat customer portal. If you're not, please head to the "Satellite project" in Red Hat Jira and file new tickets here. Individual Bugzilla bugs will be migrated starting at the end of May. If you cannot log in to RH Jira, please consult article #7032570. That failing, please send an e-mail to the RH Jira admins at rh-issues@redhat.com to troubleshoot your issue as a user management inquiry. The email creates a ServiceNow ticket with Red Hat. Individual Bugzilla bugs that are migrated will be moved to status "CLOSED", resolution "MIGRATED", and set with "MigratedToJIRA" in "Keywords". The link to the successor Jira issue will be found under "Links", have a little "two-footprint" icon next to it, and direct you to the "Satellite project" in Red Hat Jira (issue links are of type "https://issues.redhat.com/browse/SAT-XXXX", where "X" is a digit). This same link will be available in a blue banner at the top of the page informing you that that bug has been migrated.
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
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:
Embargoed:


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

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.