Description of problem: I have Spacewalk and DB server on separate machines with following setup: DB server with PostgreSQL in EST/New York timezone (-5) Spacewalk nightly in CET/Prague timezone (+1) Spacewalk WebUI in JST/Tokio timezone (+9) Version-Release number of selected component (if applicable): Spacewalk nightly How reproducible: Always Steps to Reproduce: 1. Systems -> Configuration -> Deploy files -> Deploy 2. Systems -> Provisioning -> Snapshots -> Rollback to snapshot Actual results: Mismatch in timezones Expected results: Timezones are correctly handled Additional info: In next comments as I can add only one attachment to post
<some Spacewalk with PostgreSQL>/network/systems/details/history/event.pxt?sid<some sid>&hid=19 Summary: Deploy config files to system scheduled by admin Details: This action will be executed after 11/21/12 4:19:24 PM JST. This action's status is: Queued. This action has not yet been picked up. Config Files: /root/date (rev. 1) Time: 11/21/12 4:19:24 PM JST Times when this action was created: 10:19:24 PM JST (WebUI time) 2:19:24 PM CET (Spacewalk server time) 8:19:24 AM EST (Database time) select earliest_action,created,modified from rhnaction where id=19; earliest_action | created | modified ----------------------------+-------------------------------+------------------------------- 2012-11-21 08:19:24.637-05 | 2012-11-21 08:19:24.274067-05 | 2012-11-21 08:19:24.274067-05 Expected results: Time 10:19:24 PM JST is shown everywhere instead of 4:19:24 PM JST.
<some Spacewalk with PostgreSQL>/network/systems/details/history/event.pxt?sid<some sid>&hid=18 Summary: Package Synchronization scheduled by admin Details: This action will be executed after 11/21/12 10:04:10 PM JST. This action's status is: Queued. This action has not yet been picked up. Changes Scheduled: Remove rlwrap-0.37-1.el6 Time: 11/21/12 4:04:08 PM JST Times when this action was created: 10:04:08 PM JST (WebUI time) 2:04:08 PM CET (Spacewalk server time) 8:04:08 AM EST (Database time) select earliest_action,created,modified from rhnaction where id=18; earliest_action | created | modified ------------------------+-------------------------------+------------------------------- 2012-11-21 14:04:10-05 | 2012-11-21 08:04:08.812519-05 | 2012-11-21 08:04:08.812519-05 Expected results: Time 10:04:08 PM JST is shown in column Time instead of 4:04:08 PM JST.
Additional info: When connecting to database server from Spacewalk server with pgsql #show timezone; returns: US/Eastern so it seems that sessions in PostgreSQL are by default having timezone (not offset from UTC) of the DB server. That would explain values like 2012-11-21 08:19:24.637-05 Spacewalk calculates on application layer differences between Spacewalk server and WebUI so most likely scenario was that 10:19:24 PM JST was converted to Spacewalk server time that was 2:19:24 PM CET. Value 2:19:24 PM CET was inserted into DB and converted to UTC which corespondents to 2012-11-21 08:19:24.637-05. By far everything is in consistent state but when WebUI makes select select from RHNACTION table select returns values in EST/New_York due to the fact that session time zone is US/Eastern. When presenting results WebUI simply adds difference between Spacewalk server time and WebUI time in hours, which is in this case 8 hours. So this is why are getting value 4:19:24 PM JST (scenario in which DB and Spacewalk server have different time zone is not taken in case). If you would add 6 hours (difference between SW and DB) to 4:19:24 PM JST you would get 10:19:24 PM JST which is excepted time. In scenario which is described in comment #2 further investigation in Perl code is needed as value 2012-11-21 14:04:10-05 in item earliest_action is highly suspicious.
Database: oracle-xe-11.2 on Spacewalk nightly Setup is the same as on PostgreSQL DB server with PostgreSQL in EST/New York timezone (-5) Spacewalk nightly in CET/Prague timezone (+1) Spacewalk WebUI in JST/Tokio timezone (+9) Version-Release number of selected component (if applicable): Spacewalk nightly with OracleXE How reproducible: Always Steps to Reproduce: 1. Systems -> Configuration -> Deploy files -> Deploy 2. Systems -> Provisioning -> Snapshots -> Rollback to snapshot Actual results: Timezones are correctly handled
<some Spacewalk with OracleXE>/network/systems/details/history/event.pxt?sid<some sid>&hid=40 Summary: Deploy config files to system scheduled by admin Details: This action will be executed after 11/22/12 12:04:07 AM JST. This action's status is: Queued. This action has not yet been picked up. Config Files: /root/date (rev. 1) Time: 11/22/12 12:04:07 AM JST Times when this action was created: 12:04:07 AM JST (WebUI time) //day++ 16:04:07 PM CET (SW server time) 10:04:07 AM EST (DB time) select cast(EARLIEST_ACTION as timestamp with time zone),cast(CREATED as timestamp with time zone),cast(MODIFIED as timestamp with time zone) from rhnaction where id=40; CAST(EARLIEST_ACTIONASTIMESTAMPWITHTIMEZONE) --------------------------------------------------------------------------- CAST(CREATEDASTIMESTAMPWITHTIMEZONE) --------------------------------------------------------------------------- CAST(MODIFIEDASTIMESTAMPWITHTIMEZONE) --------------------------------------------------------------------------- 21-NOV-12 04.04.07.323000 PM +01:00 21-NOV-12 04.04.07.292755 PM +01:00 21-NOV-12 04.04.07.292886 PM +01:00 Actual results: Works fine
<some Spacewalk with OracleXE>/network/systems/details/history/event.pxt?sid<some sid>&hid=38 Summary: Package Synchronization scheduled by admin Details: This action will be executed after 11/21/12 11:58:39 PM JST. This action's status is: Queued. This action has not yet been picked up. Changes Scheduled: Remove rlwrap-0.37-1.el6 Time: 11/21/12 11:58:39 PM JST Times when this action was created: 11:58:39 AM JST (WebUI time) 15:58:39 PM CET (SW server time) 09:58:39 AM EST (DB time) select cast(EARLIEST_ACTION as timestamp with time zone),cast(CREATED as timestamp with time zone),cast(MODIFIED as timestamp with time zone) from rhnaction where id=38; CAST(EARLIEST_ACTIONASTIMESTAMPWITHTIMEZONE) --------------------------------------------------------------------------- CAST(CREATEDASTIMESTAMPWITHTIMEZONE) --------------------------------------------------------------------------- CAST(MODIFIEDASTIMESTAMPWITHTIMEZONE) --------------------------------------------------------------------------- 21-NOV-12 03.58.39.000000 PM +01:00 21-NOV-12 03.58.39.481810 PM +01:00 21-NOV-12 03.58.39.481881 PM +01:00
Oracle-XE creates session that has session time zone of computer that is attempting to connect, so: select sessiontimezone from dual; on SW server that is in CET (+1) returns SESSIONTIMEZONE --------------------------------------------------------------------------- +01:00 Time records are stored in UTC in DB internally but when it comes to retrieving this data, they're converted to timezone of SW server that can be whichever. On application layer this time record is converted to timezone of the WebUI.
I've also noticed strange behavior when viewing Snapshot Tags: I am using same setup as above DB server with PostgreSQL/OracleXE in EST/New York timezone (-5) Spacewalk nightly in CET/Prague timezone (+1) Spacewalk WebUI in JST/Tokio timezone (+9) following applies to SW nightly on PostgreSQL: System snapshot tag was created at 10:56:08 CET which is time of SW server this time corresponds to 18:56:08 JST and 04:56:08 EST. On https://<SW>/network/systems/details/history/snapshots/system_tags.pxt?sid=<some sid> is 04:56:08 shown as date of creation of the snapshot instead of 18:56:08 JST. select created from rhnsnapshottag where id=<id of snapshot tag>; created ------------------------------- 2012-11-22 04:56:08.672773-05 following applies to SW nightly on OracleXE System snapshot tag was created at 10:42:38 CET which is time of SW server this time corresponds to 18:42:38 JST and 04:42:38 EST. On https://<SW>/network/systems/details/history/snapshots/system_tags.pxt?sid=<some sid> is 04:42:38 shown as date of creation of the snapshot instead of 18:42:38 JST. select created from rhnsnapshottag where id=<id of snapshot tag>; CREATED --------------------------------------------------------------------------- 22-NOV-2012 04.42.38.015416 AM This select was made locally on DB machine. select created from rhnsnapshottag where id=<id of snapshot tag>; CREATED --------------------------------------------------------------------------- 22-NOV-2012 10.42.38.015416 AM This select was made from machine where Spacewalk is installed with sessiontimezone +01:00. For more info on sessiontimezone on OracleXE see comment #7. But in this case it seems no conversion to flatten difference between JST and CET was made and data are presented in CET even if WebUI is set to JST
(In reply to comment #12) > https://<SW>/network/systems/details/history/snapshots/system_tags. > pxt?sid=<some sid> is 04:42:38 shown as date of creation of the snapshot > instead of 18:42:38 JST. fail here, theres 10:42:38 shown not 04:42:38.
(In reply to comment #14) > (In reply to comment #12) > > https://<SW>/network/systems/details/history/snapshots/system_tags. > > pxt?sid=<some sid> is 04:42:38 shown as date of creation of the snapshot > > instead of 18:42:38 JST. > > fail here, theres 10:42:38 shown not 04:42:38. another fail, it's OracleXE
Another use case with same setup https://<some Spacewalk>/rhn/systems/details/Overview.do?sid=<some sid> System events, specifically items "Checked in" and "Registered" DB server with PostgreSQL/OracleXE in EST/New York timezone (-5) Spacewalk nightly in CET/Prague timezone (+1) Spacewalk WebUI in JST/Tokio timezone (+9) PostgreSQL: System was registered on 10:33:03 PM JST, 02:33:03 PM CET and 08:33:03 AM EST. On system overview is following shown: Checked in: 11/22/12 10:33:06 JST Registered: 11/22/12 10:33:03 JST These values are OK and corresponds to reality. select s.created, si.checkin from rhnserver s, rhnserverinfo si where si.server_id = s.id; created | checkin -------------------------------+------------------------------- 2012-11-22 08:33:03.843679-05 | 2012-11-22 08:33:06.426185-05 Now THIS is getting weird, all connections in PostgreSQL are in timezone are in US Eastern/New York, Spacewalk adds automatically 8 hours (difference between JST and CET). So I would expect that values "Checked in" and "Registered" would be more like 04:33:0x PM JSP, but it seems that 6 hours (difference between JST and EST) were added too. This is completely new behavior. OracleXE System was registered on 11:23:37 PM JST, 03:23:27 PM CET and 09:23:27 AM EST. On system overview is following shown: Checked in: 11/22/12 11:23:30 JST Registered: 11/22/12 11:23:27 JST These values are OK and corresponds to reality. select s.created, si.checkin from rhnserver s, rhnserverinfo si where si.server_id = s.id; CREATED --------------------------------------------------------------------------- CHECKIN --------------------------------------------------------------------------- 22-NOV-12 09.23.27.875697 AM 22-NOV-12 09.23.30.048272 AM This select was made on the DB machine. CREATED --------------------------------------------------------------------------- CHECKIN --------------------------------------------------------------------------- 22-NOV-12 03.23.27.875697 PM 22-NOV-12 03.23.30.048272 PM This select was made on machine with SW, session time zone is +01:00. On OracleXE everything behaves correctly according previous discoveries.
Another use case with same setup https://<some Spacewalk>/rhn/configuration/file/fileDetails.do?cfid=<some cfid> System events, specifically items "Checked in" and "Registered" DB server with PostgreSQL/OracleXE in EST/New York timezone (-5) Spacewalk nightly in CET/Prague timezone (+1) Spacewalk WebUI in JST/Tokio timezone (+9) PostgreSQL I've created (10:07:59 CET,18:07:59 JST) configuration file and immediately after it, Spacewalk puts me on page where can I see details for this file. Creation Date: 0 seconds ago Last Modified: 0 seconds ago by admin This seems to be OK. Database records: select CREATED, MODIFIED from RHNCONFIGFILE where id=<some cfid>; created | modified -------------------------------+------------------------------- 2012-11-26 04:07:59.980016-05 | 2012-11-26 04:07:59.980016-05 OracleXE I've created (10:16:12 CET,18:16:12 JST) configuration file and immediately after it, Spacewalk puts me on page where can I see details for this file. Creation Date: 0 seconds ago Last Modified: 0 seconds ago by admin This seems to be OK. Database records: select CREATED, MODIFIED from RHNCONFIGFILE where id=<some cfid>; CREATED --------------------------------------------------------------------------- MODIFIED --------------------------------------------------------------------------- 26-NOV-12 04.16.12.302665 AM 26-NOV-12 04.16.12.682795 AM This select was made on DB machine. select CREATED, MODIFIED from RHNCONFIGFILE where id=<some cfid>; CREATED --------------------------------------------------------------------------- MODIFIED --------------------------------------------------------------------------- 26-NOV-12 10.16.12.302665 AM 26-NOV-12 10.16.12.682795 AM This select was made on SW server, again connection is in timezone of SW server. On OracleXE and PostgreSQL on SW nightly this works correctly.
Is this still a problem on Spacewalk 2.7?
I'd say so, as afaik no changes to database timezones handling have been made.
Spacewalk 2.8 (and older) has already reached it's End Of Life. Thank you for reporting this issue and we are sorry that we were not able to fix it before end of life. If you would still like to see this bug fixed and are able to reproduce it against current version of Spacewalk 2.9, you are encouraged change the 'version' and re-open it.