• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1161
  • Last Modified:

Error Code: 1062. Duplicate entry '620' for key 1

I am restoring for one table after I deleted many rows by mistake.
Long insert command  written in format




INSERT INTO `a_messages` VALUES (51,8320165,'user1','user1',430475,'I agree','2013-12-04 01:39:00',5,'staff2'),(52,8221,'user2','staff3',1681903,'the earlier the better for me','2013-12-04 00:52:33',5,'staff3'),(53,859821,'bob','bob',4375712,'ya','2013-12-04 00:52:33',5,'staff3'),
...


CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2110 DEFAULT CHARSET=utf8;

Open in new window


Error Code: 1062. Duplicate entry '620' for key 1
0
rgb192
Asked:
rgb192
  • 5
  • 4
  • 3
  • +2
1 Solution
 
becraigCommented:
It looks like you are trying to insert a value into a column that auto increments.
My suspicion would be

  `a_messages_id` int(11) NOT NULL auto_increment,

You either need to not specify a value for that column or change it from auto-increment to allow your insertion.
0
 
InsoftserviceCommented:
CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2110 DEFAULT CHARSET=utf8;

AUTO_INCREMENT=2110 remove it.
ENGINE=MyISAM DEFAULT CHARSET=utf8;

when you are trying to insert 2110 it will give error as by default your auto incremented value is 2110
0
 
Chris StanyonCommented:
You are trying to insert 2 records with the same primary key (a_messages_id) - 620

auto-increment has nothing to do with it!
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
InsoftserviceCommented:
Duplicate error means you are trying to insert a_messages_id twice its because AUTO_INCREMENT=2110. Please check my first comment to resolve it
0
 
Chris StanyonCommented:
Read my previous comment - auto-increment has nothing to do with it!
0
 
InsoftserviceCommented:
I know auto increment has nothing to do exactly. But its because of auto increment unique number has been inserted already which author is trying to insert again.
I thought author is trying to create table again and then insert the data..

 select  a_messages_id from a_messages where a_messages_id in ('602,'51','52','53','54');

This are the numbers you are trying to insert in table to check which a_message_id has been inserted already.
0
 
Chris StanyonCommented:
@insoftservice

The error the user is getting is DUPLICATE KEY exists. Removing the AUTO_INCREMENT setting will have no bearing on this at all. That's not what's causing the error. If the duplicate already exists in the table, then it already exists, so the AUTO_INCREMENT will have no effect.

The only reason this problem is arising is because he is trying to insert a record with a PRIMARY KEY value of 620, and a record with that PRIMARY KEY already exists in the table.
0
 
InsoftserviceCommented:
I already commented that i thought author is trying to create table again and then insert the data.

select  a_messages_id from a_messages where a_messages_id in ('602,'51','52','53','54');

This are the numbers you are trying to insert in table to check which a_message_id has been inserted already.
0
 
becraigCommented:
I think the long and short of it guys we are all saying the same thing
(2 items that make this an issue)

1) The column design does not allow for it be a null value but it increments based on the previous values (This could disallow any new insertions)
`a_messages_id` int(11) NOT NULL auto_increment,
2) This column is allow a primary key and more importantly has a unique constraint.
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`).

He can simply redesign the table to not have that constraint, however the question would be if he is inserting data that was previously present with this same design why would he have duplicate values where there should have previously been none ?

I think the question to answer would be:
Are there any other relationships already linked on the previously created messageid ?
If so then I can see a need to preserve, if not then simply continue the insert without that value and let the DB do it's work and create a new value for each message inserted.
0
 
rgb192Author Commented:
Is there a command to disable primary key

I am trying to do the long insert command


I think some of the suggestions are to create a new table (difficult)
and some suggestions are to change the insert (very difficult)
0
 
becraigCommented:
I would make the following change:

ALTER TABLE a_messages DROP INDEX unique_message_id

This should remove the unique constraint and allow you to enter the value you want.
0
 
Chris StanyonCommented:
If you remove the primary key, then your data integrity goes out of the window, and you will end up with a pretty useless table of data. Don't do it!

If you simply want to skip the rows with a duplicate ID, and avoid errors then just add IGNORE to your INSERT statement:

INSERT IGNORE INTO `a_messages` VALUES
(51,8320165,'user1','user1',430475,'I agree','2013-12-04 01:39:00',5,'staff2'),
(52,8221,'user2','staff3',1681903,'the earlier the better for me','2013-12-04 00:52:33',5,'staff3'),
(53,859821,'bob','bob',4375712,'ya','2013-12-04 00:52:33',5,'staff3')

Open in new window

If you need to update existing records, and insert new ones, then just change it to a REPLACE statement:

REPLACE INTO `a_messages` VALUES
(51,8320165,'user1','user1',430475,'I agree','2013-12-04 01:39:00',5,'staff2'),
(52,8221,'user2','staff3',1681903,'the earlier the better for me','2013-12-04 00:52:33',5,'staff3'),
(53,859821,'bob','bob',4375712,'ya','2013-12-04 00:52:33',5,'staff3')

Open in new window

0
 
rgb192Author Commented:
solved:

I dropped the table
and created again

because I have the insert from when hosting company got my data

so whose advice led me to that conclusion.
0
 
Chris StanyonCommented:
If you deleted the table and started again, then you didn't really find a solution - more of a work around. There was no need to do it that way, but if you got it working, then great. No one here suggested you drop the table completely as there was no need to.

If you'd have used the REPLACE or IGNORE statements, then your problem would have been resolved without having to delete the table.
0
 
SurranoSystem EngineerCommented:
I assume the problem originated from what you tried:
- you did not delete the remaining records
- but you tried to restore all the records, not only those that were deleted.
So basically the solution should've been what Chris Stanyon wrote (i.e. INSERT IGNORE). I recommend giving him the credits.
0
 
rgb192Author Commented:
This would have worked.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now