Bug 526864
Summary: | Number of affected systems is not calculated right in SSM -> package removal | ||
---|---|---|---|
Product: | Red Hat Satellite 5 | Reporter: | Xixi <xdmoon> |
Component: | WebUI | Assignee: | Tomas Lestach <tlestach> |
Status: | CLOSED ERRATA | QA Contact: | Dimitar Yordanov <dyordano> |
Severity: | low | Docs Contact: | |
Priority: | medium | ||
Version: | 530 | CC: | cperry, dyordano, fnadge, jentrena, mmraka, paji, xdmoon |
Target Milestone: | --- | ||
Target Release: | --- | ||
Hardware: | All | ||
OS: | Linux | ||
Whiteboard: | |||
Fixed In Version: | Doc Type: | Bug Fix | |
Doc Text: |
Cause: underlying query returned more than one row for a server.
Consequence: duplicated rows made number of affected systems higher than is should be.
Fix: don't allow duplicated rows
Result: number of systems is ok
|
Story Points: | --- |
Clone Of: | Environment: | ||
Last Closed: | 2011-02-01 10:32:30 UTC | Type: | --- |
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: | 518253 |
Description
Xixi
2009-10-02 07:43:12 UTC
Looks like the problematic query is "packages_from_server_set" from src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml - basically the server count can go over # of systems in the SSM b/c the server_id selected on rhnServerPackage may not be unique, and adding a UNIQUE to the query gave better results. In my test on the internal reproducer, the user_id is 1 (satadm user), rhnSet.label='system_list' (500 systems selected in the SSM), and package removal page under SSM shows many entries with more than 500 affected systems in the UI, as well as from db by substituting the above params into the packages_from_server_set query: SELECT PN.ID || '|' || PE.ID || '|' || PA.ID AS id_combo, PN.name || '-' || PE.evr.as_vre_simple() || '.' || PA.label nvre, PA.label AS arch, X.num_systems AS num_systems FROM rhnPackageName PN, rhnPackageEVR PE, rhnPackageArch PA, (SELECT SP.name_id, SP.evr_id, SP.package_arch_id, COUNT (SP.server_id) num_systems FROM rhnServerPackage SP, rhnSet S WHERE s.user_id = 1 AND s.label = 'system_list' AND s.ELEMENT = SP.server_id GROUP BY SP.name_id, SP.evr_id, SP.package_arch_id) X WHERE PE.ID = X.evr_id AND PN.ID = X.name_id AND PA.ID = X.package_arch_id ORDER BY UPPER (PN.NAME); Narrowing it down to the subquery which gives us X, where COUNT (SP.server_id) num_systems gives more than the total # of systems in the SSM - "SELECT SP.name_id, SP.evr_id, SP.package_arch_id, COUNT (SP.server_id) num_systems FROM rhnServerPackage SP, rhnSet S WHERE s.user_id = 1 AND s.label = 'system_list' AND s.ELEMENT = SP.server_id GROUP BY SP.name_id, SP.evr_id, SP.package_arch_id;" gives: NAME_ID EVR_ID PACKAGE_ARCH_ID NUM_SYSTEMS ---------- ---------- --------------- ----------- 4612 5819 100 390 1461 7554 100 428 1696 7555 101 503 ... with NUM_SYSTEMS can be more than # of systems in the SSM. Suspecting the server_id is not unique, if I add in a UNIQUE on rhnServerPackage.server_id - "SELECT SP.name_id, SP.evr_id, SP.package_arch_id, COUNT (UNIQUE (SP.server_id)) num_systems FROM rhnServerPackage SP, rhnSet S WHERE s.user_id = 1 AND s.label = 'system_list' AND s.ELEMENT = SP.server_id GROUP BY SP.name_id, SP.evr_id, SP.package_arch_id;" gives: NAME_ID EVR_ID PACKAGE_ARCH_ID NUM_SYSTEMS ---------- ---------- --------------- ----------- 16 5839 101 500 23 7245 101 1 23 10165 101 499 ... with NUM_SYSTEMS always <= # of systems in the SSM. There're no uniqueness constraints on the rhnServerPackage table, but in this case, we only care about packages on a per-server basis, so adding a UNIQUE may be the fix, although I need to check what the "duplicate" entries in rhnServerPackage would be. /etc/sysconfig/rhn/universe.deploy.sql - ... create table rhnServerPackage ( server_id number, name_id number, evr_id number , package_arch_id number ) tablespace DATA_TBS enable row movement ; create sequence rhn_server_package_id_seq; ... Please be so kind and add a few key words to the Technical Notes of this Bugzilla entry using the following structure: Cause: Consequence: Fix: Result: For details, see: https://bugzilla.redhat.com/page.cgi?id=fields.html#cf_release_notes Thanks Technical note added. If any revisions are required, please edit the "Technical Notes" field accordingly. All revisions will be proofread by the Engineering Content Services team. New Contents: Cause: Consequence: Fix: Result: Technical note updated. If any revisions are required, please edit the "Technical Notes" field accordingly. All revisions will be proofread by the Engineering Content Services team. Diffed Contents: @@ -1,7 +1,7 @@ -Cause: +Cause: underlying query returned more than one row for a server. -Consequence: +Consequence: duplicated rows made number of affected systems higher than is should be. -Fix: +Fix: don't allow duplicated rows -Result:+Result: number of systems is ok Dimitar, could you please tell me which errata contains the fix fir this issue? Please, ignore my previous comment. An advisory has been issued which should help the problem described in this bug report. This report is therefore being closed with a resolution of ERRATA. For more information on therefore solution and/or where to find the updated files, please follow the link below. You may reopen this bug report if the solution does not work for you. http://rhn.redhat.com/errata/RHBA-2011-0189.html |