[dev.icinga.com #1954] Oracle date field doesn't respect Timezones

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/1954

Created by jmosshammer on 2011-09-26 13:53:53 +00:00

Assignee: Tommi
Status: Resolved (closed on 2011-11-21 19:03:20 +00:00)
Target Version: 1.6
Last Update: 2014-12-08 14:37:25 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

---

ido2db doesn't respect timezones when writing to the database. Date fields are always delayed.
I think oracle's 'date' doesn't know timezones.

This crashes all oracle installations as every timestamp works, but stuff like instance status or last check doesn't work

Changesets

2011-10-27 20:27:42 +00:00 by Tommi c62937e

idoutils: replace date fields with timestamps, store dates as UTC #1954
refs #1954

2011-11-16 15:31:06 +00:00 by mfriedrich de3e6ea

fix oracle-upgrade-1.6.0.sql

refs #1954

2011-11-16 17:27:15 +00:00 by mfriedrich 515b200

fix typo in oracle-upgrade-1.6.0.sql, causing ORA-06512

refs #1954

2011-11-21 20:50:47 +00:00 by Tommi ce3cfbc

idoutils: register changes #1954,#2104 in changelog
refs #1954 #2104

---

Relations:

• relates #1954
• relates #1954
• relates #2104
• relates #1954
• relates #1954

[icinga-migration]

Updated by jmosshammer on 2011-09-26 13:57:49 +00:00

"This crashes all oracle installations as every timestamp works, but stuff like instance status or last check doesn't work"
This means that every non-oracle timestamp works

[icinga-migration]

Updated by mfriedrich on 2011-09-26 14:03:52 +00:00

can you explain that by example? i don'get what you mean.

[icinga-migration]

Updated by jmosshammer on 2011-09-26 14:29:15 +00:00

We got several installation at our customers where we've got the problem that date information is delayed by 2 hours (Europe/Berlin is GMT+2, so data is stored in GMT+0, i.e. no timezone correction). This problem exists also when you access raw data via sqlplus

[icinga-migration]

Updated by tgelf on 2011-09-26 16:17:52 +00:00

As I also stumbled over this problem, I tried to dig a little bit deeper. The root cause of this issue is that we are currently using "DATE" for columns with date/time information. DATE isn't aware of timezones in Oracle. Therefore you can fiddle around with TZ-related environment- and/or session-settings as long as you want, it won't help.

To get the same behaviour known from other IDO database backends we're probably forced to switch all "DATE" columns to "TIMESTAMP (0) WITH LOCAL TIMEZONE". Combined with a correct ORA_SDTZ environment setting (preferrably 'OS_TZ') this should IMO be the most elegant solution for this issue.

Cheers,
Thomas

NB: I'm not an Oracle DBA, nor did I ever write any Oracle-related code. Someone should first check whether what I stated here is really true before blindly changing anything ;-) However I'm pretty sure that we definitively need to change something to get things right...

[icinga-migration]

Updated by mfriedrich on 2011-09-26 16:21:34 +00:00

• Status changed from New to Assigned
• Assigned to set to Tommi

well i'm no oracle dba either and i don't know what these changes will do on existing databases and how this complicates the overall setup.

@tommi

could you have look please?

[icinga-migration]

Updated by tgelf on 2011-09-26 17:05:26 +00:00

Hmmm... Redmine tried to "understand" SQL :p

The column type should read TIMESTAMP ( 0 ) WITH LOCAL TIMEZONE

[icinga-migration]

Updated by Tommi on 2011-09-26 19:50:26 +00:00

there are several methods to include timezones. Because we are commited to Oracle 10gR2, the variants can be retrieved here: http://download.oracle.com/docs/cd/B19306\_01/server.102/b14225/ch4datetime.htm. In Oracle11 we have much more.
But there are really a few points to consider before to change:

-This will break current time setting behavior. Existing ido2db implementation relies on unix timestamps which should be UTC. There are 2 functions which converting timestamp to oracle date and back by counting seconds back to zero. If the timestamp is utc then the date is UTC and vice versa.
-we defined all dates in database are UTC and the client (php etc) needs to take care about. This is the current situation also in icinga web, where the user are supposed to set timezone in php. If we change timezone in database, it may lead to some confusion for the users. With "UTC only" per definition there is no need for "timestamp with timezone", if we dont need the fractal part of time
-timezone handling is depending from several parameters on OS,Sesiion and database level, which is difficult to support
-A physical change of the column datatyp will break sql compatibility. An default query on a timestamp column returns an unexpected format for all, internal and user created applications.
-Existing data needs to be migrated, means all tables
-for retrieving data with sqlplus there are several methods to transform retrieved dates into matching timezone value, new_time function(very limited) or cast date as timestamp and calculated local timezone value (http://download.oracle.com/docs/cd/B19306\_01/server.102/b14200/expressions006.htm#i1047500). This can be included in a view

My opinion in summary:
This is not a bug, its a change of a basic definition and behavior
My recommandation: do not change physical data type in oracle db , do timezone handling only on client side (as now)

[icinga-migration]

Updated by mfriedrich on 2011-09-26 20:50:34 +00:00

• Status changed from Assigned to Feedback
• Priority changed from Urgent to Normal

ok, thanks for the explaination. i expected that to be a problem and possible break on compatibility. as the unixtimestamp being used remains utc, i thought that the icinga-web config is then doing some magic on getting the data correct? the classic ui also shows the data in utc if the icinga master is utc. it just must know about the timesetting which can be provided on a config way.
changing such a thing in the database would also affect other applications using the database. i do believe that there are users out there using idoutils and oracle for their custom portal pages, dashboards and so on. by plain queries in their very own applications where we neither get feedback nor an idea who it is - until we break something. so in order to prevent that i'd vote for the application handling timezones like it is now, too.

[icinga-migration]

Updated by jmosshammer on 2011-09-27 05:34:37 +00:00

The problem is that i (as a normal user/developer and not dba) expect a database to return correct timezone information according to the databases timezone layout - like mysql does and pgsql does (there it works like charm).
I really would like to avoid front end hacks if possible - having to add 10 casts to every query doen't feel right (from a developer/end user perspective).

...and I don't think anyone other than icinga-web uses oracle as a db - at least I haven't found one.

[icinga-migration]

Updated by mfriedrich on 2011-09-27 07:07:37 +00:00

as a user i would expect the exact same value the inserting application Provider. isn't that correct with ido2db getting a unixtimestamp with localtime and inserting this? i do think that mysql and posthtesql behave badly on this situation, but correct me it i'm wrong...

[icinga-migration]

Updated by jmosshammer on 2011-09-27 07:26:20 +00:00

No, they don't - if the php timezone is set correctly everything works fine there (because datetime - otherwise I would agree that application level timezone correction is appropriate and add a handler that corrects it for all databases.
But if ido2db writes values that respect timezones in database A and B and values that don't respect timezone in database C I would call that an inconsistency.

I understand that this isn't a small change, but I'd really appreciate if we wouldn't have to focus on timezone correction for specific databases when the database provides facilities to manage these.

[icinga-migration]

Updated by mfriedrich on 2011-09-27 07:33:52 +00:00

to conclude with

• debug ido2db, what unixtime stamps are to be inserted into the database (values are binded mostly, so full debug required)
• verify the time in the database with plain sqlplus or whatever application
• post the query icinga-web doctrine would issue
• what php.ini settings / special php timezone settings affect this query?
• what is the expected outcome

if we can reproduce that behavior, we can discuss further. but with wild bug reports, it will be guessing all over.

[icinga-migration]

Updated by jmosshammer on 2011-09-27 07:38:40 +00:00

- ido2db debug

• I'll attach this later *
  verify the time in the database with plain sqlplus or whatever application
  Time is UTC, that is in Europe/Berlin -2 hours, while it's correct in mysql
  post the query icinga-web doctrine would issue
  When sqlplus retrieves the wrong time, the doctrine query doesn't really matter, because it's not a query-centric bug
  what php.ini settings / special php timezone settings affect this query?
  None, because it's not php specific
  what is the expected outcome
  Correct time, like in mysql.

[icinga-migration]

Updated by tgelf on 2011-09-27 10:30:00 +00:00

I'd suggest to change this issues topic to something like "IDO2Oracle handles timestamps different than other backends".

IDO2DB uses a custom function unixts2date() and converts unix timestamps to string-like DATE columns (YES, I know they are more than just strings). Here is the proof that this "convert unix timestamp to date" job behaves different on Oracle than it does on MySQL:

Oracle

SQL> ALTER SESSION SET nls_date_format = 'yyyy-dd-mm hh24:mi:ss';
SQL> ALTER SESSION SET time_zone='+01:00';
SQL> SELECT unixts2date('1317000000') AS ts FROM dual;
-------------------
2011-26-09 01:20:00

SQL> ALTER SESSION SET time_zone='+05:00';
SQL> SELECT unixts2date('1317000000') AS ts FROM dual;
-------------------
2011-26-09 01:20:00

MySQL

mysql> SET SESSION time_zone = '+01:00';
mysql> SELECT FROM_UNIXTIME(1317000000);
+---------------------------+
| from_unixtime(1317000000) |
+---------------------------+
| 2011-09-26 02:20:00       |
+---------------------------+

mysql> SET SESSION time_zone = '+05:00';
mysql> SELECT FROM_UNIXTIME(1317000000);
+---------------------------+
| 2011-09-26 06:20:00       |
+---------------------------+

PosgreSQL is also using FROM_UNIXTIME. I didn't test it, but I guess it behaves like the MySQL variant. There are different ways of handling datetime values in a timezone-aware application:

• ( A ) Store unix timestamps as INTegers - and be aware of the fact that one far day you'll have to be ready to face Y2038 issues. This way each frontend showing human-readable date/time values has to take care of timezones.
• ( B ) Use timezone-aware special date/time data fields in your db. It's up to each client connecting to the db to set the correct environment or session variables. This is true for each client, regardless of whether they are front- or backends.
• ( C ) Convert your timestamp to a specific timezone and store it in a timezone-ignorant string-a-like date/time data field.

Option ( C ) is IMO the worst solution and unfortunately for historical reason this is how things work in IDO2DB right now. Oracle differs slightly, it also does things the ( C ) way, but stores (string-like) DATEs for the UTC timezone instead of respecting your sessions environment.

I guess no one here wants to touch all backends, this would cause a lot of work. As Oracle is "the new kid on the block" and the least used implementation I'd opt for making Oracle behave like the others do. Fortunately this wouldn't mean any schema/data changes. It would be enough to improve unixts2date() and date2unixts(). They should respect the session timezone used by the ido2db process, that way all backends would behave the same way.

Would this be a viable solution?

Cheers,
Thomas

[icinga-migration]

Updated by Tommi on 2011-10-16 17:09:41 +00:00

• Done % changed from 0 to 50

Solution going implement:
Oracle: Change date datatype to timestamp(0) with local timezone. New PLSQL functions for handling timestamps
Mysql:Change datatype datetime to timestamp. looks like this is similar behavior like oracle local timestamp
PGSQL:Date Datatype is already timestamp
Alter ido2db session timezone to UTC for all DBs in db_hello to mark incoming unix timestamp as UTC

[icinga-migration]

Updated by Tommi on 2011-10-28 11:11:05 +00:00

• Target Version set to 1.6
• Done % changed from 50 to 100

feature is implemented. Please check with my branch

[icinga-migration]

Updated by mfriedrich on 2011-11-15 17:12:55 +00:00

from my point of view it works fine, but more testing needed.

[icinga-migration]

Updated by jmosshammer on 2011-11-17 13:46:45 +00:00

When reading data from pgsql or oracle checks are now delayed by 1 hour if the local timezone is GMT+1:

icinga=> SELECT last_check FROM icinga_hoststatus LIMIT 1;
last_check

2011-11-17 13:40:07
(1 row)

icinga=> SELECT CURRENT_TIME;
timetz

14:43:47.630869+01
(1 row)

When using mysql, the time is returned correctly. Using a default local oracle/pgsql server, local timezone is EUROPE/BERLIN (GMT+1)

[icinga-migration]

Updated by Tommi on 2011-11-17 16:46:23 +00:00

which timezone is set on icinga host? if Timezone is UTC: is the system time really UTC?

Pls. open oracle sqlplus, connect to icinga schema and provide output of following sql:
alter session set nls_timestamp_tz_format='DD-MON-YYYY HH24.MI.SS TZR';
select current_timestamp from dual;
SELECT last_check FROM hoststatus where rownum =1;

for postgresql:
I made this only from description. http://www.postgresql.org/files/documentation/books/pghandbuch/html/datatype-datetime.html Ch.8.5.3 Zeitzonen There are several ways to influence Time. Please check.

select localtimestamp;
SELECT last_check FROM icinga_hoststatus LIMIT 1;
should differ not more than your check interval

[icinga-migration]

Updated by jmosshammer on 2011-11-18 08:57:52 +00:00

---

PGSQL

icinga=> select localtimestamp;
timestamp

2011-11-18 09:51:11.76415
(1 row)

icinga=> SELECT last_check FROM icinga_hoststatus LIMIT 1;
last_check

2011-11-18 08:46:38
(1 row)

---

ORACLE

18-NOV-11 08.55.36.000000000

The system timezone is Europe/Berlin, and the host is the client (local workstation)

[icinga-migration]

Updated by Tommi on 2011-11-19 20:40:01 +00:00

looks like extended development agent version string "1.6.0-dev" is too long for table conninfo field agent_version =varchar2(8), therefore the db_hello terminates here and will omit the following alter session part, which is responsible to set session timezone correct. This issue will not happen in production(string is shorter), but i recommand to extend this field to varchar2(10).

[icinga-migration]

Updated by Tommi on 2011-11-21 19:03:20 +00:00

• Status changed from Feedback to Resolved

confirmed by marius. agent_version issue will be tracked with #2104

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:37:25 +00:00

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category changed from 57 to IDOUtils
• Icinga Version set to 1
• OS Version set to any