[Spacewalk-list] SQL error in SSM after update to 2.9

[Paul-Andre Panon]

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