MyISAM unique keys being cut off at 64 bytes, causing collisions

Question

I've got a MySQL table that stores urls as unique keys. I'm starting to get collisions on my keys because it seems the keys themselves are only the first 64 bytes (or characters if you prefer, its a latin-1 collated) of any url. So if a url is over 64 characters and I've already got a similar url it throws an error.

For example:

SELECT l.link_id FROM mydb.links l WHERE 
url = 'http://etonline.com/tv/108475_Charlie_Sheen_The_People_Have_Elected_Me_as_Their_Leader/index.html'

Throws this error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 
'http://etonline.com/tv/108475_Charlie_Sheen_The_People_Have_Elec' for key 'url'

Isnt MyISAM supposed to have 1000-byte key lengths?

EDIT: There doesn't seem to be a prefix length listed on the CREATE TABLE STATUS call it looks like this:

CREATE TABLE `links` (
  `link_id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(500) NOT NULL,
  PRIMARY KEY (`link_id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I tried to set one at 256 like this:

ALTER TABLE `mydb`.`links` 
DROP INDEX `url`, ADD UNIQUE INDEX `url` (`url`(256) ASC) ;

and I got the following error:

ERROR 1062: Duplicate entry '<...64-byte key...>' for key 'url'
SQL Statement:
ALTER TABLE `mydb`.`links` 
DROP INDEX `url`, ADD UNIQUE INDEX `url` (`url`(256) ASC)

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'links' already exists

I think the fallback is just because I ran the ALTER TABLE through MySQL Workbench.

Danford

Do a show create table on your links table. The index is probably defined with a length of 64. – Chil

Answer 1

When you create an index that only uses a prefix (by specifying the length of the index), then the prefix can be up to 1000 bytes (see 7.5.1 Column Indexes). Use SHOW CREATE TABLE to find out the actual length of the index.

Burkitt

Agree with what @Burkitt said. The official document says (link) :Indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an <index prefix length>... I encountered the same problem, but I can't find any official doc that says it's defaulting the index prefix length to 64 when the size of the chars-like column is gt 64 and you haven't specified the index prefix length. – Wages

Answer 2

I think the error message is only showing you the first 64 characters, but that doesn't mean the constraint is limited to 64 characters.

If your SHOW CREATE TABLE output is accurate, then the index is on all 500 characters, and you are hitting an exact duplicate.

Chil

I have also seen this error truncated at 64 characters, when it had nothing to do with the length of the primary key. (MySQL Ver 14.14 Distrib 5.1.61) – Kinnard

phew, same here and that's a weight off my mind. For a second I thought my DB was screwed. – Psittacine