| Summary: | Error 500 on snapshots page - PGSQL backend | ||||||
|---|---|---|---|---|---|---|---|
| Product: | [Community] Spacewalk | Reporter: | pierre.casenove | ||||
| Component: | API | Assignee: | Michael Mráka <mmraka> | ||||
| Status: | CLOSED CURRENTRELEASE | QA Contact: | Red Hat Satellite QA List <satqe-list> | ||||
| Severity: | medium | Docs Contact: | |||||
| Priority: | unspecified | ||||||
| Version: | 1.5 | CC: | mminar | ||||
| Target Milestone: | --- | ||||||
| Target Release: | --- | ||||||
| Hardware: | x86_64 | ||||||
| OS: | Linux | ||||||
| Whiteboard: | |||||||
| Fixed In Version: | spacewalk-web-1.6.13-1 | Doc Type: | Bug Fix | ||||
| Doc Text: | Story Points: | --- | |||||
| Clone Of: | Environment: | ||||||
| Last Closed: | 2011-12-10 21:31:41 UTC | Type: | --- | ||||
| Regression: | --- | Mount Type: | --- | ||||
| Documentation: | --- | CRM: | |||||
| Verified Versions: | Category: | --- | |||||
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |||||
| Cloudforms Team: | --- | Target Upstream Version: | |||||
| Bug Depends On: | |||||||
| Bug Blocks: | 723481 | ||||||
| Attachments: |
|
||||||
Based on PostGreSQL Porting Guide, I've modified four requests and now the page are OK. I've done quick testing, I think that the requests are correct. But I haven't tested them on Oracle. More testing is required. I don't have the possibility tào get the git sources (blocked by the proxy). Please find attached the diff on the three files I've modified. Created attachment 515132 [details]
Diff output on the three files modified
The modified files are located in /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource/xml
The issue has been fixed by
commit 95b35c52805616f5710dcf9dbc53d5d511edf02c
724963 - use ANSI joins
commit 2bed66e27af71e46beebe994fce79920166f1607
724963 - use ANSI joins
commit c0c583f116e22417bdf4ab0e64463ac4203064b3
724963 - use ANSI joins
commit cdc961ae76a68266fdc7b197a099e63a0f736e41
724963 - use LEFT JOIN instead of MINUS
i've applied the patches on my pgsql installation and it stills fails:
In file SystemGroup_queries.xml, request "groups_a_system_is_in", a CAST as numeric has to be done:
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,
rhnVisServerGroupMembership VSGM
LEFT JOIN rhnServerGroupType SGT
ON CAST(VSGM.group_type as numeric) = SGT.id,
rhnUserServerGroupPerms USGP
WHERE VSGM.org_id = :org_id
AND VSGM.server_id = :sid
AND VSGM.group_id = SG.id
AND USGP.user_id = :user_id
AND USGP.server_group_id = SG.id
ORDER BY UPPER(SG.name)
Thanks,
For information, The issue is completely fixed by git d64ea81ab324d8d7bd3211093d03267f3dcbbb73 According to comment #5 switching to VERIFIED. Thanks for confirmation. |
Description of problem: On spacewalk 1.5 and postgresql 8.4 installation, when I go to Systems --> "SystemName" --> Provisionning --> Snapshots and the I click on any entry, I get the following traceback: Error message: RHN::Exception: DBD::Pg::st execute failed: ERROR: syntax error at or near "MINUS" at character 869 RHN::DB /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB.pm 228 RHN::Exception::DB::throw RHN::DB::st /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB.pm 564 RHN::DB::handle_error RHN::DB::DataSource /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource.pm 75 RHN::DB::st::execute_h RHN::DataSource /usr/lib/perl5/vendor_perl/5.8.8/RHN/DataSource.pm 108 RHN::DB::DataSource::run_query RHN::DB::SystemSnapshot /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/SystemSnapshot.pm 164 RHN::DataSource::execute_query Sniglets::Snapshot /usr/lib/perl5/vendor_perl/5.8.8/Sniglets/Snapshot.pm 119 RHN::DB::SystemSnapshot::package_list_is_servable PXT::Parser /usr/lib/perl5/vendor_perl/5.8.8/PXT/Parser.pm 160 Sniglets::Snapshot::snapshot_details PXT::Parser /usr/lib/perl5/vendor_perl/5.8.8/PXT/Parser.pm 72 PXT::Parser::expand_tag PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 510 PXT::Parser::expand_tags PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 113 PXT::ApacheHandler::pxt_parse_data PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 113 (eval) main -e 0 PXT::ApacheHandler::handler main -e 0 (eval) Version-Release number of selected component (if applicable): Spacewalk 1.5 How reproducible: go to Systems --> "SystemName" --> Provisionning --> Snapshots and click on any entry Actual results: Error 500 Expected results: The page displays correctly Additional info: Logging all SQL queries, here is the query that fails: ERROR: syntax error at or near "MINUS" at character 869 STATEMENT: 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, rhnPackageNEVRA PNEVRA, rhnSnapshotPackage SP, rhnSnapshot S WHERE S.id = $1 AND S.server_id = $2 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 = $3) AND PNEVRA.evr_id = PE.id AND NOT EXISTS ( SELECT 1 FROM rhnServerPackage SP WHERE SP.server_id = $2 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 = $1 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