[Spacewalk-list] Error 500 on snapshots pages
Pierre Casenove
pedro109 at hotmail.com
Mon Jul 25 13:13:54 UTC 2011
Hello,I've modified 4 queries in order to get this work... But I don't have an oracle set up to test my queries.Moreover, I'm not sure the queries actually returns the legitimate results.
I insist on the fact that I'm not a DBA. I'm really not sure that the rewritten queries are correct. Please have a look on the diffs
Here are the diff on the three modifed files, in folder /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource/xml:diff config_queries.xml /root/config_queries.xml.orig85,90c85,90< FROM rhnConfigFile CF< LEFT OUTER JOIN rhnConfigRevision CR ON (CR.config_file_id = CF.id)< LEFT OUTER JOIN rhnSnapshotConfigRevision SCR ON (SCR.config_revision_id = CR.id)< LEFT OUTER JOIN rhnConfigFileName CFN ON (CFN.id = CF.config_file_name_id)< LEFT OUTER JOIN rhnConfigContent CCon ON (CCon.id = CR.config_content_id)< LEFT OUTER JOIN rhnChecksumView Csum ON (Csum.id = CCon.checksum_id)---> FROM rhnConfigContent CCon,> rhnConfigFileName CFN,> rhnConfigFile CF,> rhnConfigRevision CR,> rhnSnapshotConfigRevision SCR,> rhnChecksumView Csum91a92,96> AND SCR.config_revision_id = CR.id> AND CR.config_content_id = CCon.id (+)> AND CR.config_file_id = CF.id> AND CF.config_file_name_id = CFN.id> AND CCon.checksum_id = Csum.id (+)
diff SystemGroup_queries.xml /root/SystemGroup_queries.xml.orig16,22c16,25< (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS< FROM rhnSnapshot SN< LEFT OUTER JOIN rhnSnapshotServerGroup SSG ON (SSG.snapshot_id = SN.id)< LEFT OUTER JOIN rhnServerGroup SG ON (SG.id = SSG.server_group_id)< LEFT OUTER JOIN rhnServerGroupType SGT ON (SGT.id = SG.group_type)< WHERE SN.id = :ss_id< AND SN.server_id = :sid---> (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS> FROM rhnServerGroupType SGT,> rhnServerGroup SG,> rhnSnapshotServerGroup SSG,> rhnSnapshot SN> WHERE SN.id = :ss_id> AND SN.server_id = :sid> AND SN.id = SSG.snapshot_id> AND SSG.server_group_id = SG.id> AND SG.group_type = SGT.id (+)90,101c93,107< SELECT VSGM.group_id AS ID,< SG.name AS GROUP_NAME,< SGT.label AS GROUP_TYPE_LABEL,< (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS< FROM rhnServerGroup SG< LEFT OUTER JOIN rhnVisServerGroupMembership VSGM ON (VSGM.group_id = SG.id)< LEFT OUTER JOIN rhnServerGroupType SGT ON (SGT.id = CAST(VSGM.group_type as numeric) )< LEFT OUTER JOIN rhnUserServerGroupPerms USGP ON (USGP.server_group_id = SG.id)< WHERE VSGM.org_id = :org_id< AND VSGM.server_id = :sid< AND USGP.user_id = :user_id< ORDER BY UPPER(SG.name)---> SELECT VSGM.group_id AS ID,> SG.name AS GROUP_NAME,> SGT.label AS GROUP_TYPE_LABEL,> (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS> FROM rhnServerGroupType SGT,> rhnServerGroup SG,> rhnVisServerGroupMembership VSGM,> rhnUserServerGroupPerms USGP> WHERE VSGM.org_id = :org_id> AND VSGM.server_id = :sid> AND VSGM.group_id = SG.id> AND VSGM.group_type = SGT.id (+)> AND USGP.user_id = :user_id> AND USGP.server_group_id = SG.id> ORDER BY UPPER(SG.name)
diff Package_queries.xml /root/Package_queries.xml.orig30,52c30,75< PE.id AS EVR_ID,< PN.name AS NAME,< PE.version AS VERSION,< PE.release AS RELEASE,< PE.epoch AS EPOCH,< PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,< UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE< FROM rhnPackageName PN< LEFT OUTER JOIN rhnPackageNEVRA PNEVRA ON (PNEVRA.name_id = PN.id)< LEFT OUTER JOIN rhnPackageEVR PE ON (PE.id = PNEVRA.evr_id)< LEFT OUTER JOIN rhnSnapshotPackage SP ON (SP.nevra_id = PNEVRA.id)< LEFT OUTER JOIN rhnSnapshot S ON (S.id = SP.snapshot_id)< LEFT OUTER JOIN rhnPackage P ON (P.id = PN.id)< LEFT OUTER JOIN rhnChannelPackage CP ON (CP.package_id = P.id)< LEFT OUTER JOIN rhnSnapshotChannel SC ON (SC.channel_id = CP.channel_id)< WHERE S.id = :ss_id< AND S.server_id = :sid< AND NOT EXISTS (SELECT 1 FROM rhnPackageSyncBlacklist PSB WHERE< PSB.package_name_id = PN.id AND org_id is NULL OR org_id = :org_id)< AND SC.snapshot_id = :ss_id< AND NOT EXISTS (SELECT 1 FROM rhnServerPackage SEP WHERE< SEP.server_id = :sid AND SEP.name_id = PN.id AND SEP.evr_id = PE.id)< ORDER BY 8---> PE.id AS EVR_ID,> PN.name AS NAME,> PE.version AS VERSION,> PE.release AS RELEASE,> PE.epoch AS EPOCH,> PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,> UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE> FROM rhnPackageName PN,> rhnPackageEVR PE,> rhnPackageNEVRA PNEVRA,> rhnSnapshotPackage SP,> rhnSnapshot S> WHERE S.id = :ss_id> AND S.server_id = :sid> AND S.id = SP.snapshot_id> AND SP.nevra_id = PNEVRA.id> AND PNEVRA.name_id = PN.id> AND NOT EXISTS (SELECT 1 FROM rhnPackageSyncBlacklist PSB WHERE> PSB.package_name_id = PN.id AND org_id is NULL OR org_id = :org_id)> AND PNEVRA.evr_id = PE.id> AND NOT EXISTS (> SELECT 1 FROM rhnServerPackage SP> WHERE SP.server_id = :sid> AND SP.name_id = PN.id> AND SP.evr_id = PE.id> )> MINUS> SELECT PN.id AS NAME_ID,> PE.id AS EVR_ID,> PN.name AS NAME,> PE.version AS VERSION,> PE.release AS RELEASE,> PE.epoch AS EPOCH,> PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,> UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE> FROM rhnPackageName PN,> rhnPackageEVR PE,> rhnPackage P,> rhnChannelPackage CP,> rhnSnapshotChannel SC> WHERE SC.snapshot_id = :ss_id> AND SC.channel_id = CP.channel_id> AND CP.package_id = P.id> AND P.name_id = PN.id> AND P.evr_id = PE.id> ORDER BY 8
> Date: Fri, 22 Jul 2011 19:44:02 +0200
> From: msuchy at redhat.com
> To: spacewalk-list at redhat.com
> Subject: Re: [Spacewalk-list] Error 500 on snapshots pages
>
> On 07/22/2011 08:15 AM, Pierre Casenove wrote:
> > Here is the SQL statement executed:
> > ERROR: syntax error at or near "MINUS" at character 869
>
> https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#MINUSkeyword
>
> Patches are welcome.
>
> --
> Miroslav Suchý
> Red Hat Satellite Engineering
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110725/22069697/attachment.htm>
More information about the Spacewalk-list
mailing list