Time shifts when replicating from Oracle because upgrade logic is not turned off

[GoogleCodeExporter]

What steps will reproduce the problem?

1. Deploy Oracle to MySQL replication. Both DBMS in GMT, Linux in CET (+01:00).
2. Insert date/time value into a DATE (Oracle) field and let it replicate to 
DATETIME in MySQL.

What is the expected output?

Correct replication, as previously.

What do you see instead?

Diff:

< 1000-01-01_01:00:00
< 2014-06-01_12:08:25
< 2014-05-31_11:16:45
< 1982-10-25_00:01:01
< 2015-12-01_12:11:11
< 2037-01-28_11:10:10
< 2000-03-01_00:01:01
< 2014-03-30_04:30:35
< 2014-03-09_03:30:35
< 2014-10-26_02:30:35
< 2014-11-02_02:30:35
< 1000-01-01_01:00:00

---
> 1000-01-01_00:00:00
> 2014-06-01_10:08:25
> 2014-05-31_09:16:45
> 1982-10-24_23:01:01
> 2015-12-01_11:11:11
> 2037-01-28_10:10:10
> 2000-02-29_23:01:01
> 2014-03-30_02:30:35
> 2014-03-09_02:30:35
> 2014-10-26_01:30:35
> 2014-11-02_01:30:35
> 1000-01-01_00:00:00

What is the possible cause?

Oracle extractor does not mark events as time-zone aware, hence applier uses 
upgrade logic and shifts the time to machine's time zone:

INFO   | jvm 1    | 2014/12/17 19:31:55 | 2014-12-17 18:31:55,474 [ora_my - 
q-to-dbms-0] INFO  applier.MySQLApplier Found a non-time zone-aware event while 
in TZ-aware mode: seqno=0
INFO   | jvm 1    | 2014/12/17 19:31:55 | 2014-12-17 18:31:55,474 [ora_my - 
q-to-dbms-0] INFO  applier.MySQLApplier Resetting MySQL session time zone back 
to global value

The problem can be fixed by forcing slave's JVM time zone to GMT with now a 
deprecated tpm option:

--java-user-timezone=GMT \

With it, time is replicated correctly, because upgrade logic shift's to GMT 
(+00:00, which is no shift):

INFO   | jvm 1    | 2014/12/17 19:31:55 | 2014-12-17 18:31:55,474 [ora_my - 
q-to-dbms-0] INFO  applier.MySQLApplier Resetting time zones used for date-time 
to enable non-time zone-aware operation: new tz=Greenwich Mean Time

What is the proposed solution?

1.) Add time-zone aware flag to Oracle extractor generated events. Here's how 
MySQL does it:

com.continuent.tungsten.replicator.extractor.mysql.MySQLExtractor.extractEvent(B
inlogReader)

// MySQL event extraction is time zone-aware.
dbmsEvent.addMetadataOption(
    ReplOptionParams.TIME_ZONE_AWARE, "true");
return dbmsEvent;

2.) Ensure that OracleDatabase always sets session time zone to GMT. Currently 
it's set to whatever Oracle is set to.

Original issue reported on code.google.com by linas.vi...@continuent.com on 17 Dec 2014 at 6:42

[GoogleCodeExporter]

Fix (2) is in: r2722

Original comment by linas.vi...@continuent.com on 17 Dec 2014 at 6:55

[GoogleCodeExporter]

This issue was updated by revision r2723.

Making Oracle extractor timezone aware

Original comment by stephane...@continuent.com on 18 Dec 2014 at 8:38

[GoogleCodeExporter]

This issue was updated by revision r2724.

Making Parallel extractor timezone aware

Original comment by stephane...@continuent.com on 18 Dec 2014 at 9:31

[GoogleCodeExporter]

Both Oracle extractor and parallel extractor are now fixed.

Original comment by linas.vi...@continuent.com on 18 Dec 2014 at 10:44

• Changed state: Documenting

[GoogleCodeExporter]

This didn't make it into 3.0.1-64 (GA) build.

Original comment by linas.vi...@continuent.com on 19 Dec 2014 at 6:51

• Added labels: FixedIn-3.1.0
• Removed labels: FixedIn-3.0.1

[GoogleCodeExporter]

There won't be a 3.1.0 version number.

Original comment by linas.vi...@continuent.com on 19 Jan 2015 at 2:17

• Added labels: FixedIn-4.0.0
• Removed labels: FixedIn-3.1.0