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