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: | |
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 |
[10 Oct 2008 1:47]
Dario Souza
[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