Error: #1062 - Duplicate entry '1' for key 'PRIMARY'

Question

I've been given a database file to upload to my server but am getting the following error message when importing. I've searched for other similar problems but can't work out how to fix this.

SQL query:

--
-- 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/',
     '', 
     '0000-00-00 00:00:00',
     '0000-00-00 00:00:00',
     'Hi, this is a comment.<br />To delete a comment, just log in and view the post&#039;s comments. There you will have the option to edit or delete them.',
     0,
     '1', 
     '', 
     '', 
     0,
     0 
    );

MySQL said:

#1062 - Duplicate entry '1' for key 'PRIMARY'

Answer 1

comment_ID column in your wp_comments table is set as PRIMARY KEY, thus disallowing multiple records with same comment_ID value to be inserted into the table.

In your case, you already have a record with value "1" inserted, so you either have to remove that record (or empty the whole table) manually, or start inserting from a different comment_ID, or simply omit the comment_ID column and value, and it will most likely be automatically populated.

Reformat your query to look like this:

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 ( null, 1, 'Mr WordPress', '', 'http:////wordpress.org//', '', '0000-00-00 00:00:00', '0000-00-00 00:00:00', "Hi, this is a comment. 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, '1', '', '', 0, 0 ) ;

Where the value for comment_ID is null, OR

INSERT INTO wp_comments ( 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, 'Mr WordPress', '', 'http:////wordpress.org//', '', '0000-00-00 00:00:00', '0000-00-00 00:00:00', "Hi, this is a comment. 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, '1', '', '', 0, 0 ) ;

Where the comment_ID and it's value are omitted entirely.

In both cases, MySQL will assign the next auto-incremented value automatically.

Answer 2

Were you trying to migrate a WordPress site?

If so, if you did the following, the above mentioned error will occur.

1. You made a backup of your WordPress MySQL database
2. You setup a new WordPress site
3. You then tried to restore the backup in this newly setup WordPress site

After step 2 you should first delete all tables from the new WordPress database into which you will restore the backup in step 3.

The error messages appears becauase you've tried to import the MySQL dump (database file) into a non-empty WordPress database. When you setup a WordPress site it creates the needed tables, and inserts a few example records such as a comment. So, there's already a comment with id==1 in your database, and your backup tries to add another comment with id==1 (most likely the first real comment added on your website).