pt-heartbeat handles timezones inconsistently

Reported by Kristian Davies on 2011-11-04
14
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Brian Fraser

Bug Description

I have a master in GMT and a slave in IST. CentOS 5, perl 5.8.8, pt-heartbeat v1.0.1.

When I set pt-heartbeat to monitor in other timezones it translates the time so I get 0.00s but it seems to ignore IST so I get 19800.00s.

Please include more information so we can understand and reproduce the
problem. What do you mean "set pt-heartbeat to monitor in other
timezones" ? What command-line arguments are you using? What is the
machine's local time? What is the output of this query?

SELECT NOW(), * FROM <timestamp table>

I have a master in GMT(BST) and a slave in IST.

I have slaves connected to this master in the US (east and west coast) and they work fine.

master:
/usr/bin/pt-heartbeat --daemonize --table heartbeat --database replicated --update

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | BST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select *,now() from heartbeat\G;;
*************************** 1. row ***************************
                   ts: 2011-11-04T12:28:22.001590
            server_id: 1
                 file: binary-log.001158
             position: 351702414
relay_master_log_file: NULL
  exec_master_log_pos: NULL
                now(): 2011-11-04 12:28:22
1 row in set (0.00 sec)

slave:
pt-heartbeat -uheartbeat -pheartbeat -D replicated --table heartbeat

mysql> select *,now() from heartbeat\G;
*************************** 1. row ***************************
                   ts: 2011-11-04T12:26:08.003740
            server_id: 1
                 file: binary-log.001158
             position: 348298042
relay_master_log_file: NULL
  exec_master_log_pos: NULL
                now(): 2011-11-04 17:56:08
1 row in set (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | IST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

This is what runs on the salve:

pt-heartbeat -uheartbeat -pheartbeat -D replicated --table heartbeat --monitor

It appears... That my pt-heartbeat running in EST and PST were in fact not working at all (i.e. regardless whether slave was working it reported 0secs).... but I managed to get them to work with:

EST pt-heartbeat -D <database> -u<user> -p<pass>--monitor -h 127.0.0.1 --skew -18000
PST pt-heartbeat -D <database> -u<user> -p<pass>--monitor -h 127.0.0.1 --skew -28800

By that rationale in IST I could add "--skew 19800" and wait 5.5hrs for output and it would be at 0secs

Cheers,
Kristian
p.s. Really enjoyed the Percona Live talks in London.

Baron Schwartz (baron-xaprb) wrote :

I believe that the correct fix for you to is use the --set-vars option to set the correct timezone for the connection.

tags: added: pt-heartbeat
tags: added: tz
Changed in percona-toolkit:
status: New → Confirmed
Brian Fraser (fraserbn) on 2012-12-13
Changed in percona-toolkit:
assignee: nobody → Brian Fraser (fraserbn)
status: Confirmed → Triaged
summary: - pt-heartbeat timezone ignores IST
+ pt-heartbeat inconsistently handles timezones
Changed in percona-toolkit:
milestone: none → 2.2.1
Brian Fraser (fraserbn) on 2012-12-13
summary: - pt-heartbeat inconsistently handles timezones
+ pt-heartbeat handles timezones inconsistently
Changed in percona-toolkit:
status: Triaged → In Progress
Daniel Nichter (daniel-nichter) wrote :

The fix for this is using UTC_TIMESTAMP() so the tool uses UTC for everything. Timezones really don't matter for this tool and it should work regardless of them, so UTC is the natural choice for a common TZ to figure out lag.

Changed in percona-toolkit:
milestone: 2.2.1 → 2.1.8
importance: Undecided → Medium
Brian Fraser (fraserbn) on 2012-12-14
Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers