MySQL Forums
Forum List  »  Data Recovery

Recovery causing ERROR 1062 (23000) at line 257653 in file: 'rec_DB1_016179.sql': Duplicate entry '20240' for key 'PRIMARY'
Posted by: Lee Bennett
Date: August 21, 2009 02:16AM

Hi

I am restoring serveral databases from a backup taken with mysqldump and rolling them forward by using the mysqlbinlog program to generate a list of statement for each database so that I can restore them all to the same point in time

So for the fisrt log I do:

mysqlbinlog --database=<database> --start-position=<start_position> --disable-log-bin <binary log file>

Next log files until one before last:

mysqlbinlog --database=<database> --disable-log-bin <binary log file>

Last log file:

mysqlbinlog --database=<database> --stop-position=<end_position> --disable-log-bin <binary log file>

I then redirect the output to a file with the db name and log sequnce number in it. To my mind should generate a set on statements for each database that I can use to restore the databases to the required end position. This is scripted process.

This all seems to work ok most of the time however when I run the restore sql scripts that are generated above say for the database DB1 I very often get the below error:

ERROR 1062 (23000) at line 257653 in file: 'rec_DB1_016179.sql': Duplicate entry '20240' for key 'PRIMARY'

Upom looking in the file 'rec_DB1_016179.sql' at the line '257653' the below entries appear:

#090821 4:32:52 server id 1 end_log_pos 14414683 Table_map: `codecollab`.`appstate` mapped to number 376166
#090821 4:32:52 server id 1 end_log_pos 14414759 Write_rows: table id 376166 flags: STMT_END_F

BINLOG '
ZBWOShMBAAAAPwAAAFvz2wAAAGa9BQAAAAAACmNvZGVjb2xsYWIACGFwcHN0YXRlAAoDAwMDAwMD
AwMMAAAA
ZBWOShcBAAAATAAAAKfz2wAQAGa9BQAAAAEACv//APwQTwAAkBMAADoCAABPAAAADAsAAKwAAAAA
AAAAEgAAADMAAAA6PkDCRRIAAA==
'/*!*/;
# at 14414759
#090821 4:32:52 server id 1 end_log_pos 14414786 Xid = 150552452
COMMIT/*!*/;

This is of course a straight forward primary key violation on the mentioned table.
I am somewhat confused about this at the mentioned table `codecollab`.`appstate` is not part of the database DB1 that I am restoring so why does it appear in my restore script when I specified '--database' with 'mysqlbinlog' and the database is not 'codecollab'. Also what are the entries after the BINLOG statement? This happens very often during the restore.

Any ideas?

Thanks
Lee

Options: ReplyQuote


Subject
Views
Written By
Posted
Recovery causing ERROR 1062 (23000) at line 257653 in file: 'rec_DB1_016179.sql': Duplicate entry '20240' for key 'PRIMARY'
8770
August 21, 2009 02:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.