Skip to content

Timezone issue with timestamp columns if server doesn't use UTC. #161

Closed

Description

@dveeden

After running a gh-ost with -test-on-replica I found differences in two timestamp columns.

I found this by doing a

select 
  * 
from 
  t1 a 
  inner join _t1_gho b on a.id=b.id 
where 
  a.c1<>b.c1 
  or a.c2<>b.c2
  ...

Record 1

   timestamp_notified: 2016-08-17 13:08:04
   timestamp_notified: 2016-08-17 11:08:04

Record 2

   timestamp_notified: 2016-08-17 13:08:19
  timestamp_finalized: 2016-02-01 23:39:30
   timestamp_notified: 2016-08-17 11:08:19
  timestamp_finalized: 2016-02-01 22:39:30

From the definition:

  `timestamp_notified` timestamp NULL DEFAULT NULL,
  `timestamp_exported` timestamp NULL DEFAULT NULL,
  `timestamp_finalized` timestamp NULL DEFAULT NULL,

Activity

  1. dveeden commented on Aug 17, 2016

    @dveeden
    ContributorAuthor

    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.

  2. shlomi-noach commented on Aug 17, 2016

    @shlomi-noach
    Contributor

    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?

  3. shlomi-noach commented on Aug 17, 2016

    @shlomi-noach
    Contributor

    Also, please confirm that on record 2 there are:

    • one TZ difference of 2 hours
    • one TZ difference of 1 hour

    -- in the same record. Please double check this?

  4. dveeden commented on Aug 17, 2016

    @dveeden
    ContributorAuthor

    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.

  5. dveeden commented on Aug 17, 2016

    @dveeden
    ContributorAuthor

    Yes I verified the 2h/1h difference... I have no clue about why that is..

  6. dveeden commented on Aug 17, 2016

    @dveeden
    ContributorAuthor

    This is with gh-ost from the v1.0.8 tag/release.

  7. shlomi-noach commented on Aug 17, 2016

    @shlomi-noach
    Contributor

    was gh-ost executing from the very same replica host, or from another host? If from another host, can you check what the TZ is on said host?

  8. dveeden commented on Aug 17, 2016

    @dveeden
    ContributorAuthor

    The very same replica host. (and TZ=:/etc/localtime )

  9. dveeden commented on Aug 18, 2016

    @dveeden
    ContributorAuthor

    Tested with v1.0.9 but hit #157 so used a build from today's master. It shows the same issue.

    *************************** 1. row ***************************
                    id: 29793038
    Original Timestamp: 2016-08-18 10:01:56
      gh-ost Timestamp: 2016-08-18 08:01:56
    

    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
    
  10. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    Contributor

    @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?

  11. dveeden commented on Aug 18, 2016

    @dveeden
    ContributorAuthor

    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:

    1. do a SET SESSION time_zone='+00:00' on the connection which applies events
    2. 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.
  12. changed the title [-]Possible timezone issue with timestamp columns[/-] [+]Timezone issue with timestamp columns if server doesn't use UTC.[/+] on Aug 18, 2016
  13. dveeden commented on Aug 18, 2016

    @dveeden
    ContributorAuthor

    master and server use the same timezone.
    master uses minimal RBR.

  14. 2 remaining items

  15. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    Contributor

    Will add fix once #172 is tested (sent to testing for the duration of the day)

  16. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    Contributor

    meanwhile @dveeden you can:

    ..obsolete..
    

    and proceed from there to assign the time_zone from within the trx and before the query is executed: https://github.com/github/gh-ost/pull/172/files#diff-55e7b291008bc31a7fac46abdad84feaR874

  17. dveeden commented on Aug 19, 2016

    @dveeden
    ContributorAuthor

    @shlomi-noach yes planning to test this today

  18. shlomi-noach commented on Aug 19, 2016

    @shlomi-noach
    Contributor

    To clarify, there's now a dedicated branch: apply-dml-tz that should solve your issue, you only need to check it out.

  19. dveeden commented on Aug 19, 2016

    @dveeden
    ContributorAuthor

    I did test it and it does solve this issue. 👍

  20. shlomi-noach commented on Aug 19, 2016

    @shlomi-noach
    Contributor

    👍 thanks for updating; Will merge come Monday

  21. shlomi-noach commented on Aug 22, 2016

    @shlomi-noach
    Contributor

    Fixed by #175

  22. shlomi-noach commented on Aug 22, 2016

    @shlomi-noach
    Contributor

    #175 doesn't actually fix this. #182 does, and is not merged yet. Reopening.

  23. shlomi-noach commented on Aug 24, 2016

    @shlomi-noach
    Contributor

    1.0.13 fixes this

  24. shlomi-noach commented on Oct 12, 2016

    @shlomi-noach
    Contributor

    @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.

  25. shlomi-noach commented on Oct 12, 2016

    @shlomi-noach
    Contributor

    Further discussion in #247 (comment)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions