thanks for the suggestions.
doing the insert in the commandline gives the same error.
i deleted all the other indexes except the one in question (just to
see if it changed anything)...same error.
did a check table (and a repair table and an optimize table) - no
change.
is it possible there is a MySQL server setting somewhere that is
causing it to truncate the index?
i was running MySQL version 5.0.27-community-nt on WindowsXP
just upgraded to 5.0.37 - but no change
i'm stumped...anyone got any ideas?
thanks,
jerad
On Mar 13, 2007, at 6:24 PM, Steve Edberg wrote:
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 ,x
mltitle ) 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
[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)
754-9127 |
+---------------- said t e lawrence, picking up his fork
----------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]