Bug 922928

+++ This bug was initially created as a clone of Bug #835199 +++

Description of problem:
When trying to resolve some duplicate systems, I found that using the "Duplicate Hostname" tab under Systems->Duplicate Systems does not show system names with different case as the same systems.  If I use the "Duplicate IP Address" tab instead, I see all the duplicate systems.

For example, I have three systems that are named like this:
DBQLDBIDB09 - systemID 1000020200
DBQLDBIDB09 - systemID 1000020260
dbqldbidb09 - systemID 1000020300

The currently active one is the systemID 1000020300 (dbqldbidb09).  All three show up under the "Duplicate IP Address" tab, but only the first two (that match exactly, with all uppercase) show up under the "Duplicate hostname" tab.

I would expect system name matching to be case insensitive and show all three systems.

Version-Release number of selected component (if applicable):
Satellite 5.4.1

How reproducible:
Always

Steps to Reproduce:
1. Register the same system/hostname multiple times, but use different case for the hostname
2. Go to Systems -> Duplicate Systems -> Duplicate Hostname
3. Due to the differences in hostname case, the hostname doesn't show up as duplicate
  
Actual results:
Hostnames that are the same except for differences in case are not considered to be duplicate.

Expected results:
Hostnames that are the same except for differences in case should still be considered duplicate.

Additional info:
We believe filing this as a bug (and not feature request) is appropriate, because the current behavior does not accurately reflect the state of the environment.

--- Additional comment from Mark Huth on 2012-06-25 15:07:21 EDT ---

I believe this is the code for the /rhn/systems/DuplicateHostName.do page:

java/code/src/com/redhat/rhn/manager/system/SystemManager.java:

2782     /**
2783      * List duplicate systems by hostname
2784      * @param user the user doing the search
2785      * @param inactiveHours the number of hours a system hasn't checked in
2786      *          to consider it inactive
2787      * @return List of DuplicateSystemBucket objects
2788      */
2789     public static List listDuplicatesByHostname(User user, Long inactiveHours) {
2790         List<DuplicateSystemGrouping> duplicateSystems = listDuplicates(user,
2791                 "duplicate_system_ids_hostname",
2792                 Collections.EMPTY_LIST, inactiveHours);

java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml:

1774 <mode name="duplicate_system_ids_hostname" class="com.redhat.rhn.frontend.dto.NetworkDto">
1775     <query params="uid, inactive_date">
1776         SELECT distinct S.id as id, S.name as name, NI.hostname as key, si.checkin as last_checkin,
1777              (select count(*)  from rhnServerInfo Si2 where  S.id = Si2.server_id and Si2.checkin &lt; :inactive_date) as inactive
1778         from rhnServer S inner join
1779          rhnUserServerPerms USP on S.id = USP.server_id inner join
1780          rhnServerNetwork NI on NI.server_id = S.id inner join
1781              rhnServerInfo si on si.server_id = S.id inner join
1782          ( Select NI2.hostname, count(NI2.server_id) as syscount
1783             from rhnServerNetwork NI2 inner join
1784                     rhnUserServerPerms USP2 on NI2.server_id = USP2.server_id
1785             where USP2.user_id = :uid
1786                 group by NI2.hostname having count(NI2.server_id) > 1) SUMM on SUMM.hostname = NI.hostname
1787         where USP.user_id = :uid
1788     </query>
1789 </mode>

Complex query, but I'm guessing this is the all important bit:

"on SUMM.hostname = NI.hostname"

... and since Oracle is case-sensitive, this will only match strings with the same case, hence the behaviour as reported by the customer.  I guess we could use the Oracle upper/lower functions to make the comparison case-insensitive, like so:

"on lower(SUMM.hostname) = lower(NI.hostname)"

I'd lean toward this being a bug rather than a feature request.  You would think that, generally, machines with the same name but in different cases would still be referring to the same machine, and so a search of machines with the same name should do a case-insensitive search.

--- Additional comment from Mark Huth on 2012-08-09 22:38:15 EDT ---

Created attachment 603386 [details]
Make the duplicate_system_ids_hostname query case insensistive

This patch makes the Duplicate Hostname page SQL query (duplicate_system_ids_hostname associated with page /rhn/systems/DuplicateHostName.do) consider as duplicates, those systems with the same name but in different cases.  I have tested it and it seems to work well.

Need to make the query called duplicate_system_ids_hostname_key case insensitive as well.  That's next.

--- Additional comment from Mark Huth on 2012-08-13 21:44:11 EDT ---

Created attachment 604125 [details]
Make the duplicate_system_ids_hostname* queries case insensistive

This patch makes the Duplicate Hostname SQL queries (the duplicate_system_ids_hostname* queries in com/redhat/rhn/common/db/datasource/xml/System_queries.xml which are associated with the pages /rhn/systems/DuplicateHostName.do and /rhn/systems/DuplicateSystemsCompare.do) consider as duplicates, those systems with the same name but in different cases.  I have tested it and it seems to work well.

--- Additional comment from Mark Huth on 2012-08-13 22:07:49 EDT ---

To test:

Before applying the patch:
1) Register a system to satellite with the same hostname but different cases, eg
rhel6.3.mhuth.bne.redhat.com
RHEL6.3.mhuth.bne.redhat.com
rhel6.3.mhuth.bne.redhat.COM

2) Go to /rhn/systems/DuplicateIPList.do and see that they are regarded as duplicates because they have the same IP address, however going to the /rhn/systems/DuplicateHostName.do page and none of the hostnames appear.

After applying the patch and restarting tomcat service
3) Go to the /rhn/systems/DuplicateHostName.do page and observe that the 3 machines are duplicates under the key of rhel6.3.mhuth.bne.redhat.com (using the duplicate_system_ids_hostname query)

4) Click on the Compare Systems link and the 3 machines appear as duplicates here too (using the duplicate_system_ids_hostname_key query)


--- Additional comment from Mark Huth on 2013-02-07 17:20:38 EST ---

Created attachment 694808 [details]
Make the duplicate_system_ids_hostname* queries case insensistive

This patch makes the Duplicate Hostname SQL queries (the duplicate_system_ids_hostname* queries in com/redhat/rhn/common/db/datasource/xml/System_queries.xml which are associated with the pages /rhn/systems/DuplicateHostName.do and /rhn/systems/DuplicateSystemsCompare.do) consider as duplicates, those systems with the same hostname but in different cases.

The customer has tested the patch and confirms it works for them.

One way you can reproduce the problem is by re-registering the same system a number of times so that it gets a different systemid number on the satellite, and before each time you register the system, you change the case of its hostname, like so:

# hostname hostname.example.com
# rhn_register
# hostname HOSTNAME.example.com
# rhn_register
# hostname HostName.Example.Com
# rhn_register

So there will be 3 separate profiles, but its really the same machine.  And the patch will mean that when you click on the Systems -> Duplicate Systems -> Duplicate Hostnames, all 3 machines will appear under the hostname 'hostname.example.com' even though the cases are different.

Addressed in commit d81d82975519353f53a8f5ff095998911e179af4

Fix for this bug is present in Spacewalk 2.0, closing this bug as CURRENTRELEASE.