-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
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
-----BEGIN PGP SIGNATURE-----
Version: PGP Universal 3.4.2 (Build 353)
Charset: iso-8859-1
wsFVAwUBXGFLBiMT++4TjnpaAQh9RA/7B7HOSTrf49po4+IC40k73vnVmMDhoPSI
7DFP8fBFoFxqYiW4yHjgjIq85p5lMSwCMVC5I0N10ORgVlXVTLJaDCb3rFnN8iV7
a0D7sadT4sCEhU+y2O5MMtkRP1wKFhD+jmPeMDWsWpIg0nmkPLmg7j7EjgWQusGz
24y7v35vT0BmTgj3qto5FO27DGI/hK08HiZFUCFKqGs8E5re454HurO/Sijs9u5c
M4yHm7Tnfwm6Mouzwg6Z7riM75R7szmaGkdvnc12SKuAbAu4QaEpTqxDQ9GwuPza
Q3VVmRTzBENO2Sfz/mQgk3pq3oKuVkLIgloBDwdWM5C4sk2e0F3Xqr6bnad2GG/J
9MmAPStwFghvxwyj4eD/eqscJM/WDFXd8JAEZohQrol7VYkd65fxvqhGWzU27mfb
nXqp9EAhMCbuar3H3cpDKkltnHBCLPJ0BwQiMKJc1+yrX8WpexQ2OtTlR6wDt5wX
pGMsshIwL5MsuHo1V4DRTceYCJKrOaHCu4rd3BBwKCGRfVc1TwJ9aIGjH0Cq0k/o
hOsLZF9I7VovnN31XoYrHHWTQVJ9TVL1NkvvyAQlFyJM4NU8YDwfAEaWO/gsPyWX
EXgnjUDPtX9QS2Ojtg+LuFXTzQOcELFeCfXIKTGa6TZqyc4NqKADgzP2PPOlcZ3j
nnZ2VHoHA0o=
=oZUg
-----END PGP SIGNATURE-----
_______________________________________________
Spacewalk-list mailing list
Spacewalk-list@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list
