Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-08
16
Medium Priority
?
1,098 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
  • 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 44

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 44

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 44

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 44

Accepted Solution

by:
Chris Stanyon earned 2000 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 44

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month11 days, 4 hours left to enroll

885 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