Project

General

Profile

Bug #24699

Dashboard widget 'Run distribution chart' gives error 'PG::DuplicateAlias'

Added by Adam Winberg 11 months ago. Updated 4 months ago.

Status:
Resolved
Priority:
Normal
Category:
Dashboard
Target version:
-
Difficulty:
Triaged:
No
Bugzilla link:
Team Backlog:
Fixed in Releases:
Found in Releases:

Description

This happens when I search for a specific host in the dashboard search. The 'Run distribution chart' widget then errors out with message:

ActionView::Template::Error: PG::DuplicateAlias: ERROR:  table name "reports" specified more than once

The Foreman log shows the exact SQL query which looks like this (prettied up):

SELECT COUNT(DISTINCT "hosts"."id") FROM "hosts" 
LEFT OUTER JOIN "reports" ON "reports"."host_id" = "hosts"."id" AND "reports"."type" IN ('ConfigReport')
LEFT OUTER JOIN "models" ON "models"."id" = "hosts"."model_id" 
LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id" 
LEFT OUTER JOIN "nics" ON "nics"."host_id" = "hosts"."id" AND "nics"."primary" = $1
LEFT OUTER JOIN "domains" ON "domains"."id" = "nics"."domain_id" 
LEFT OUTER JOIN "realms" ON "realms"."id" = "hosts"."realm_id" 
LEFT OUTER JOIN "environments" ON "environments"."id" = "hosts"."environment_id" 
LEFT OUTER JOIN "architectures" ON "architectures"."id" = "hosts"."architecture_id" 
LEFT OUTER JOIN "compute_resources" ON "compute_resources"."id" = "hosts"."compute_resource_id" 
LEFT OUTER JOIN "images" ON "images"."id" = "hosts"."image_id" 
LEFT OUTER JOIN "operatingsystems" ON "operatingsystems"."id" = "hosts"."operatingsystem_id" 
LEFT OUTER JOIN "nics" "primary_interfaces_hosts" ON "primary_interfaces_hosts"."host_id" = "hosts"."id" AND "primary_interfaces_hosts"."primary" = $2
LEFT OUTER JOIN "nics" "primary_interfaces_hosts_join" ON "primary_interfaces_hosts_join"."host_id" = "hosts"."id" AND "primary_interfaces_hosts_join"."primary" = $3
LEFT OUTER JOIN "subnets" ON "subnets"."id" = "primary_interfaces_hosts_join"."subnet_id" AND "subnets"."type" = $4
LEFT OUTER JOIN "nics" "primary_interfaces_hosts_join_2" ON "primary_interfaces_hosts_join_2"."host_id" = "hosts"."id" AND "primary_interfaces_hosts_join_2"."primary" = $5
LEFT OUTER JOIN "subnets" "subnet6s_hosts" ON "subnet6s_hosts"."id" = "primary_interfaces_hosts_join_2"."subnet6_id" AND "subnet6s_hosts"."type" = $6
LEFT OUTER JOIN "nics" "provision_interfaces_hosts" ON "provision_interfaces_hosts"."host_id" = "hosts"."id" AND "provision_interfaces_hosts"."provision" = $7
INNER JOIN reports ON reports.host_id = hosts.id 
WHERE "hosts"."type" IN ('Host::Managed') AND (("hosts"."name" ILIKE '%lxserv940%' OR "hosts"."comment" ILIKE '%lxserv940%' OR "reports".  "origin" ILIKE '%lxserv940%'  OR "models"."name" ILIKE '%lxserv940%' OR "hostgroups"."name" ILIKE '%lxserv940%' OR "hostgroups"."title" ILIKE '%lxserv940%' OR "domains". "name" ILIKE '%lxserv940%'   OR "realms"."name" ILIKE '%lxserv940%' OR "environments"."name" ILIKE '%lxserv940%' OR "architectures"."name" ILIKE '%lxserv940%' OR          "compute_resources"."name" ILIKE '%lxserv940%' OR "images"."name" ILIKE '%lxserv940%' OR "operatingsystems"."name" ILIKE '%lxserv940%' OR "operatingsystems".            "description" ILIKE        '%lxserv940%' OR "operatingsystems"."title" ILIKE '%lxserv940%' OR "nics"."ip" ILIKE '%lxserv940%' OR "subnets"."network" ILIKE '%lxserv940%' OR     "subnets"."name"    ILIKE '%lxserv940%' OR "subnets"."network" ILIKE '%lxserv940%' OR "subnets"."name" ILIKE '%lxserv940%' OR "hosts"."uuid" ILIKE '%lxserv940%' OR "nics". "mac" ILIKE      '%lxserv940%')) AND "reports"."origin" = $8 AND (reports.reported_at BETWEEN '2018-08-22 09:42:20.568842' AND '2018-08-22 09:49:20.568842')

The problem is that there is both a 'LEFT OUTER JOIN' and an 'INNER JOIN' on the reports table, which seem to conflict. Maybe the INNER JOIN can be removed, or the joins on the report table should be aliased?

This is on Foreman 1.18.0.


Related issues

Related to Foreman - Feature #22779: Calculate out of sync host via origin specific interval settingClosed2018-03-05

History

#1 Updated by Tomer Brisker 11 months ago

  • Related to Feature #22779: Calculate out of sync host via origin specific interval setting added

#2 Updated by Ohad Levy 11 months ago

i can confirm similar error on mysql too.

#3 Updated by Tomer Brisker 10 months ago

  • Target version set to 1.18.3

#4 Updated by The Foreman Bot 10 months ago

  • Pull request https://github.com/theforeman/foreman/pull/6131 added

#5 Updated by Tomer Brisker 9 months ago

  • Assignee set to Sebastian Gräßl
  • Status changed from New to Ready For Testing

#6 Updated by Tomer Brisker 8 months ago

  • Target version deleted (1.18.3)
  • Status changed from Ready For Testing to Resolved

We haven't managed to reproduce this issue with any newer release, closing for now. If issue persists in a current version please reopen.

Also available in: Atom PDF