Bug 1042909

Description of problem:
The Spacewalk Webfrontend system view https://SERVER/rhn/systems/RequiringReboot.do shows systems that have already been rebooted.

This is due to the fact, that timezones are not handled correctly and the system boot time is in another format as the package isntallation time.

How reproducible:
My system do run in Central European Timezone (CET or MEZ), which is currently GMT + 1 hour.

Install an errata with has the keyword "reboot_suggested" at a system.

This system will then be listed at rhn/systems/RequiringReboot.do.

Reboot the system. Don't forget to call "rhn_check" after booting, because otherwise the system last_boot entry is not updated.

The system will still be listend as requiring reboot.

Workaround:
wait an hour (if your are at CET) and reboot the system again. Spacewalk will then stop to claim, that this systems requires a reboot.

The code that is responsible for this is at
https://git.fedorahosted.org/cgit/spacewalk.git/tree/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml

<query name="having_errata_with_keyword_applied_since_last_reboot_list" params="org_id, user_id, keyword">
SELECT DISTINCT S.id, S.NAME,
       (SELECT 1
          FROM rhnServerFeaturesView SFV
         WHERE SFV.server_id = 2S.id
           AND SFV.label = 'ftr_system_grouping') AS selectable
  FROM rhnServer S,
       rhnErrata E,
       rhnServerInfo SI,
       rhnServerPackage SP,
       rhnPackage P,
       rhnErrataPackage EP
 WHERE S.org_id = :org_id
   AND EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE USP.user_id=:user_id AND USP.server_id = S.id)
   AND SI.server_id = S.id
   AND SP.server_id = S.id
   AND P.evr_id = SP.evr_id
   AND P.name_id = SP.name_id
   AND EP.errata_id = E.id AND EP.package_id = P.id
   AND (to_timestamp('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(S.last_boot, 'second')) &lt; SP.installtime
   AND E.id IN (SELECT EK.errata_id FROM rhnErrataKeyword EK WHERE EK.keyword = :keyword)
</query>

To check this, "spacewalk-sql -i" can be used with following query:

select S.name,advisory_name,to_timestamp('1970-01-01', 'YYYY-MM-DD')+numtodsinterval(S.last_boot,'second'),SP.installtime,PN.name
  FROM rhnServer S,
       rhnErrata E,
       rhnServerInfo SI,
       rhnServerPackage SP,
       rhnPackage P,
       rhnErrataPackage EP, rhnPackagename PN
 WHERE SI.server_id = S.id
   AND SP.server_id = S.id
   AND P.evr_id = SP.evr_id
   AND P.name_id = SP.name_id AND P.name_id = PN.id
   AND EP.errata_id = E.id AND EP.package_id = P.id
   AND (to_timestamp('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(S.last_boot, 'second')) < SP.installtime
   AND E.id IN (SELECT EK.errata_id FROM rhnErrataKeyword EK WHERE EK.keyword = 'reboot_suggested' )

I created a Test Errata saying updating to bash-4.1.2-15.el6_4 would require a reboot.

I applied this errata, rebooted the system and called rhn_check.

Webfrondend says:
Last Booted:    13.12.13 15:08:22 MEZ

rpm -qa --last says:
bash-4.1.2-15.el6_4.x86_64                    Fr 13 Dez 2013 14:22:08 CET

SQL-Query says:
 localhost.localdomain  | TEST-2013:0001 | 2013-12-13 14:08:22.3122+01   | 2013-12-13 14:22:08+01 | bash

While last_boot is in GMT time, package installtime is in CET.

A reboot after 15:22 (package have been installed at 14:22) solved this problem this time.