Blocker Description
We discovered a critical bug where incorrect Daylight Saving Time shifts were occurring based on the Database timezone.
Try running this Coordinator:
<coordinator-app name=“foo” frequency="${coord:hours(1)}" start="2014-11-02T04:15Z" end="2014-11-02T16:00Z" timezone=“UTC” xmlns="uri:oozie:coordinator:0.1"> <controls> <concurrency>10</concurrency> </controls> <action> <workflow> <app-path>${appPath}</app-path> <configuration> <property> <name>jobTracker</name> <value>${jobTracker}</value> </property> <property> <name>nameNode</name> <value>${nameNode}</value> </property> <property> <name>queueName</name> <value>${queueName}</value> </property> </configuration> </workflow> </action> </coordinator-app>
Note that it runs over a DST shift (at least in most US timezones).
Here's a sample of some of the actions, along with their Nominal Times:
0000013-150212140838307-oozie-rkan-C@4 2014-11-02 07:15 GMT 0000013-150212140838307-oozie-rkan-C@6 2014-11-02 09:15 GMT 0000013-150212140838307-oozie-rkan-C@5 2014-11-02 09:15 GMT 0000013-150212140838307-oozie-rkan-C@7 2014-11-02 10:15 GMT
Note that actions 5 and 6 have the same time. This is incorrect because the times are in GMT (with no DST). I've also confirmed via a debugger that these dates have the same number of seconds since epoch (so it's not just a rendering issue).
By the way, if you're in a different timezone, you'll see this problem occur on different actions because the issue is related to the timezone that your database is in. It depends on when the DST shift occurs in your timezone; for example, in "America/New_York", it happens with actions 2 and 3.
On that note, if I ask Oozie to use "America/Los_Angeles" to print the dates, I get this:
0000013-150212140838307-oozie-rkan-C@4 2014-11-02 00:15 PDT 0000013-150212140838307-oozie-rkan-C@6 2014-11-02 01:15 PST 0000013-150212140838307-oozie-rkan-C@5 2014-11-02 01:15 PST 0000013-150212140838307-oozie-rkan-C@7 2014-11-02 02:15 PST
Action 5's nominal time should be 2014-11-02 01:15 PDT, not 2014-11-02 01:15 PST.
Using the debugger some more, I verified that Oozie is creating the nominal times correctly, and writing them to the database correctly (at least, it's converting them to Java's SQL TimeStamp objects correctly; OpenJPA handles writing them). But when the problematic value is read back from the database, it has the wrong value!
Here's something interesting from the MySQL documentation:
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions.
So, I think what's happening is that it's interpreting everything in PST, and not in PST and PDT depending on the time itself. Ideally, it would just store the time since epoch, like Java's Date does, but it's doing this broken interpretation instead. In fact, this Stack Overflow I found talks about this problem.
I tried to create a unit test to show the problem without all the action stuff, but it seems like HSQLDB doesn't have this problem, so the test passes. I've confirmed that this affects Derby and MySQL; I'm not sure about Postgres, Oracle, or SQLServer.
I was able to find a workaround for Derby and one of my colleagues found one for MySQL:
- For Derby, you just need to change the JVM's timezone to GMT (see here). All you have to do is add -Duser.timezone=GMT to CATALINA_OPTS.
- For MySQL, you can either change the global timezone to GMT (which the DB admin probably won't go for), or you can add useLegacyDatetimeCode=false&serverTimezone=GMT to JDBC URL. For example:
<property> <name>oozie.service.JPAService.jdbc.url</name> <value>jdbc:mysql://HOST/oozie?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=GMT</value> </property>
I imagine the Derby workaround won't work for anything else, but perhaps the MySQL workaround would?
The easiest way to fix this is to add the user.timezone thing (to fix Derby), and have Oozie always add the JDBC configs to the JDBC URL (for the others). If Oracle, Postgres, and SQLServer don't support that, we'll have to come up with other ideas. The other ideas I had were to replace TIMESTAMP with DATETIME or the SQL equivalent of long; though these have the downside of being schema changes and possibly other side effects.
I have some homework for everyone 
Please check if the DB type listed below is affected by this, and if so, does the above workaround (or some similar configs) fix it?
- bowenzhangusa: SQLServer
- chitnis, puru, rohini, or egashira: Oracle
- Me: Postgres