Solved

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

Posted on 2014-02-08
16
1,052 Views
Last Modified: 2014-02-10
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
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
16 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 39844742
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
 
LVL 15

Expert Comment

by:Insoftservice
ID: 39845066
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39845231
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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 15

Expert Comment

by:Insoftservice
ID: 39845333
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39845344
Read my previous comment - auto-increment has nothing to do with it!
0
 
LVL 15

Expert Comment

by:Insoftservice
ID: 39845396
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39845405
@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
 
LVL 15

Expert Comment

by:Insoftservice
ID: 39845438
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
 
LVL 29

Expert Comment

by:becraig
ID: 39845650
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
 

Author Comment

by:rgb192
ID: 39846121
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
 
LVL 29

Expert Comment

by:becraig
ID: 39846135
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
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39846150
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
 

Author Comment

by:rgb192
ID: 39846286
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39846293
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39846703
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
 

Author Closing Comment

by:rgb192
ID: 39847473
This would have worked.  Thanks.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question