Bug 601984

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.

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.

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.

# 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).

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