Bug 484686 - [RFE] Spacewalk do not handle properly timezones
Summary: [RFE] Spacewalk do not handle properly timezones
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Spacewalk
Classification: Community
Component: Server
Version: 0.4
Hardware: All
OS: Linux
low
medium
Target Milestone: ---
Assignee: Jan Pazdziora
QA Contact: Miroslav Suchý
URL:
Whiteboard:
Depends On:
Blocks: spacewalk-rfe
TreeView+ depends on / blocked
 
Reported: 2009-02-09 15:02 UTC by Miroslav Suchý
Modified: 2012-12-07 19:11 UTC (History)
5 users (show)

Fixed In Version:
Doc Type: Enhancement
Doc Text:
Clone Of:
Environment:
Last Closed: 2012-12-07 19:11:07 UTC
Embargoed:


Attachments (Terms of Use)

Description Miroslav Suchý 2009-02-09 15:02:44 UTC
Description of problem:
I have both Spacewalk and client machine in CET timezone. Spacewalk display wrong time in "Checked in".

Version-Release number of selected component (if applicable):
Spacewalk 0.4

How reproducible:
deterministic

Steps to Reproduce:
1. On Spacewalk: cp /usr/share/zoneinfo/CET /etc/localtime
2. in /etc/sysconfig/clock set ZONE="CET"
3. do steps 1 and 2 on client
4. register client to Spacewalk
5. on client run rhn_check; date
6. look on system detail on Spacewalk and value of "Checked in"
  
Actual results:
[root@dhcp77-189 ~]# rhn_check; date
Mon Feb  9 15:55:06 CET 2009

..../rhn/systems/details/Overview.do?sid=1000010020
Checked In:  	9.2.09 9:55:05 CET

Expected results:
[root@dhcp77-189 ~]# rhn_check; date
Mon Feb  9 15:55:06 CET 2009

..../rhn/systems/details/Overview.do?sid=1000010020
Checked In:  	9.2.09 15:55:05 CET

Comment 1 Miroslav Suchý 2009-02-10 15:02:57 UTC
Additionaly: if I try to access scout list I get:
==> /var/log/httpd/error_log <==
[Tue Feb 10 15:59:34 2009] [error] Execution of /var/www/html/network/monitoring/scout/index.pxt failed at Tue Feb 10 15:59:34 2009: Cannot determine local time zone
[Tue Feb 10 15:59:35 2009] [error] Traceback sent to msuchy at /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm line 729.

Comment 2 Jay Dobies 2009-03-10 19:07:17 UTC
I wasn't able to easily reproduce the first issue (the rhn_check time being off). I found a scenario that would result in the times being incorrect, however it's a somewhat unlikely scenario to encounter.

The timezone on the client seems reasonably stable. I was able to dork around with it without causing any discrepancies on the server.

Likewise, I was able to change the UI's time zone to something different from the server itself with no issue.

The issue arises when there is data already in the server when its timezone is changed. It doesn't look like the Oracle DATE field carries timezone information. So it appears we will assume the time we get from the database (in this case, the CHECKIN field of rhnServerInfo) is from that timezone. If, at the time of the request (i.e. using the UI) the server timezone is different than when the data was originally inputted, the math to convert from server timezone to UI timezone will produce an unexpected result.

There are a few options, but all of which really should be done on a global scale.The type of DATE columns could be changed to TIMESTAMP, which could associate a time zone with the data instead of the database as a whole. Another option is to change the data type to a LONG and use epoch millis, only converting to a formatted date at the last moment before displaying to the user. This would have the added benefit of simplifying issues between oracle and pgsql. Admittedly, both approaches are a very significant impact on the existing codebase.

Comment 3 Jay Dobies 2009-03-16 20:02:07 UTC
I'm a bit unsure how this blocks 464114. The only time I was able to cause any sort of issues regarding the time zone was when I switched the time zone of the server after there was data already populated in the DB.

Miroslav, can you provide me some more info on why this blocks 464114?

Also, I'm going to file the perl page error as a new BZ. That's visible under much more common circumstances (any european timezone as compared to switching time zones) and appears to be a distinctly separate issue.

Comment 4 Miroslav Suchý 2009-04-07 08:21:02 UTC
In the time I filed this BZ I was unable to pick up any action. Which blocks me.
I can do it now. So I will remove the blocker.
However I think the BZ is still valid, I only should make the reproducer more solid. I will check it again...

Comment 5 Miroslav Suchý 2009-04-07 09:03:53 UTC
Ok, I do confirm that id do not block me anymore. 
I still think we should address this issue, but I'm not more relaxed about targeted release. Feel free to target to release where you can handle the impact to existing codebase.

Comment 6 Jan Pazdziora 2010-11-19 16:03:20 UTC
Mass-moving to space13.

Comment 7 Jan Pazdziora 2011-02-28 18:38:12 UTC
Taking.

Mirek, could you please clarify which part of the bugzilla you'd like to see addressed, and how?

Comment 8 Miroslav Suchý 2011-02-28 20:53:29 UTC
I would like to see addressed that part, which I described in comment #0 in steps to reproduce.

And I have no idea, how it should be solved. I would expectd that it would require some investigation.

But I could anticipate that we have to either send timezone info (which I think we already do) and then somehow reflect this information before we save it to database.

Comment 9 Miroslav Suchý 2011-04-11 07:32:02 UTC
We did not have time for this one during Spacewalk 1.4 time frame. Mass moving to Spacewalk 1.5.

Comment 10 Miroslav Suchý 2011-04-11 07:36:35 UTC
We did not have time for this one during Spacewalk 1.4 time frame. Mass moving to Spacewalk 1.5.

Comment 11 Jan Pazdziora 2011-07-20 11:49:46 UTC
Aligning under space16.

Comment 12 Jan Pazdziora 2011-08-19 09:14:31 UTC
I did verify that even if I do

# date -s '1980-01-01'
Tue Jan  1 00:00:00 CET 1980
# date
Tue Jan  1 00:00:00 CET 1980
# rhn_check
# date
Tue Jan  1 00:00:06 CET 1980
# 

on the client, the WebUI says

Checked In: 	8/19/11 9:12:12 AM GMT

Thus, the time setting on the client does not seem to affect the Checked In value.

Comment 13 Jan Pazdziora 2011-08-19 09:21:25 UTC
(In reply to comment #12)
> on the client, the WebUI says
> 
> Checked In:  8/19/11 9:12:12 AM GMT

... which is correct -- my account is set in GMT, even if the server runs in EDT:

# date
Fri Aug 19 05:21:04 EDT 2011
# echo "select to_char(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnServerInfo;" | spacewalk-sql --select-mode -

TO_CHAR(CHECKIN,'YY
-------------------
2011-08-19 05:12:12

#

Comment 14 Jan Pazdziora 2011-08-19 09:29:03 UTC
And the sysdate would return

# echo "select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;" | spacewalk-sql --select-mode -

TO_CHAR(SYSDATE,'YY
-------------------
2011-08-19 05:22:57

Then, indeed, when I do

# cp /usr/share/zoneinfo/CET /etc/localtime
cp: overwrite `/etc/localtime'? y
# service oracle-xe restart

I get

# echo "select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;" | spacewalk-sql --select-mode -

TO_CHAR(SYSDATE,'YY
-------------------
2011-08-19 11:23:39

# echo "select server_id, to_char(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnServerInfo;" | spacewalk-sql --select-mode -

 SERVER_ID TO_CHAR(CHECKIN,'YY
---------- -------------------
1000010000 2011-08-19 05:12:12

and the WebUI reports

Checked In: 	8/19/11 3:12:12 AM GMT

So the problem is that the checkin value is stored as "string value" -- it is pretty stable at the "2011-08-19 05:12:12" timezone-less value, no matter what we do.

What then happens is conversion to the Web-user's timezone which fails because if I change the timezone setting underneath the database server, the notion of sysdate changes, and so does the other arithmentics there.

The workaround is simple -- never change the timezone setting after some user data was populated to the database.

Comment 15 Jan Pazdziora 2011-08-19 09:30:21 UTC
I'm adding an RFE to this bug's summary.

We are not likely to refactor the date/time handling in Spacewalk anytime soon. Patches that would handle the task without introducing regressions would certainly be welcome for review thou.

Comment 16 Jan Pazdziora 2011-08-19 09:35:14 UTC
(In reply to comment #14)

> 
> So the problem is that the checkin value is stored as "string value" -- it is
> pretty stable at the "2011-08-19 05:12:12" timezone-less value, no matter what
> we do.

When I do rhnreg_ks of another system (well, the same client system but new profile) some 13 minutes later, when the timezone has already been changed from original EDT to CEST, I get

# echo "select server_id, to_char(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnServerInfo;" | spacewalk-sql --select-mode -

 SERVER_ID TO_CHAR(CHECKIN,'YY
---------- -------------------
1000010000 2011-08-19 05:12:12
1000010001 2011-08-19 11:25:10

And indeed, for this new profile, the WebUI is again correct:

Checked In: 	8/19/11 9:25:19 AM GMT

Comment 17 Jan Pazdziora 2012-12-07 19:11:07 UTC
The CET problem from comment 1 was fixed in Spacewalk master via bug 490524.

The problem described in comment 0 is caused by *changing* the timezone on the server. I believe that with the timestamp database types in Spacewalk 1.8, this problem has solved as well.

Closing as CURRENTRELEASE.

What we might have is a problem with clients in different timezone than the server when they send a local time without mentioning their time zone or offset, and a problem when the database server is not on the same host as the Spacewalk applications -- tracked in bug 878899 now.


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