pt-heartbeat handles timezones inconsistently

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.

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

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.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-432