phpMyAdmin export/import results in duplicate primary key error

7 Views

I want to globally replace all instances of my site url in the WordPress MySQL database with a new site url. To do that, I’m using phpMyAdmin to export the database to a .sql file, then doing a global replace in a text editor, then using phpMyAdmin to import the .sql file.

During the import, I’m encountering a duplicate entry for primary key error. Trying to debug this, I exported the file, then imported the identical file, making no changes and I still get the same error.

Read More

I appreciate any help solving this. 

--
-- Dumping data for table `wp_comments`
--
INSERT INTO  `wp_comments` 
  (`comment_ID`, `comment_post_ID`, `comment_author`, `comment_author_email`
  ,`comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt` 
  ,`comment_content`, `comment_karma`, `comment_approved`, `comment_agent` 
  ,`comment_type`, `comment_parent`, `user_id`) 
VALUES (1, 1, 'Mr WordPress', ''
       ,'http://wordpress.org/', '', '2011-04-28 00:49:55', '2011-04-28 00:49:55'
       ,'Hi, this is a comment.<br />To delete a comment, 
         just log in and view the post's comments. 
         There you will have the option to edit or delete them.'
       , 0, 'post-trashed',  '',  '', 0, 0 ) ;

MySQL said: 

#1062 - Duplicate entry '1' for key 'PRIMARY'
Post Views: 7

Related posts

Leave a Reply

8 comments

  1. The original data is still in the database.

    If you were working manually you’d send a UPDATE rather than INSERT query with the new values, but since you’re doing a batch import, it may just be better to wipe the existing table clean before the import.

    DELETE FROM `tblName`;

    Be sure to back-up your data, first.

  2. To avoid duplicates you have to use UPDATE instead of INSERT statements. To achieve this in phpMyAdmin, follow these steps:

    1. Select your database in the tree.
    2. OPTIONAL. Go to “Search” tab and search for string you want to replace in all tables. If string is found in several tables only, note their names. This will help to speed up process by updating only the tables which needs updating. This my be important if you have lot of data.
    3. Go to “Export” tab.
    4. In the “Export method:” select “Custom”.
    5. OPTIONAL. If you noted the tables which need updating in step 2 above, then in the “Table(s):” section, click “Unselect all” and then select only the tables which need to be updated.
    6. Scroll down to “Data creation options” section, and in the drop
      box labeled “Function to use when dumping data:” select “UPDATE”
      (default is “INSERT”).
    7. Click “Go”.
    8. Open the downloaded SQL dump file.
    9. IMPORTANT! Save the file with a new name for backup purposes before any changes are made.
    10. Use Search & replace function of your editor to change what you want. Then save the file.
    11. In phpMyAdmin go to “Import” tab.
    12. In the “File to import:” section click the “Choose file” button and
      browse for the edited file. Click GO

    You are ready! To check if everything is OK, search the database second time (repeat step 2). You should not find any tables containing your old string.

  3. If you’re exporting, that means that the main content stays in the database. So, when you try to insert a new row with the same PRIMARY KEY, which are always UNIQUE, you’ll get an error.

    Solution: You must delete the row from the table that has the same comment_ID.
    You must open the PHPMyAdmin and go your table page, and check the row with the ID you want. In this case is 1, which means that it is probabily in the first results page of your table listing. Just click on the red X which appears in the row.

    This could also be done by SQL, which could be simple too.

    DELETE FROM `wp_comments` WHERE `comment_ID` = 1 LIMIT 1

    For this, just select your database in PHPMyAdmin and go to the SQL tab, and insert the code above in the text area.

  5. The problem is related with your file – you are trying to create a DB using a copy – at the top of your file you will find something like this:

    CREATE DATABASE IF NOT EXISTS *THE_NAME_OF_YOUR_DB* DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
    USE *THE_NAME_OF_YOUR_DB*;

    and I’m sure that you already have a DB with this name – IN THE SAME SERVER – please check, because you are trying to overwrite!! Just change the name OR (better) ERASE THIS LINE!

  6. Since you already have that record there, you can just update the record rather than inserting. It would go something like this. 

      UPDATE `wp_comments` 
  SET 'comment_author_url' = 'YOUR NEW ADDRESS'
  WHERE  `comment_ID` = 1

    Just update every instance of your old address using this method. You can search through all of all posts by saying “WHERE ‘comment_author_url’ = ‘YOUR OLD ADDRESS'”

  7. If all you want to do is replace your URL, I believe this is all you must do:

    Update `wp_comments` Set 
`comment_author_url` = 'http://wordpress.org/'
Where `comment_author` = 'Mr WordPress'

    Just type the above SQL into PHPMyAdmin’s SQL box and execute.

    NOTE: First make sure you have a backup. And there’s no need to do all that export and import stuff 🙂

  8. Change to code to 

    INSERT .... (what you already have) 
ON DUPLICATE KEY UPDATE;

    This will fix your problem with a minimum of fuss, whilst still inserting new rows.