pt-heartbeat does not appear to support timezones/problem with daylight saving time switch

[Sheeri K. Cabral]

As filed here:

https://bugzilla.mozilla.org/show_bug.cgi?id=808934

pt-heartbeat's table schema does not appear to support timezones, and the times written to the browserid.heartbeat database are in PST8PDT rather than UTC. During the "fall back" hour the Nagios mysql_repl check reports false alarms of 3600.0 seconds of lag, which resolves once the clock ticks over to 2am a second time.

01:03 < nagios-svc-scl2> Sun 01:03:10 PST [140] db3.iddb.scl2.svc:mysql_repl is CRITICAL: CRITICAL: master=db1.iddb.phx1.svc, replication lag is 3600.00 seconds ( 60)
02:03 < nagios-svc-scl2> Sun 02:03:09 PST [155] db3.iddb.scl2.svc:mysql_repl is OK: OK: master=db1.iddb.phx1.svc replication lag is 0.00 seconds

This resulted in a one hour gap in production replication lag monitoring, during which one manual comparison of slaves' log position vs. master log position was performed.

Note that the timestamp stored in the database between 01:00:00 PST (-0800) and 01:59:59 PST (-0800) is accurate in PST (-0800); that is, it reads "01:xx" rather than "02:xx" or some other value. I do not have data for 01:00:00 PDT (-0700) to 01:59:59 PDT (-0700).

[Brian Fraser (fraserbn)]

Hi Sheeri,

Seems like this is a duplicate of https://bugs.launchpad.net/percona-toolkit/+bug/886059 -- do any of the workarounds in there solve the issue for you?

If they do, what do you think the tool should do here? I can think of arguments for and against respecting the timezone, and if this can be sidestepped by using --set-vars, I'm leaning towards leaving the current behavior but adding a warning in the docs -- But I'm a developer, so an user's opinion would be quite welcome.

[Sheeri K. Cabral (awfief)]

I saw that bug earlier, and then realized that we'd have to use
--set-vars to set the timezone to PDT or PST depending on what it
really was.

Or are you suggesting something else, perhaps that --set-vars should
be used to set the timezone to a non-daylight-saving-time zone? such
as UTC?

-Sheeri

On Tue, Nov 13, 2012 at 4:00 PM, Brian Fraser
<email address hidden> wrote:
> Hi Sheeri,
>
> Seems like this is a duplicate of https://bugs.launchpad.net/percona-
> toolkit/+bug/886059 -- do any of the workarounds in there solve the
> issue for you?
>
> If they do, what do you think the tool should do here? I can think of
> arguments for and against respecting the timezone, and if this can be
> sidestepped by using --set-vars, I'm leaning towards leaving the current
> behavior but adding a warning in the docs -- But I'm a developer, so an
> user's opinion would be quite welcome.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1078416
>
> Title:
> pt-heartbeat does not appear to support timezones/problem with
> daylight saving time switch
>
> Status in Percona Toolkit:
> New
>
> Bug description:
> As filed here:
>
> https://bugzilla.mozilla.org/show_bug.cgi?id=808934
>
> pt-heartbeat's table schema does not appear to support timezones, and
> the times written to the browserid.heartbeat database are in PST8PDT
> rather than UTC. During the "fall back" hour the Nagios mysql_repl
> check reports false alarms of 3600.0 seconds of lag, which resolves
> once the clock ticks over to 2am a second time.
>
> 01:03 < nagios-svc-scl2> Sun 01:03:10 PST [140] db3.iddb.scl2.svc:mysql_repl is CRITICAL: CRITICAL: master=db1.iddb.phx1.svc, replication lag is 3600.00 seconds ( 60)
> 02:03 < nagios-svc-scl2> Sun 02:03:09 PST [155] db3.iddb.scl2.svc:mysql_repl is OK: OK: master=db1.iddb.phx1.svc replication lag is 0.00 seconds
>
> This resulted in a one hour gap in production replication lag
> monitoring, during which one manual comparison of slaves' log position
> vs. master log position was performed.
>
> Note that the timestamp stored in the database between 01:00:00 PST
> (-0800) and 01:59:59 PST (-0800) is accurate in PST (-0800); that is,
> it reads "01:xx" rather than "02:xx" or some other value. I do not
> have data for 01:00:00 PDT (-0700) to 01:59:59 PDT (-0700).
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-toolkit/+bug/1078416/+subscriptions

--
- Sheeri K. Cabral

http://tinyurl.com/mysqlbook will take you to the Amazon.com page for
my book, "MySQL Administrator's Bible".

[Brian Fraser (fraserbn)]

If both of the heartbeat processes have the same timezone assigned through --set-vars, I don't think it would matter? Or am I missing something?

[Sheeri K. Cabral (awfief)]

The problem is when the time zone changes, say from PST to PDT. How would one script that to happen automatically?