Hi Chaps,
I have a CMD prompt script that will dump only the data from my database (dbjobs):
mysqldump -u 'username' -p'password' --no-create-info --complete-insert dbjobs > c:/database_data_only.sql
But when I try to restore the data, I get multiple errors:
e.g.
ERROR 1062 (23000): Duplicate entry '1' for key 1
Is there something that I can add to the mysqldump command that will solve this issue?
are you restoring it to the same table you dumped it from?
Yes, but I’ve included ‘–complete-insert’ to accomodate the possibility of restoring the data to the same table, with extra columns, at a later date
if you’re gettin a “duplicate key” error when you try to load the dumed data back into the same table you dumped it from, then there are only two possibilities that i can think of –
you defined the key after you dumped the data
you’re loading the same data into the table without clearing out the table first
- you’re loading the same data into the table without clearing out the table first
Sounds right to me, how do I clear the data first, then restore the data in the sql file?
use TRUNCATE TABLE
OK, that sounds sweet . . .is there a way I can add this to my CMD prompt, so each table is TRUNCATEd automatically?
sorry, i don’t do command prompts, i gave up DOS when Windows came out
OK far enough, the PHP comand and CMD prompt are the same:
mysqldump -u ‘username’ -p’password’ --no-create-info --complete-insert dbjobs > c:/database_data_only.sql
Can TRUNCATE / or equivalent be added to the mysqldump options?
what did you find when you looked for this in da manual?
Couldn’t find anything that would suggest that TRUNCATE could be used, hence ‘equivalent’. . . I’ve looked at: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html, but couldn;t see anything that could do the same. . .unless I’ve missed it?
hint: the answer was no
so is there no way of dumping the data out of a table, clearing it, then importing what was dumped?
yes, there is a way – three separate steps
OK,
three separate steps
…Got it!
Cheers