Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 1445406

Summary:	[5.6] remove DISTINCT keyword from subscribed server counts when META DATA is fixed
Product:	Red Hat Satellite 5	Reporter:	Grant Gainey <ggainey>
Component:	Server	Assignee:	Grant Gainey <ggainey>
Status:	CLOSED ERRATA	QA Contact:	Radovan Drazny <rdrazny>
Severity:	high	Docs Contact:
Priority:	unspecified
Version:	560	CC:	ggainey, hartsjc, rcyriac, rdrazny, satqe-list, sgraf, shughes, tlestach
Target Milestone:	---
Target Release:	---
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:	satellite-schema-5.6.0.35-1-sat, spacewalk-schema-2.0.2-26-sat	Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:	1416940	Environment:
Last Closed:	2017-07-05 14:28:07 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:	1416940
Bug Blocks:

Description Grant Gainey 2017-04-25 15:28:34 UTC

+++ This bug was initially created as a clone of Bug #1416940 +++

Description of problem:

this bz will track the fix to remove the DISTINCT that was put into place due to the upstream meta data with the server/channels in bz 1021057. While this fixed the duplicates it introduced a terrible performance penalty for larger Satellite client counts. There is more detail history in a wiki that I will post separately.

--- Additional comment from Shannon Hughes on 2017-01-26 15:45:06 EST ---

problem set history is at https://engineering.redhat.com/trac/satellite/wiki/1021057

--- Additional comment from Shannon Hughes on 2017-01-27 06:21:50 EST ---

for now been using oracle optimizer hints on the rhn_channel.channel_family_current_members function: 

SELECT /*+ FULL(RHNSERVER) OPT_PARAM('_optimizer_mjc_enabled' 'false') OPT_PARAM('_optimizer_cartesian_enabled' 'false')*/ COUNT (DISTINCT SC.SERVER_ID)

Have used this hint successfully for three customers with large Satellite 5.6 and 5.7 external oracle installs

this would turn off the more expensive cartesian joins

--- Additional comment from Grant Gainey on 2017-04-12 14:41:46 EDT ---

SATELLITE-5.8:
67946bbb63ae281a6443234584c086b6bf3a94ad
1215bf7cf5c041e63cebfd47d08b04697752db51
0b77b4e546941392774e895d20917765dcdcc068
f0a57a3e04fd7b7459d50e44cc189e390917e4f4
3e610da34f4b510da067ab7dcf433969789666d5

--- Additional comment from Grant Gainey on 2017-04-12 14:51:43 EDT ---

Following files affected:

backend/satellite_tools/rhn-entitlement-report
backend/satellite_tools/satCerts.py
schema/spacewalk/oracle/packages/rhn_channel.pkb
schema/spacewalk/oracle/packages/rhn_entitlements.pkb
schema/spacewalk/postgres/packages/rhn_channel.pkb
schema/spacewalk/postgres/packages/rhn_entitlements.pkb

NOTE: This affects entitlement-counting. Since we don't do that any more in Spacewalk, *there is no upstream fix*

--- Additional comment from releng-rhel on 2017-04-13 11:05:35 EDT ---

Fixed in 'satellite-schema-5.8.0.26-1'. 'satellite-schema-5.8.0.26-1.el6sat' included in compose 'Satellite-5.8-RHEL-6-20170413.n.0'.
Fixed in 'spacewalk-backend-2.5.3-107'. 'spacewalk-backend-2.5.3-107.el6sat' included in compose 'Satellite-5.8-RHEL-6-20170413.n.0'.
Fixed in 'spacewalk-schema-2.5.1-41'. 'spacewalk-schema-2.5.1-41.el6sat' included in compose 'Satellite-5.8-RHEL-6-20170413.n.0'.
Moving to ON_QA.

Comment 1 Grant Gainey 2017-04-25 15:41:47 UTC

Only the rhn_channel change made by 42a58e3cd25cda65473cc4dfece760664eaefb82 made it into 5.6, and is the only thing that needs to be reverted

Comment 5 Radovan Drazny 2017-07-04 10:38:31 UTC

Tested using the steps from BZ1416940 comment 6 and BZ1416940 comment 7. No errors during registration and adding channel and system entitlements. Count of consumed entitlements is correct.

VERIFIED

Comment 7 errata-xmlrpc 2017-07-05 14:28:07 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:1683