MySQL Bugs: #60847: slave replication encounters error 1062 after restoring from master snapshot

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:		CPU Architecture:	Any

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

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.

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?

No, these are InnoDB tables.

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?

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.

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.

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.

No update needed.

This is actually duplicate of bug #61854. Avoid using single-transaction with flush-logs for now

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