Everytime I try restoring the dump, I get :
ERROR 1062 (23000) at line 10297: Duplicate entry 'spaß' for key 'PRIMARY'
I am trying to restore it using:
mysql -u root -ppassword database < 0719.sql
views:
22answers:
2+1: That or check the dump to see why it has duplicate values...
OMG Ponies
2010-07-19 20:33:17
surely there is a way to make MYSQL do the work for you, that is to ignore duplicates. The database dump is like a gazillion lines. Not to mention the character it is finding appears to be weird itself.
jini
2010-07-19 20:36:19
mysql _must not_ do that because it would compromise data integrity. it's impossible to know which of the duplicates is right. it is db administrator's work.
kgb
2010-07-19 20:39:46
@jini: most things you can do with a dump (using INSERT IGNORE instead of INSERT for instance) can be done when _making_ the dump, not when importing it. Don't blame the tool for an incorrect / ill-suited backup.
Wrikken
2010-07-19 20:40:24
if you can not redump the db your only way might be to manually edit the dump and make it INSERT IGNORE or REPLACE instead of just INSERT
kgb
2010-07-19 20:44:03
I did a dump using: mysqldump --single-transaction -u root -pxxxxxx-h database > /tmp/0719.sql. You mean to say I can modify that to clean my dump?
jini
2010-07-19 20:49:46
the fact that you get duplicates might mean that you are trying to merge data form two separately changed databases... which is not a good idea;) but if you don't care about old data, you might open the dump in any text editor and change INSERT to REPLACE(i wouldn't call it fixing, but it will do what you want)... and if you really don't care about the old data, you should empty the table first! otherwise, if you want to keep the data safe, you will have to examine every duplicate _individually_
kgb
2010-07-19 21:06:35
A:
You could import the dump into a temporary table, than use an SQL statement to copy only rows from this temporary table which do are not found in the target table.
R. Hill
2010-07-19 20:33:52