pt-heartbeat causing duplicate key errors

[Eric H]

I have gotten duplicate key errors on 2 different stacks of mysql within the last week.

Could not execute Write_rows event on table repl_heartbeat.heartbeat; Duplicate entry '6206' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000054, end_log_pos 201330032

I am running MySQL 5.6.34 in Master <-> Master configuration
and using pt-heartbeat 2.2.20

The duplicate key errors break on the specified pt-heartbeat target table and I have resolved the issue by truncating the target table (repl_heartbeat.heartbeat) on both sides and then starting the sql_thread.

I have been running pt-heartbeat for about 5 years now have had very few issues if any but now I had have 2 issues in the last week. I did recently upgrade percona-toolkit on my server.

sudo grep -i percona /var/log/dpkg.log
2017-01-23 09:56:06 upgrade percona-toolkit:all 2.2.20-1 2.2.20-1
2017-01-23 09:56:06 status half-configured percona-toolkit:all 2.2.20-1
2017-01-23 09:56:06 status unpacked percona-toolkit:all 2.2.20-1
2017-01-23 09:56:06 status half-installed percona-toolkit:all 2.2.20-1
2017-01-23 09:56:06 status half-installed percona-toolkit:all 2.2.20-1
2017-01-23 09:56:07 status half-installed percona-toolkit:all 2.2.20-1
2017-01-23 09:56:07 status unpacked percona-toolkit:all 2.2.20-1
2017-01-23 09:56:07 status unpacked percona-toolkit:all 2.2.20-1
2017-01-23 09:56:07 configure percona-toolkit:all 2.2.20-1 2.2.20-1
2017-01-23 09:56:07 status unpacked percona-toolkit:all 2.2.20-1
2017-01-23 09:56:07 status half-configured percona-toolkit:all 2.2.20-1
2017-01-23 09:56:07 status triggers-awaited percona-toolkit:all 2.2.20-1
2017-01-23 09:56:07 status installed percona-toolkit:all 2.2.20-1

Attached is a dump of pertinent binary logs , show slave status output and global variables.

Would you please take a look and see if you can identify the problem.

Thanks,
EricH

[Eric H (eric-hardway)]

I just wanted to drop a note that I have not had an issue again since late January 2017.
It did happen twice in January on two separate stacks but never again.

I send 12 pt-heartbeats per minute 24/7 on 12 different stacks.

I am still using pt-heartbeat 2.2.20

[Carlos Salguero (carlos-salguero)]

Thanks for the info

[Eric H (eric-hardway)]

I found another possible contributing factor to the problem.

I have been sending two pt-heartbeats per minute to each master in each
mysql stack.

cron definition
* * * * * /usr/bin/pt-heartbeat --defaults-file /home/nagios/.my.cnf -D
repl_heartbeat --update --replace --create-table -h writer.acme.com -P 3306
 --run-time 1 --no-version-check

I just corrected the issue. So now I only send12 pt-heartbeats per minute
to 12 different stacks instead of 24 pt-heartbeats per minute to 12
different stacks.

Regards,

On Mon, Feb 27, 2017 at 12:20 PM, Carlos Salguero <
<email address hidden>> wrote:

> Thanks for the info
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1659047
>
> Title:
> pt-heartbeat causing duplicate key errors
>
> Status in Percona Toolkit:
> New
>
> Bug description:
> I have gotten duplicate key errors on 2 different stacks of mysql
> within the last week.
>
> Could not execute Write_rows event on table repl_heartbeat.heartbeat;
> Duplicate entry '6206' for key 'PRIMARY', Error_code: 1062; handler
> error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000054,
> end_log_pos 201330032
>
> I am running MySQL 5.6.34 in Master <-> Master configuration
> and using pt-heartbeat 2.2.20
>
> The duplicate key errors break on the specified pt-heartbeat target
> table and I have resolved the issue by truncating the target table
> (repl_heartbeat.heartbeat) on both sides and then starting the
> sql_thread.
>
> I have been running pt-heartbeat for about 5 years now have had very
> few issues if any but now I had have 2 issues in the last week. I did
> recently upgrade percona-toolkit on my server.
>
> sudo grep -i percona /var/log/dpkg.log
> 2017-01-23 09:56:06 upgrade percona-toolkit:all 2.2.20-1 2.2.20-1
> 2017-01-23 09:56:06 status half-configured percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:06 status unpacked percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:06 status half-installed percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:06 status half-installed percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:07 status half-installed percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:07 status unpacked percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:07 status unpacked percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:07 configure percona-toolkit:all 2.2.20-1 2.2.20-1
> 2017-01-23 09:56:07 status unpacked percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:07 status half-configured percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:07 status triggers-awaited percona-toolkit:all 2.2.20-1
> 2017-01-23 09:56:07 status installed percona-toolkit:all 2.2.20-1
>
> Attached is a dump of pertinent binary logs , show slave status output
> and global variables.
>
> Would you please take a look and see if you can identify the problem.
>
> Thanks,
> EricH
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-toolkit/+bug/1659047/+subscriptions
>

--
Eric H.
<email address hidden>

[Shahriyar Rzayev (rzayev-sehriyar)]

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