Internal Server Error in Overview and Systems pages within SUSE Manager Server web-UI

• Home/Overview
• Systems

Rest of the pages are displayed correctly. Following error message appears in /var/log/rhn/rhn_web_ui.log

...
2021-09-01 15:09:01,455 [ajp-nio-0:0:0:0:0:0:0:1-8009-exec-19] INFO  org.directwebremoting.log.accessLog - Method execution failed:
com.redhat.rhn.common.db.WrappedSQLException: ERROR: more than one row returned by a subquery used as an expression
        at com.redhat.rhn.common.translation.SqlExceptionTranslator.postgreSqlException(SqlExceptionTranslator.java:50)
        at com.redhat.rhn.common.translation.SqlExceptionTranslator.sqlException(SqlExceptionTranslator.java:40)
        at com.redhat.rhn.common.db.NamedPreparedStatement.execute(NamedPreparedStatement.java:143)
        at com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:494)
        at com.redhat.rhn.common.db.datasource.CachedStatement.lambda$executeChecking$1(CachedStatement.java:451)
        at org.hibernate.jdbc.WorkExecutor.executeReturningWork(WorkExecutor.java:55)
... 

and /var/lib/pgsql/data/log/postgresql.log reports:

...
2021-09-01 16:57:09.075 CEST susemanager susemanager [5232]ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-09-01 16:57:09.075 CEST susemanager susemanager [5232]STATEMENT:  select 'c3p0 ping' from dual
2021-09-01 16:57:09.173 CEST susemanager susemanager [5231]ERROR:  more than one row returned by a subquery used as an expression
2021-09-01 16:57:09.173 CEST susemanager susemanager [5231]STATEMENT:  SELECT S.id, S.NAME, S.info, S.created,
               (SELECT 1
                  FROM rhnServerFeaturesView SFV
                 WHERE SFV.server_id = S.id
                   AND SFV.label = 'ftr_system_grouping') AS selectable,
                        (select C.name
                                from rhnChannel C
                                inner join rhnServerChannel SC on SC.channel_id = C.id
                                where SC.server_id = S.id
                                and C.parent_channel IS NULL) AS CHANNEL_LABELS,
               (SELECT wc.login
                  FROM web_contact wc
                 WHERE wc.id = s.creator_id) as creator_name
          FROM rhnServer S
         WHERE S.org_id = $1
           AND EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE USP.user_id = $2 AND USP.server_id = S.id)
           AND S.created > current_timestamp - numtodsinterval($3 * 86400, 'second')
        ORDER BY S.created DESC
2021-09-01 16:57:09.174 CEST susemanager susemanager [5231]ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-09-01 16:57:09.174 CEST susemanager susemanager [5231]STATEMENT:  select 'c3p0 ping' from dual
2021-09-01 16:57:30.393 CEST susemanager susemanager [5233]ERROR:  more than one row returned by a subquery used as an expression
2021-09-01 16:57:30.393 CEST susemanager susemanager [5233]STATEMENT:  SELECT SERVER_ID AS ID, OUTDATED_PACKAGES, SERVER_NAME, security_errata, bug_errata, enhancement_errata,
                  SERVER_ADMINS, GROUP_COUNT, MODIFIED, CHANNEL_LABELS, CHANNEL_ID, HISTORY_COUNT,
                  unique_crash_count, total_crash_count, LAST_CHECKIN_DAYS_AGO, PENDING_UPDATES, OS, RELEASE,
                  SERVER_ARCH_NAME, LAST_CHECKIN, LOCKED, PROXY_ID AS IS_RHN_PROXY
          FROM  rhnServerOverview
          WHERE  server_id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25)
        ORDER BY  UPPER(COALESCE(SERVER_NAME, '(none)')), SERVER_ID
...
 

echo "select s.id serverid, C.id channelid, C.name, C.label, C.parent_channel from rhnserver s, rhnChannel C, rhnServerChannel SC where SC.server_id = s.id and SC.channel_id = C.id and C.parent_channel IS NULL order by s.id;" | spacewalk-sql --select-mode - > /tmp/query-output.txt

This creates the file /tmp/query-output.txt containing the output of the query.

2)  change to directory /tmp and run the following commands

cd /tmp

Followed by:

cat query-output.txt | awk '{print $1}' | sort | uniq -c | awk '{print $1,$2}' | grep -v ^1 

This output tells which 'server ID' has more than one entry in that file: '<how many entries> <server ID>'
Example:

2 1000018033 

Now you can grep for the details of this 'server ID' to understand the 'more than one entry' error you are seeing:

cat query-output.txt | grep <server ID>

Example output:

1000018033 | sles12-sp4-pool-x86_64-clone
1000018033 | SLES12-SP5-Pool for x86_64

3) With the ID from the previous output the hostname of a problematic client can be obtained using spacecmd

spacecmd system_list | grep <server ID>

If prompted, enter the SUSE Manager admin password to continue.  Attempt to run the command again.
Example output:

problem-client.acme.com : 1000018033 

In the SUSE Manager WebUI, note that ID correlates with the 'SUSE Manager System ID' in the 'System > Details > Overview' section of that client.

4) Any 'server ID' that has more than one entry in the query-output.txt would be causing the issue.

spacecmd system_delete <system ID>