List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:March 14 2007 1:24am
Subject:Re: getting a " Duplicate entry" error when inserting a new
record - but there is no duplicate record
View as plain text  
At 5:24 PM -0700 3/13/07, jerad sloan wrote:
>thanks for the quick reply.
>
>i want that to be unique so there is no way to insert the same URL 
>more than once.
>there isn't a record with that URL...but it gives the error 
>anyway...it seems to be just checking a portion of the entry i'm 
>trying to insert.
>i don't want uniqueness across a set of columns...just that one column.


Aah, I wasn't following the emails closely enough; sorry. Your error 
message does indeed look a bit puzzling, as it only shows the first 
64 characters. Key length is limited to 1024 -

	http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

- and even the sum total length of all your keys is only something 
like 440 bytes. Two other options I can think of: there's a hidden 
ascii NUL character in there between the 'vid' and 'eoid...' that's 
terminating the string, or you might have some table corruption. A 
check table -

	http://dev.mysql.com/doc/refman/5.0/en/check-table.html

- might illuminate that.

One other thought: have you tried inserting the records by hand from 
the Mysql commandline prompt? Perhaps the program is truncating the 
field at 64 chars, so it's not a mysql problem at all. Or,  if this 
data is coming from an HTML form, perhaps there's a maxlength 
parameter on the videourl form field.


>what do you mean by
>(3) drop index videourl; create index videourl (videourl).
>drop the unique index and create a non-unique index?
>that isn't what i'm trying to do...i need it to be unique.


You can disregard this; again, didn't read the original message 
closely enough. This would indeed create a non-unique index.

	steve


>thanks,
>jerad
>
>
>On Mar 13, 2007, at 4:55 PM, Steve Edberg wrote:
>
>>At 4:36 PM -0700 3/13/07, jerad sloan wrote:
>>>thanks.
>>>i had an extra index setup for one of my fields...i deleted it and 
>>>it started working as expected...but now the same thing is 
>>>happening again.
>>>
>>>i'm getting the error
>>>"Duplicate entry 
>>>'http://vids.myspace.com/index.cfm?fuseaction=vids.individual&vid' 
>>>for key 2"
>>>
>>>when doing the following
>>>insert into tblvideoURL 
>>>(videourl,userid,shortname,videourlcreated,videoURLcurrentstatus 
>>>,xmltitle ) values 
>>>('http://vids.myspace.com/index.cfm?fuseaction=vids.individual&videoid=1951753288',3,'MySpace',{ts
> 
>>>'2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely 
>>>Wasted (director unknown - hopefully Emily?!)' )
>>>
>>>here is the Show Create Table
>>>
>>
>><SNIP>
>>
>>>   PRIMARY KEY  (`videourlID`),
>>>   UNIQUE KEY `videourl` (`videourl`),
>>>   KEY `videoURLcurrentstatus` (`videourlcurrentstatus`),
>>
>>
>>Key 2 is defined as unique; thus, the error message says you're 
>>inserting a record where videourl duplicates an existing record. 
>>Solutions:
>>
>>(1) don't do that.
>>(2) drop index videourl
>>(3) drop index videourl; create index videourl (videourl).
>>(4) if you want to ensure uniqueness across a set of columns, 
>>define a unique composite key, for instance: create unique index01 
>>(videourl,userid);
>>
>>Indexes/keys do not have to be unique unless they are primary or 
>>defined as unique.
>>
>>	steve


-- 
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg                                http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center                            sbedberg@stripped |
| Bioinformatics programming/database/sysadmin             (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
Thread
getting a " Duplicate entry" error when inserting a new record - but there is no duplicate recordjerad sloan8 Mar
  • Re: getting a " Duplicate entry" error when inserting a new record - but there is no duplicate recordBrian Mansell8 Mar
    • Re: getting a " Duplicate entry" error when inserting a new record - but there is no duplicate recordjerad sloan14 Mar
      • Re: getting a " Duplicate entry" error when inserting a newrecord - but there is no duplicate recordSteve Edberg14 Mar
        • Re: getting a " Duplicate entry" error when inserting a new record - but there is no duplicate recordjerad sloan14 Mar
          • Re: getting a " Duplicate entry" error when inserting a newrecord - but there is no duplicate recordSteve Edberg14 Mar
            • Re: getting a " Duplicate entry" error when inserting a new record - but there is no duplicate recordjerad sloan14 Mar