-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Hello all,
it was easier than expected :) The XML file containing the statements is included in /usr/share/rhn/lib/rhn.jar I extracted it, removed the trailing semicolon from com/redhat/rhn/common/db/datasource/xml/System_queries.xml (line 2235), recreated the JAR archive and restarted Spacewalk. The problem is now gone :D I created a pull request in the Spacewalk Github repository: https://github.com/spacewalkproject/spacewalk/pull/686 Regards, Christian - -----Ursprüngliche Nachricht----- Von: spacewalk-list-boun...@redhat.com <spacewalk-list-boun...@redhat.com> Im Auftrag von Hailer, Christian Gesendet: Montag, 11. Februar 2019 11:14 An: spacewalk-list@redhat.com Betreff: Re: [Spacewalk-list] SQL error in SSM after update to 2.9 * PGP Signed: 02/11/2019 at 11:14:30 AM Hi Paul-Andre, thank you for your response! I did some further debugging and used p6spy to log the actual SQL statements before they are processed by the Oracle driver. Here's what I've seen: 1549872508746|2|statement|connection 4|url jdbc:p6spy:oracle:oci:@//DB-FQDN/SCHEMA|SELECT COUNT(*) as count FROM rhnSet LEFT JOIN rhnServerEntitlementView ON rhnSet.element = rhnServerEntitlementView.server_id WHERE rhnSet.user_id = ? AND rhnSet.label = ? AND (rhnServerEntitlementView.is_base IS NULL OR rhnServerEntitlementView.is_base = 'Y') AND (rhnServerEntitlementView.label IS NULL OR rhnServerEntitlementView.label <> ?)|SELECT COUNT(*) as count FROM rhnSet LEFT JOIN rhnServerEntitlementView ON rhnSet.element = rhnServerEntitlementView.server_id WHERE rhnSet.user_id = 5 AND rhnSet.label = 'system_list' AND (rhnServerEntitlementView.is_base IS NULL OR rhnServerEntitlementView.is_base = 'Y') AND (rhnServerEntitlementView.label IS NULL OR rhnServerEntitlementView.label <> 'enterprise_entitled') 1549872508755|4|statement|connection 4|url jdbc:p6spy:oracle:oci:@//DB-FQDN/SCHEMA|SELECT COUNT(*) as count FROM rhnSet LEFT JOIN rhnServerEntitlementView ON rhnSet.element = rhnServerEntitlementView.server_id WHERE rhnSet.user_id = ? AND rhnSet.label = ? AND (rhnServerEntitlementView.is_base IS NULL OR rhnServerEntitlementView.is_base = 'Y') AND (rhnServerEntitlementView.label IS NULL OR rhnServerEntitlementView.label <> ?)|SELECT COUNT(*) as count FROM rhnSet LEFT JOIN rhnServerEntitlementView ON rhnSet.element = rhnServerEntitlementView.server_id WHERE rhnSet.user_id = 5 AND rhnSet.label = 'system_list' AND (rhnServerEntitlementView.is_base IS NULL OR rhnServerEntitlementView.is_base = 'Y') AND (rhnServerEntitlementView.label IS NULL OR rhnServerEntitlementView.label <> 'enterprise_entitled') 1549872508765|8|statement|connection 4|url jdbc:p6spy:oracle:oci:@//DB-FQDN/SCHEMA|SELECT COUNT(*) as count FROM rhnSet LEFT JOIN rhnServerEntitlementView ON rhnSet.element = rhnServerEntitlementView.server_id WHERE rhnSet.user_id = ? AND rhnSet.label = ? AND (rhnServerEntitlementView.is_base IS NULL OR rhnServerEntitlementView.is_base = 'Y') AND (rhnServerEntitlementView.label IS NULL OR rhnServerEntitlementView.label <> ?)|SELECT COUNT(*) as count FROM rhnSet LEFT JOIN rhnServerEntitlementView ON rhnSet.element = rhnServerEntitlementView.server_id WHERE rhnSet.user_id = 5 AND rhnSet.label = 'system_list' AND (rhnServerEntitlementView.is_base IS NULL OR rhnServerEntitlementView.is_base = 'Y') AND (rhnServerEntitlementView.label IS NULL OR rhnServerEntitlementView.label <> 'enterprise_entitled') 1549872508786|14|statement|connection 4|url jdbc:p6spy:oracle:oci:@//DB-FQDN/SCHEMA|SELECT COUNT(*) as count FROM rhnSet WHERE rhnSet.user_id = ? AND rhnSet.label = ? AND ( SELECT COUNT(*) FROM rhnServerFeaturesView WHERE rhnSet.element = rhnServerFeaturesView.server_id AND rhnServerFeaturesView.label = ? ) = 0;|SELECT COUNT(*) as count FROM rhnSet WHERE rhnSet.user_id = 5 AND rhnSet.label = 'system_list' AND ( SELECT COUNT(*) FROM rhnServerFeaturesView WHERE rhnSet.element = rhnServerFeaturesView.server_id AND rhnServerFeaturesView.label = 'ftr_kickstart' ) = 0; ==> /var/log/tomcat6/catalina.out <== 2019-02-11 09:08:28,794 [TP-Processor1] ERROR com.redhat.rhn.common.db.datasource.CachedStatement - Error while processing cached statement sql: SELECT COUNT(*) as count FROM rhnSet WHERE rhnSet.user_id = ? AND rhnSet.label = ? AND ( SELECT COUNT(*) FROM rhnServerFeaturesView WHERE rhnSet.element = rhnServerFeaturesView.server_id AND rhnServerFeaturesView.label = ? ) = 0; com.redhat.rhn.common.db.WrappedSQLException: ORA-00933: SQL command not properly ended As you can see, the first 3 statements are processed without any problems, but the 4th one is reported as "not properly ended". This one is the only one using a ";" at the end. I looked up the source code, and the mentioned statement is the only one with a semicolon at the end: https://github.com/spacewalkproject/spacewalk/blob/SPACEWALK-2.9/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml#L2235 The responsible commit was added with version 2.9, and maybe the Postgres driver doesn't complain about the extra semicolon, so no one else reported something similar as I'm the only one using Oracle? :) I'll try to find the Java class using the statement and maybe recompile it without the semicolon. Regards, Christian - -----Ursprüngliche Nachricht----- Von: spacewalk-list-boun...@redhat.com <spacewalk-list-boun...@redhat.com> Im Auftrag von Paul-Andre Panon Gesendet: Freitag, 8. Februar 2019 20:15 An: spacewalk-list@redhat.com Betreff: Re: [Spacewalk-list] SQL error in SSM after update to 2.9 On Fri, 8 Feb 2019 06:29:27 +0000, "Hailer, Christian" <christian.hai...@interhyp.de> wrote: >Doesn't anybody else experience this problem? Is there anybody out there using Spacewalk 2.9 with Oracle? > >Regards, Christian Maybe not. PostgreSQL will meet most people's needs quite well. >>Hello all, >> >>I recently updated Spacewalk from version 2.8 to 2.9. Since then I'm unable to manage multiple servers at once. >>As soon as I select some servers on the "Systems" tab and click the "Manage" button, I get an internal server error. Looking at the Tomcat catalina.out log, I can see the following SQL error: >> >>2019-02-01 07:14:02,504 [TP-Processor11] ERROR com.redhat.rhn.common.db.datasource.CachedStatement - Error while processing cached statement sql: SELECT COUNT(*) as count >> FROM rhnSet >> WHERE >> rhnSet.user_id = ? AND >> rhnSet.label = ? AND ( >> SELECT COUNT(*) >> FROM rhnServerFeaturesView >> WHERE rhnSet.element = rhnServerFeaturesView.server_id >> AND rhnServerFeaturesView.label = ? >> ) = 0; >>com.redhat.rhn.common.db.WrappedSQLException: ORA-00933: SQL command not properly ended >> >>What could be the problem here? Any help would be appreciated! >> >>Regards, >>Christian Hailer Hi Christian, The query looks fine. I tried to run it in my Postgres database and it appeared to work when I substituted values for the parameters as direct SQL. i.e. spaceschema=# SELECT COUNT(*) as count FROM rhnSet WHERE rhnSet.user_id = 4 AND rhnSet.label = 'system_list' AND ( SELECT COUNT(*) FROM rhnServerFeaturesView WHERE rhnSet.element = rhnServerFeaturesView.server_id AND rhnServerFeaturesView.label = 'ftr_config' ) = 0; count - ------- 0 (1 row) You could try running that command against your Oracle db with the standard Oracle query tools and see if you get more detailed information on where the failure happens. If the command fails then maybe the Oracle definition for those 2 tables has somehow diverged or is corrupt. If that command works then perhaps there's a type mismatch between the parameter being passed and the column type in the Oracle schema, even though that isn't a problem with the PostgreSQL schema. I would actually expect a different error in that case but I'm far from authoritative on Oracle. The PostgreSQL column definitions for the relevant tables are spaceschema=# \d rhnSet Table "public.rhnset" Column | Type | Modifiers - ---------------+-----------------------+----------- user_id | numeric | not null label | character varying(32) | not null element | numeric | not null element_two | numeric | element_three | numeric | . . . spaceschema=# \d rhnServerFeaturesView View "public.rhnserverfeaturesview" Column | Type | Modifiers - -----------+-----------------------+----------- server_id | numeric | label | character varying(32) | It might be that somehow a hidden character such as a CR has crept into the Oracle version of the query in the source code, although I would expect that error to come up earlier when the SQL statement is prepared/pre-compiled. Another possibility might be that the "prepared" statements aren't actually being submitted for pre-compilation by the Oracle JDBC driver, but that the Oracle driver just merges in the parameters and submits the results as dynamic SQL. If that's the case, then one of those parameters could contain a ';' or something similar, rendering the merged dynamic statement invalid. A Bobby Tables moment. That's just a wild speculative guess though as I have no idea how the Oracle JDBC driver handles "prepared" statements, I just vaguely remember an experience with an early version of some other driver that did that. If those approaches give you any more clues, then you could try seeing if it's a known issue or filing a bug report. Good luck, Paul-Andre _______________________________________________ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list * Hailer Christian <christian.hai...@interhyp.de> * 0x138E7A5A(L) _______________________________________________ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list -----BEGIN PGP SIGNATURE----- Version: PGP Universal 3.4.2 (Build 353) Charset: iso-8859-1 wsFVAwUBXGJoCiMT++4TjnpaAQhyBxAAnOQl1MRTzpwbyZ64ILZPRa/fseIyvD/Y LXxeUhTv+mQWnxjb6gC1UDr18w3L23NE+7aDdJ3D+YAxqvPtXR7FMx6xRSFBA52Q r+nYGm/AzCFTCJ2InWlzPMDt1JU15GXvtsVpmrR6ez0QyuPIn89+1fJR9INJioI9 z+MZ5NBn5G/0fRNExpP2a8rs0zXs+j6USh028LfUTb8HMfL5fbMMP0ikrL+Cf78V cxtlPMTyxiqfQh5od2hFKJ4yOZuIFaurLJTGxy3ycJGX0XWXf/TCKm7qPRzxsHhQ dW2UcntjxxBNEhoKb9H7LsC0MB5y7u7ESZvpkZCbtjuDpyLxauRorQq4sOjuwSmo M3tgL8cpacS8NAnffz2N68VytbHDtraLliTkA74FAvoMPO+TBtfu7cBVeifjs8EJ Ca67vDTmJxw8GR6EaQ71W2sT5MkJHkgtWNKKPThEjE62OxpNkApXTgiLKis1VPiW GEPjabL1KNOWdCujlHdVPysCg5tDyXZa1SmG3ClgpQZbD4CqAuH8Syixiw5g6Psu gJWhplD5kxuA9OFHJOHZDNbE516U+lhgXBtYxYZaYpaSBFuO9nNBzy8kfYQgSaX5 ijz58vLiWcrjiHFlI0+Bv26ThJpBtPcqJwPilzmBG2HNZD+4y3VTTAYcQkX0zZT9 fcJsG5Surws= =yIq+ -----END PGP SIGNATURE----- _______________________________________________ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list