Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to create a table in MySQL with the statement:

CREATE TABLE tags (Tag VARCHAR(200) NOT NULL default '', Login VARCHAR(255) NOT NULL default '', PRIMARY KEY (Tag, Login)) ENGINE=MyISAM;

However I'm getting an error:

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

If I reduce the Tag field to VARCHAR(78) the table is created, however VARCHAR(79) fails. Why is any varchar size above 78 failing? I've read a few posts around the web about MySQL storing VARCHAR as UTF-8 which can take up to six bytes, but 79 * 6 is 474 which is well within the 1000 byte limit so not sure they UTF-8 thing is accurate.

share|improve this question
    
your primary key is Tag+Login, don,t you forget to add the Login bytes? –  regilero Jan 11 '13 at 15:41

1 Answer 1

up vote 1 down vote accepted

Your primary key consists of both Tag and Login, which combined are 355 characters long.
If Tag is 78 characters the combined total is 333, so it looks like MySQL makes a 3-byte-per-character assumption:

(78 + 255) * 3 = 999, (79 + 255) * 3 = 1002

That said, VARCHAR values are notoriously unsuited for Primary Keys.

Consider adding an INT instead.

share|improve this answer
    
Thanks. Regarding the INT, dunno if that will work. This is a problem I'm trying to debug in ocsinventory (ocsinventory-ng.org/fr) install. –  Server Fault Jan 11 '13 at 15:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.