Bug 601984 - spacewalk-report inventory returns SQL Error ORA-01489: result of string concatenation is too long
Summary: spacewalk-report inventory returns SQL Error ORA-01489: result of string conc...
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Server
Version: 530
Hardware: All
OS: Linux
medium
low
Target Milestone: ---
Assignee: Jan Pazdziora
QA Contact: Garik Khachikyan
URL:
Whiteboard:
Keywords:
Depends On:
Blocks: sat540-blockers sat540-reporting
TreeView+ depends on / blocked
 
Reported: 2010-06-09 00:31 UTC by Xixi
Modified: 2018-10-27 12:50 UTC (History)
6 users (show)

(edit)
Clone Of:
(edit)
Last Closed: 2010-10-28 15:01:09 UTC


Attachments (Terms of Use)

Description Xixi 2010-06-09 00:31:23 UTC
Description of problem:
For customer, "spacewalk-reports inventory" command bails out with an SQL error "ORA-01489: result of string concatenation is too long" -

SQL error occurred, traceback follows...
Traceback (most recent call last):
  File "/usr/bin/spacewalk-report", line 130, in ?
    h.execute()
  File "/usr/share/rhn/server/rhnSQL/sql_base.py", line 161, in execute
    return apply(self._execute_wrapper, (self._execute, ) + p, kw)
  File "/usr/share/rhn/server/rhnSQL/driver_cx_Oracle.py", line 118, in _execute_wrapper
    raise apply(sql_base.SQLError, ret)
server.rhnSQL.sql_base.SQLError: (1489, 'ORA-01489: result of string concatenation is too long\n', "select rhnserver.id as server_id, rhnserver.name as profile_name, rhnservernetwork.hostname, trim(rhnservernetwork.ipaddr) as ip_address, ( select login from web_contact where rhnserver.creator_id = web_contact.id ) as registered_by, to_char(rhnserver.created, 'YYYY-MM-DD HH24:MI:SS') as registration_time, ( select to_char(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnserverinfo where rhnserver.id = server_id ) as last_checkin_time, running_kernel as kernel_version, ( select count(distinct rhnpackage.name_id) from rhnpackage, rhnserverneededpackagecache where rhnserver.id = rhnserverneededpackagecache.server_id and rhnserverneededpackagecache.package_id = rhnpackage.id ) as packages_out_of_date, ( select count(*) from rhnServerNeededErrataCache where rhnserver.id = server_id ) as errata_out_of_date, rhnchannel.name as software_channel, rhnconfigchannel.name as configuration_channel, entitlement_data.name as entitlements, group_data.name as system_group, ( select name from web_customer where rhnserver.org_id = web_customer.id ) as organization, virtual_data.host_system_id as virtual_host, ( select rhnserverarch.name from rhnserverarch where rhnserver.server_arch_id = rhnserverarch.id ) as architecture, ( ( select count(*) || ' CPUs' from rhncpu where rhnserver.id = rhncpu.server_id ) || network_data.col ) as hardware from rhnserver, ( select server_id, hostname, ipaddr from rhnservernetwork where id in ( select min(id) from rhnservernetwork group by server_id ) ) rhnservernetwork, rhnserverchannel, rhnchannel, rhnserverconfigchannel, rhnconfigchannel, ( select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id, rhnservergroup.name from rhnservergroupmembers, rhnservergroup where rhnservergroupmembers.server_group_id = rhnservergroup.id (+) and rhnservergroup.group_type is not null ) entitlement_data, ( select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id, rhnservergroup.name from rhnservergroupmembers, rhnservergroup where rhnservergroupmembers.server_group_id = rhnservergroup.id (+) and rhnservergroup.group_type is null ) group_data, ( select host_system_id, virtual_system_id from rhnvirtualinstance where host_system_id is not null ) virtual_data, ( select server_id, max(sys_connect_by_path(val, '; ')) col from ( select server_id, name||' '||ip_addr||'/'||netmask||' '||hw_addr val, row_number() over (partition by server_id order by name, rowid) rn from rhnservernetinterface ) start with rn = 1 connect by prior rn = rn - 1 and prior server_id = server_id group by server_id ) network_data where rhnserver.id = rhnservernetwork.server_id (+) and rhnserver.id = rhnserverchannel.server_id (+) and rhnserverchannel.channel_id = rhnchannel.id (+) and rhnserver.id = rhnserverconfigchannel.server_id (+) and rhnserverconfigchannel.config_channel_id = rhnconfigchannel.id (+) and rhnserver.id = entitlement_data.server_id (+) and rhnserver.id = group_data.server_id (+) and rhnserver.id = virtual_data.virtual_system_id (+) and rhnserver.id = network_data.server_id (+) order by server_id, rhnchannel.parent_channel nulls first, rhnchannel.id, rhnconfigchannel.id, entitlement_data.server_group_id, group_data.server_group_id, virtual_data.host_system_id")

Version-Release number of selected component (if applicable):
Red Hat Network (RHN) Satellite 5.3.0

How reproducible:
Always (with customer's db).

Steps to Reproduce:
1. Import customer's db on 5.3 Satellite.
2. run: spacewalk-reports inventory 

Actual results:
Error/traceback, see above.

Expected results:
Successful reporting, no error/traceback.

Additional info:

Comment 1 Xixi 2010-06-09 00:34:51 UTC
It looks like the concatenation done in the inventory query can exceed limits set by Oracle.  

Following is the SQL query for "spacewalk-report inventory", taken from /usr/share/spacewalk/reports/data/inventory -
...
        select rhnserver.id as server_id, rhnserver.name as profile_name,
                rhnservernetwork.hostname, trim(rhnservernetwork.ipaddr) as ip_address,
                ( select login from web_contact where rhnserver.creator_id = web_contact.id ) as registered_by,
                to_char(rhnserver.created, 'YYYY-MM-DD HH24:MI:SS') as registration_time,
                ( select to_char(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnserverinfo where rhnserver.id = server_id ) as last_checkin_time,
                running_kernel as kernel_version,
                ( select count(distinct rhnpackage.name_id) from rhnpackage, rhnserverneededpackagecache
                        where rhnserver.id = rhnserverneededpackagecache.server_id
                                and rhnserverneededpackagecache.package_id = rhnpackage.id ) as packages_out_of_date,
                ( select count(*) from rhnServerNeededErrataCache where rhnserver.id = server_id ) as errata_out_of_date,
                rhnchannel.name as software_channel,
                rhnconfigchannel.name as configuration_channel,
                entitlement_data.name as entitlements,
                group_data.name as system_group,
                (
                select name from web_customer where rhnserver.org_id = web_customer.id
                ) as organization,
                virtual_data.host_system_id as virtual_host,
                (
                select rhnserverarch.name from rhnserverarch where rhnserver.server_arch_id = rhnserverarch.id
                ) as architecture,
                (
                        (
                        select count(*) || ' CPUs' from rhncpu where rhnserver.id = rhncpu.server_id
                        ) || network_data.col
                ) as hardware
        from rhnserver,
                (
                select server_id, hostname, ipaddr
                from rhnservernetwork
                where id in (
                        select min(id)
                        from rhnservernetwork
                        group by server_id
                        )
                ) rhnservernetwork,
                rhnserverchannel, rhnchannel,
                rhnserverconfigchannel, rhnconfigchannel,
                (
                select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id, rhnservergroup.name
                from rhnservergroupmembers, rhnservergroup
                where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
                        and rhnservergroup.group_type is not null
                ) entitlement_data,
                (
                select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id, rhnservergroup.name
                from rhnservergroupmembers, rhnservergroup
                where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
                        and rhnservergroup.group_type is null
                ) group_data,
                (
                select host_system_id, virtual_system_id
                from rhnvirtualinstance
                where host_system_id is not null
                ) virtual_data,
                (
                select server_id, max(sys_connect_by_path(val, '; ')) col
                from (
                        select server_id,
                                name||' '||ip_addr||'/'||netmask||' '||hw_addr val,
                                row_number() over (partition by server_id order by name, rowid) rn
                        from rhnservernetinterface
                )
                start with rn = 1
                connect by prior rn = rn - 1
                        and prior server_id = server_id
                group by server_id
                ) network_data
        where rhnserver.id = rhnservernetwork.server_id (+)
                and rhnserver.id = rhnserverchannel.server_id (+)
                and rhnserverchannel.channel_id = rhnchannel.id (+)
                and rhnserver.id = rhnserverconfigchannel.server_id (+)
                and rhnserverconfigchannel.config_channel_id = rhnconfigchannel.id (+)
                and rhnserver.id = entitlement_data.server_id (+)
                and rhnserver.id = group_data.server_id (+)
                and rhnserver.id = virtual_data.virtual_system_id (+)
                and rhnserver.id = network_data.server_id (+)
        order by server_id, rhnchannel.parent_channel nulls first, rhnchannel.id,
                rhnconfigchannel.id, entitlement_data.server_group_id, group_data.server_group_id,
                virtual_data.host_system_id;
...

Manually running this via SQLPlus gives "ERROR at line 27:
ORA-01489: result of string concatenation is too long"

This corresponds to "select count(*) || ' CPUs' from rhncpu where rhnserver.id = rhncpu.server_id
                        ) || network_data.col
                ) as hardware" and if you refer to the network_data subquery 

"                select server_id, max(sys_connect_by_path(val, '; ')) col
                from (
                        select server_id,
                                name||' '||ip_addr||'/'||netmask||' '||hw_addr val,
                                row_number() over (partition by server_id order by name, rowid) rn
                        from rhnservernetinterface
                )
                start with rn = 1
                connect by prior rn = rn - 1
                        and prior server_id = server_id
                group by server_id;"

Running this in SQLplus gives the same error.  So depending on the network configurations of systems, the concatenation above can become too long, resulting in the error.

Comment 3 Jan Pazdziora 2010-08-05 15:26:41 UTC
The reason for the error is that presumably one of the server has many network interfaces and the length of the string after concatenation is greater than 4000, which is the SQL limit. I probably should be able to change the behaviour to be able to process resulting strings up to the length of 32000 but again, there will be a limit there.

So maybe what we could do is to just trim the output to 4000 and append something like "..." to the end of this field, and have another report which would have the hardware inventory in rows, instead of concatenated in one field.

Comment 4 Jan Pazdziora 2010-08-09 11:35:25 UTC
Fixed in Spacewalk master, 92f09ae169b6697ae9ed2053ee99cea716e1fd88.

In the end I've changed the concatting operation to use clob instead of varchar, to avoid hitting the size limit.

Comment 8 Garik Khachikyan 2010-09-02 15:22:04 UTC
# VERIFIED

The issue was possible to reproduce on older spacewalk-reports version (spacewalk-reports-0.9.1-1.el5.noarch.rpm)

On registered system with eth[0..100] network slots the query throws the Oracle exception.

On the corrected version of (spacewalk-reports-1.2.2-1.el5.noarch.rpm) the issue disappeared and the result displayed correctly (with all that eth*-s there).

Comment 10 Clifford Perry 2010-10-28 14:56:02 UTC
The 5.4.0 RHN Satellite and RHN Proxy release has occurred. This issue has been resolved with this release. 


RHEA-2010:0801 - RHN Satellite Server 5.4.0 Upgrade
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10332

RHEA-2010:0803 - RHN Tools enhancement update
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10333

RHEA-2010:0802 - RHN Proxy Server 5.4.0 bug fix update
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10334

RHEA-2010:0800 - RHN Satellite Server 5.4.0
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10335

Docs are available:

http://docs.redhat.com/docs/en-US/Red_Hat_Network_Satellite/index.html 

Regards,
Clifford


Note You need to log in before you can comment on or make changes to this bug.