Bug #39967 Binlog should save timezone information when calculating dates from timestamp
Submitted: 10 Oct 2008 1:47 Modified: 12 Nov 2008 13:26
Reporter: Dario Souza Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.67, 5.1 OS:Linux (ubuntu 8.04 lts x86_64)
Assigned to: Assigned Account CPU Architecture:Any
Tags: timezone utc statement-based replication sql timestamp
Triage: Triaged: D2 (Serious)

[10 Oct 2008 1:47] Dario Souza
Description:
When replicating from machines with different timezones, the slave sql thread should try to replicate identical values from the master. In that sense, when issuing SET TIMESTAMP=<value>, an update that requires timezone information yields different values and hence creates problems in replication.

How to repeat:
Setup replication between A and B.
Use timezone GMT in A and GMT-1 in B.

In A:

mysql> CREATE TABLE a (somehour INT, UNIQUE unique_hour (somehour)) ENGINE=InnoDB;
mysql> SET TIMESTAMP=1223589601;
mysql> INSERT INTO a VALUES (HOUR(CURRENT_TIMESTAMP));
mysql> SELECT * FROM a;
+----------+
| somehour |
+----------+
|       22 |
+----------+
1 row in set (0.02 sec)

-------------------------------
In B:
mysql> SELECT * FROM a;
+----------+
| somehour |
+----------+
|       21 |
+----------+
1 row in set (0.02 sec)

Suggested fix:
Save timezone information along with timestamp in binlog. If it is there already (I don't know details of the bin log format), try to make sql thread use it along with the timestamp when updating the slave machine.

I don't know if it's the best behaviour, but it does create problems and IMHO, defeats the purpose of replication (different values between replicated machines).
[10 Oct 2008 19:45] Dario Souza
Sorry, issues has to do with plain old mysql statement-based replication and not row-based.
[12 Nov 2008 13:26] Susanne Ebrecht
Many thanks for pointing this out.

When I understood you right then I would disagree with your opinion but that doesn't matter because you hit a bug here and we have to discuss this topic anyway and how the final solution should be.

I made the following tests:

1) took two machines (OS should be irrelevant, I took Ubuntu and FreeBSD)
2) set system of slave machine to GMT
3) set system of master machine of CET
4) synced system time with an atomic time clock (just to make really sure both machines have same time only different timezones)

I started my tests at 12:11 CET which means slave machine has 11:11 GMT.

my.ini of master:
...
binlog-format=row
...

I started slave and master 

and made the following test:

on master:
create table foo(repmode text, h integer);
insert into foo values('row', hour(current_timestamp));

stopped master and changed my.cnf to binlog-format=mixed
start master again ...

insert into foo values('mixed', hour(current_timestamp));

stopped master and changed my.cnf to binlog-format=statement
start master again ...

insert into foo values('statement', hour(current_timestamp));

select * from foo; 
+-----------+------+
| repmod    | h    |
+-----------+------+
| row       |   12 | 
| mixed     |   12 | 
| statement |   12 | 
+-----------+------+

on slave:

select * from foo;
+-----------+------+
| repmod    | h    |
+-----------+------+
| row       |   12 | 
| mixed     |   11 | 
| statement |   11 | 
+-----------+------+

This means either row based replication behaves wrong or mixed/statement based replication.

I would vote for row based replication because for me it is familar to get result related to machine time zone and not to master time zone. But I also think argument of reporter too have same values on both machines is acceptable.

We just should be consistent here and document which behaviour we have implemented.

Get different behaviours by using different modes is a bug.
[12 Nov 2008 13:28] Susanne Ebrecht
I forgot to say: I tested with actual MySQL 5.1 bzr tree.
[30 Mar 2010 9:25] Sven Sandberg
When a Query_log_event is logged, it checks if THD::time_zone_used is set: if it is set, the event contains the timezone; otherwise not. I think the bug is that Item_func_hour::val_int() (and a few other Item_func_*::val_int() methods) does not set time_zone_used.
[19 Dec 2012 16:29] Hartmut Holzgraefe
Works fine (at least on current 5.5) when explicitly setting --default-time-zone or @time_zone on the master, "wrong" behavior is reproducible when using the default time_zone=SYSTEM though as SYSTEM is ambiguous ...

So !bug IMHO