import / load sql db error 1062

Hi all,

Apologies for 1st post being a request for help!

I am trying to import an sql file onto a new server and have got well and truly stuck! I get "/* SQL Error (1062): Duplicate entry '%-sproduction-sproduction' for key 'PRIMARY' */"

I will run through all steps taken to get to this point;

Connect to old server using Heidi session manager, click export then export db as SQL, options for create db and table are ticked, drop db and table are not. Data set to insert, output is one big file. I export this successfully.

Connect to new server using Heidi session manager, right click and 'drop' all the databases currently on the new server apart from information_schema as it wont let me. Next I click import, then load SQL file. I select yes on the warning - to run the file without loading it into editor, if I click no it runs out of memory. After 5mb I then get the error

/* SQL Error (1062): Duplicate entry '%-sproduction-sproduction' for key 'PRIMARY' */

Is there anyone that can help me out? If you need more info please ask. I am running win2008 r2 on both servers and have full admin access to everything.

Many thanks in advance
Luke

I'm not sure if you understand what a primary key is. It's the whole essence of relational databases: primary keys that identify rows. That implies that you can't repeat primary keys becase, otherwise, they would not identify rows.

My educated guess, given that you didn't post neither the offending row nor the existing duplicate, if that such primery key contains a column where some of the values need certain server setting to exist. For instance, you can only store 2012-02-30 in a DATE column if MySQL is configured to allow invalid dates. MySQL finds and invalid piece of data and silently converts it to something else.

Enable logging (Tools-> Preferences-> Logging), try again and identify the offending row.

I have over come this problem, don't know the cause but the answer was to keep same settings but to select each database individually rather than selecting the whole lot in one go. It took a while (200+ databases) then I imported them one at a time on the destination server. Doing it this way selected the same data as before but for some reason it worked.

Thanks for you reply anyway, and i do understand what a primary key is, what i couldnt understand is how i was getting a duplicate of a primary key when copying existing data from one working db to a blank server.

Regards
Luke