You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Timezone issue with timestamp columns if server doesn't use UTC. #161
Are these all the rows of the table? How many rows does the table have?
Also, to speed up the investigation, would you guess these rows were inserted/added/manipulated throughout the migration process?
It looks like the timestamp was converted from CEST to UTC and then inserted.
Observed from the binlogs:
The row is inserted with NULL values for the timestamp and gets at least one update which doesn't touch the timestamp columns. This is while gh-ost runs.
Then the row gets updated with a timestamp
From the mysqlbinlog output:
### UPDATE `t1`.`foo`
### WHERE
### @1=29793038
…
### SET
### @21=1471507316
### UPDATE `t1`.`_foo_gho`
### WHERE
### @1=29793038
…
### SET
### @21=1471500116
@dveeden thank you for all this input. Also, last question, though I suspect I already know the answer: master and replicas are all in the same timezone, correct?
And last-last question, is master in STATEMENT based replication?
With SET GLOBAL time_zone='+00:00' set I couldn't reproduce the issue.
The 1 and 2 hour offsets could have been the 1 or 2 hour difference between UTC and CEST/CET
Possible solutions:
do a SET SESSION time_zone='+00:00' on the connection which applies events
Convert the timestamp to @@session.time_zone before applying it. Don't use CONVERT_TZ() for that as not having the timezone database loaded is fairly common. So convert it in go.
changed the title [-]Possible timezone issue with timestamp columns[/-][+]Timezone issue with timestamp columns if server doesn't use UTC.[/+]on Aug 18, 2016
@dveeden are you able to lend a hand in testing #265, which takes a different approach on time-zone handling? Can you please test it on your setup?
Insofar it passes all tests on my side (unit + prod) and on another user side.
Activity
dveeden commentedon Aug 17, 2016
Environment: CentOS 6, MySQL 5.6.27
/etc/localtime is a link to /usr/share/zoneinfo/Europe/Amsterdam
@@global.time_zone is set to SYSTEM.
shlomi-noach commentedon Aug 17, 2016
Are these all the rows of the table? How many rows does the table have?
Also, to speed up the investigation, would you guess these rows were inserted/added/manipulated throughout the migration process?
shlomi-noach commentedon Aug 17, 2016
Also, please confirm that on
record 2there are:-- in the same record. Please double check this?
dveeden commentedon Aug 17, 2016
Rows with differences: 7085
Total number of rows (from show table status): 31982317
Yes I do think these were modified while gh-ost was running.
dveeden commentedon Aug 17, 2016
Yes I verified the 2h/1h difference... I have no clue about why that is..
dveeden commentedon Aug 17, 2016
This is with gh-ost from the v1.0.8 tag/release.
shlomi-noach commentedon Aug 17, 2016
was
gh-ostexecuting from the very same replica host, or from another host? If from another host, can you check what the TZ is on said host?dveeden commentedon Aug 17, 2016
The very same replica host. (and TZ=:/etc/localtime )
dveeden commentedon Aug 18, 2016
Tested with v1.0.9 but hit #157 so used a build from today's master. It shows the same issue.
It looks like the timestamp was converted from CEST to UTC and then inserted.
Observed from the binlogs:
From the mysqlbinlog output:
shlomi-noach commentedon Aug 18, 2016
@dveeden thank you for all this input. Also, last question, though I suspect I already know the answer: master and replicas are all in the same timezone, correct?
And last-last question, is master in
STATEMENTbased replication?dveeden commentedon Aug 18, 2016
With
SET GLOBAL time_zone='+00:00'set I couldn't reproduce the issue.The 1 and 2 hour offsets could have been the 1 or 2 hour difference between UTC and CEST/CET
Possible solutions:
SET SESSION time_zone='+00:00'on the connection which applies events[-]Possible timezone issue with timestamp columns[/-][+]Timezone issue with timestamp columns if server doesn't use UTC.[/+]Set session timezone to UTC to prevent data drift for timestamp columns
dveeden commentedon Aug 18, 2016
master and server use the same timezone.
master uses minimal RBR.
2 remaining items
shlomi-noach commentedon Aug 18, 2016
Will add fix once #172 is tested (sent to testing for the duration of the day)
shlomi-noach commentedon Aug 18, 2016
meanwhile @dveeden you can:and proceed from there to assign thetime_zonefrom within the trx and before the query is executed: https://github.com/github/gh-ost/pull/172/files#diff-55e7b291008bc31a7fac46abdad84feaR874dveeden commentedon Aug 19, 2016
@shlomi-noach yes planning to test this today
shlomi-noach commentedon Aug 19, 2016
To clarify, there's now a dedicated branch:
apply-dml-tzthat should solve your issue, you only need to check it out.dveeden commentedon Aug 19, 2016
I did test it and it does solve this issue. 👍
shlomi-noach commentedon Aug 19, 2016
👍 thanks for updating; Will merge come Monday
shlomi-noach commentedon Aug 22, 2016
Fixed by #175
shlomi-noach commentedon Aug 22, 2016
#175 doesn't actually fix this. #182 does, and is not merged yet. Reopening.
shlomi-noach commentedon Aug 24, 2016
1.0.13fixes thisshlomi-noach commentedon Oct 12, 2016
@dveeden are you able to lend a hand in testing #265, which takes a different approach on time-zone handling? Can you please test it on your setup?
Insofar it passes all tests on my side (unit + prod) and on another user side.
shlomi-noach commentedon Oct 12, 2016
Further discussion in #247 (comment)