Solved

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

Posted on 2014-02-08
16
1,025 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 28

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 42

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
 
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 42

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 42

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 28

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 28

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 42

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 42

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now