pt-heartbeat --update --replace causes duplicate key error

[Mark W]

I am using pt-heartbeat with MySQL that is configured in a HA setup.
 LoadBalancer(VIP)
              |
M1(RW)<--->M2(RW)
|
          +----+----+
S1(R) S2(R)
I am using pt-heartbeat version 1.0.1

I send out a heartbeat every two minutes to the active writer via cron
to the load balancer Virtual IP Address.

*/2 * * * * /usr/bin/pt-heartbeat -D test --update --replace --create-
table -h LBVIP -P LBPORT --run-time 1

When I do a failover I get duplicate key errors on the secondary
master M2. On failback i get duplicate key error on Master M1.

Last_Error: Error 'Duplicate entry '8806' for key 'PRIMARY'' on query.
Default database: 'test'. Query: 'INSERT INTO `test`.`heartbeat`
(server_id, ts) VALUES ('8806', NOW())'

I am using the --update --replace options but i believe based on my
replication errors that it is doing an insert not a replace.

Am i hitting a bug with pt-heartbeat or do i have to change my method
of sending heartbeat?

[Baron Schwartz (baron-xaprb)]

I can't reproduce this with the latest version of pt-heartbeat, 2.1.1. The binary log event is as follows:

REPLACE INTO `test`.`heartbeat` (ts, server_id, file, position, relay_master_log_file, exec_master_log_pos) VALUES ('2012-05-25T13:16:38.001640', '12345', 'mysql-bin.000001', '3254215', NULL, NULL)

I just noticed that you're not using the latest version in the 1.0 series. Can you either upgrade to the latest release (and there will be a new one in a few days) or upgrade to the latest 1.0 release?

[Mark W (deviant-dolphin)]

Last night i had another HA Failover and Fail Back.
Upon Fail Back i got the same type of error.

Default database: 'test'. Query: 'INSERT INTO `test`.`heartbeat`
(server_id, ts) VALUES ('8006', NOW())'

I reviewed my binary log and indeed most of the time pt-heartbeat is sending a replace
but it occasionally does do an insert.

In the past I ran into this very same problem and i created a secondary cron job that deletes a row from the table that matches the current HA primary nodes server-id. In retrospect this would not really help because once replication brakes its to late.

So my heartbeat goes out every 2 minutes

*/2 * * * * nagios /usr/bin/pt-heartbeat --defaults-file /home/nagios/.my.cnf -D test --update --replace --create-table -h LBVIP -P LBPORT --run-time 1

Then i had a cron job deleting the row it inserted every 10 minutes. ( as of today i am no longer running this job)
*/10 * * * * nagios mysql -h LBVIP -P LBPORT test -e "DELETE FROM test.heartbeat WHERE server_id <> LBSERVERID "

I think the problem is that when table becomes empty the --create-table option fires off an insert , which does not respect the --update --replace parameters. I am not sure why this breaks replication because the slaves should also have an empty heartbeat table at this point.

I have attached the relevant portions my binlog from last night.

--

[Baron Schwartz (baron-xaprb)]

Have you tried with the newest version of Percona Toolkit, or are you still looking at the old version?

[Mark W (deviant-dolphin)]

I updated pt-heartbeat to 2.1.1 but i have not failed over to test.
I also stop the cron job that runs every 10 minutes that cleans up the table.

I have no planned maintenance at this time but something always comes up.
I will report back in on my next failover which probably happen sometime in next 30 days.

[Baron Schwartz (baron-xaprb)]

I believe the problem may be caused by this bit of code:

3586 my $row = $dbh->selectall_arrayref($sql);
3587 if ( scalar @$row == 0 ) {
3588 PTDEBUG && _d('No heartbeat row in table');
3589 if ( $o->get('insert-heartbeat-row') ) {
3590 my $sql = "INSERT INTO $db_tbl ($pk_col, ts) "
3591 . "VALUES ('$pk_val', NOW())";
3592 PTDEBUG && _d($sql);
3593 $dbh->do($sql);
3594 }

This doesn't respect the REPLACE options.

[Baron Schwartz (baron-xaprb)]

See also bug 885938.

[Daniel Nichter (daniel-nichter)]

WontFix 1.0, just the fix but not the test in 2.0 (because the 2.1 test suite is different than 2.0), fix and test in 2.1.

[Shahriyar Rzayev (rzayev-sehriyar)]

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