Bug #60847 slave replication encounters error 1062 after restoring from master snapshot
Submitted: 13 Apr 2011 6:01 Modified: 28 Jul 2011 12:03
Reporter: Greg Hazel Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.5.10 OS:Any
Assigned to:

[13 Apr 2011 6:01] Greg Hazel
Description:
Setting up a slave to one master, I took a snapshot of the master with:

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > foo.sql

I restored the snapshot on the new empty slave with:

mysql < foo.sql

Then I setup the slave using the "CHANGE MASTER TO" comment from the snapshot:

CHANGE MASTER TO MASTER_HOST='myhost', MASTER_USER='repl', MASTER_PASSWORD='mypass', MASTER_LOG_FILE='mysql-bin.000353', MASTER_LOG_POS=107;

Then I started the slave:

START SLAVE;

Immediately, I got:

Last_Error: Error 'Duplicate entry '76016617' for key 'PRIMARY'' on query. ...

Inspecting the duplicate key rows, it looks like the slave has the exact correct data for that row already, indicating that the slave data is actually farther in the future than the point it is replaying from. Based on the application-level timestamps on the rows, it looks like this difference is about 29 minutes.

How to repeat:
Repeat presumably by repeating my exact steps. Relevant configuration of the two machines:

master:
log-bin = mysql-bin
server-id = 1

slave:
log-bin = mysql-bin
server-id = 2
read_only = 1
[13 Apr 2011 7:41] Greg Hazel
Perhaps this bug should simply be filed against mysqldump and the options I'm using. I confirmed that the binlog position written in the dump is about 29 minutes behind the data in the dump itself.
[28 Apr 2011 17:04] Sveta Smirnova
Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_master-data:

----<q>----
The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump. 
----</q>----

If you use MyISAM table they can be updated while you are taking dump with single-transaction option. Do you use MyISAM tables?
[28 Apr 2011 19:51] Greg Hazel
No, these are InnoDB tables.
[24 May 2011 9:29] Saverio M
I get this error as well.

The replication error has the format:

Error 'Duplicate entry '170837028' for key 'PRIMARY'' on query. Default database: '<db>'. Query: 'INSERT INTO <table> (<fields>) VALUES(<values>)'

Problem is that none of the <fields> is the primary key, which is an INT AUTO_INCREMENT, so this query itself is not incorrect, and the data in the table either, as this would not fail if launched manually.
The relay binlog contains exactly the same query (INSERT...), without including any reference to the primary key.

The dump strategy I use is the same (mysqldump --master-data=2 followed by manual change of the master).
We have MyISAM tables, although the table causing the error is an InnoDB one.
I double checked the consistency of the database (using maatkit), and it's correct.

The mysql version I'm using is 5.1.50.

Is there anything else I can check?
[21 Jul 2011 13:41] Gerbec David
Hi all!

I have the same issue on my replication slave... 

What you could do, is try to rsync the files from one server to the other - though, for that, the MySQL servers need to be down...

Will report if it succeeded or not, but it wont be before Sunday night, since it's a production server and that's when maintenance time is scheduled.

Regards,
D.
[21 Jul 2011 14:28] Gerbec David
After more reading and more testing i managed to fix my slave server with the following:

on slave:
slave stop;
reset slave;
drop <database>;
create <database>;

On master i created the dump with the following command:
mysqldump -u root -p <database> --master-data=1 > database.sql

Then i transferred the database.sql to the slave server and imported the data.

Once this was finished, i just had to do the 'change master to' and start slave... Replication is now working.

Regards,
D.
[27 Jul 2011 22:44] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior. Please try current version 5.5.14 and if problem still exists inform us if there was write activity on master while performing dump.
[28 Jul 2011 12:04] Sveta Smirnova
No update needed.

This is actually duplicate of bug #61854. Avoid using single-transaction with flush-logs for now
[22 Nov 2011 18:19] Adam Whelan
I am having the same issue on 5.5.16 x64. With the following backup command I can't
produce a consistent backup. I had no problem before when I was on 5.0. I noticed the
problem when I used the dump to create a new slave server. When I started up my new slave
I had numerous duplication alerts. After reviewing the data from the restore I could see
that it did not create a consistent backup.
   Has there been any updates or progress? I need to create a new slave without stopping
or completely locking my tables. Is there any way to currently do this?

mysqldump -uxxx -pxxx -AFfq --single-transaction --master-data=2 --routines --triggers |
/bin/gzip > backup.sql.gz