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.

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

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.

  • 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. Jan 11 '13 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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