[Spacewalk-list] SQL error in SSM after update to 2.9
Hailer, Christian
christian.hailer at interhyp.de
Tue Feb 12 06:30:32 UTC 2019
-----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-----
More information about the Spacewalk-list
mailing list