[Spacewalk-list] SQL error in SSM after update to 2.9
ndegz
nndegz at gmail.com
Thu Mar 28 16:44:05 UTC 2019
Is there a patch available for this bug or should we unpack, fix and repack
the jar?
regards
N
On Tue, Feb 12, 2019 at 1:31 AM Hailer, Christian <
christian.hailer at interhyp.de> wrote:
> -----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-bounces at redhat.com <spacewalk-list-bounces at redhat.com>
> Im Auftrag von Hailer, Christian
> Gesendet: Montag, 11. Februar 2019 11:14
> An: spacewalk-list at 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-bounces at redhat.com <spacewalk-list-bounces at redhat.com>
> Im Auftrag von Paul-Andre Panon
> Gesendet: Freitag, 8. Februar 2019 20:15
> An: spacewalk-list at 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.hailer at 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 at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
> * Hailer Christian <christian.hailer at interhyp.de>
> * 0x138E7A5A(L)
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at 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 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/20190328/ac36fb55/attachment.htm>
More information about the Spacewalk-list
mailing list