MySQL extractor fails to mark certain events associated with transaction boundaries as auto-commit

[GoogleCodeExporter]

What steps will reproduce the problem?

1.  Set up Tungsten master/slave replication on MySQL with statement 
replication enabled and using a server time zone other than UTC. 

2.  Create a large table with timestamp data.  One way to do this is to prepare 
a test table using sysbench then add timestamp data using the following 
command: 

mysql> show create table sbtest;
CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

mysql> alter table sbtest add column ts timestamp default now();

3. Dump the table using mysqldump, then reload.  Here is an example. 

mysqldump -uroot db01 > db01.sql
mysql -uroot db01 < db01.sql

4. Compare dates in the master and slave tables using a command like the 
following: 

select * from sbtest order by id desc limit 20;

What is the expected output?

Dates should match in both tables.  Also, the slave log should be free of 
messages like the following: 

INFO   | jvm 1    | 2015/02/16 22:32:47 | 2015-02-17 06:32:47,634 [repltest - 
q-to-dbms-0] INFO  applier.MySQLApplier Found a non-time zone-aware event while 
in TZ-aware mode: seqno=81 fragno=1

What do you see instead?

The dates on the slave are incorrectly converted to UTC.  Also, the log is 
filled with statements like the following: 

INFO   | jvm 1    | 2015/02/16 22:32:47 | 2015-02-17 06:32:47,330 [repltest - 
q-to-dbms-0] INFO  applier.MySQLApplier Resetting time zones used for date-time 
to enable time zone-aware operation: new tz=Greenwich Mean Time
INFO   | jvm 1    | 2015/02/16 22:32:47 | 2015-02-17 06:32:47,634 [repltest - 
q-to-dbms-0] INFO  applier.MySQLApplier Found a non-time zone-aware event while 
in TZ-aware mode: seqno=81 fragno=1
INFO   | jvm 1    | 2015/02/16 22:32:47 | 2015-02-17 06:32:47,634 [repltest - 
q-to-dbms-0] INFO  applier.MySQLApplier Resetting MySQL session time zone back 
to global value
INFO   | jvm 1    | 2015/02/16 22:32:47 | 2015-02-17 06:32:47,634 [repltest - 
q-to-dbms-0] INFO  applier.MySQLApplier Resetting time zones used for date-time 
to enable non-time zone-aware operation: new tz=Pacific Standard Time

What is the possible cause?

The MySQLExtractor event does not mark all events properly with the metadata 
tag tz_aware=true.  Some events sneak through because they are returned before 
the bottom of the MySQLExtractor.extractEvent() method. 

What is the proposed solution?

Move the tagging logic out into the calling extract() method, which also marks 
the events as coming from MySQL. 

Additional information

This problem is exacerbated by the fact that time zone migration compatibility 
is turned on by default.  Users should turn off the supportNonTzAwareMode 
option for now to avoid possible errors in time values on slaves as shown in 
the following example: 

 # If true, similate time-zone unaware operation to process events from older
# Tungsten masters that do not extract events in a time zone-aware manner. 
# This option is only required when there is a chance of processing an older
# replicator log. 
replicator.applier.dbms.supportNonTzAwareMode=false 

Use labels and text to provide additional information.

Original issue reported on code.google.com by robert.h...@continuent.com on 17 Feb 2015 at 7:01

[GoogleCodeExporter]

This issue was updated by revision r2795.

Fixed MySQLExtractor to ensure all extracted events are marked as time zone 
aware.  Also turned off time zone compatibility for upgrades by default, since 
this behavior is unnecessary outside of upgrade situations. 

Original comment by robert.h...@continuent.com on 18 Feb 2015 at 7:05

[GoogleCodeExporter]

Tested with build 4.1.0-10.
Test added to the regression suite

Original comment by g.maxia on 18 Feb 2015 at 8:04

• Changed state: Documenting
• Added labels: Test-Done, Tester-Giuseppe

[GoogleCodeExporter]

This issue was updated by revision r2799.

Merged fix to ensure extracted events are time zone aware and to turn off time 
zone compatibility by default for upgrades. 

Original comment by robert.h...@continuent.com on 20 Feb 2015 at 6:34

[GoogleCodeExporter]

This issue was updated by revision r2800.

Merged fix to MySQLExtractor to ensure all extracted events are marked as time 
zone aware and turn off time zone compatibility for upgrades by default, since 
this behavior is unnecessary outside of upgrade situations. 

Original comment by robert.h...@continuent.com on 21 Feb 2015 at 5:22