MySQL Specified key was too long

Question

I don't understand the error I'm getting. The only field longer than 767 is password but it's not an index or anything.

mysql> CREATE TABLE users (
    ->         id INTEGER NOT NULL AUTO_INCREMENT,
    ->         email VARCHAR(256) NOT NULL,
    ->         password VARCHAR(1024) NOT NULL,
    ->         date_added INTEGER,
    ->         PRIMARY KEY (id),
    ->         UNIQUE (email)
    -> );
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

See http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes. Is this an INNODB table? — Sablefoste, Jan 03 '14 at 17:07
@N.B.I'm saying that `password` is the only one longer than 767 but it's not an index. — Mark, Jan 03 '14 at 17:09
@Mark: Cool! :-) See [`@Krish R`'s answer](http://stackoverflow.com/a/20909000/206403) for an explanation as to why that worked :-D — gen_Eric, Jan 03 '14 at 17:10
@Mark: Which version of MySQL you are using. Because, I am not getting any error when executed this create statement. I am using mysql 5.5.34 — ursitesion, Jan 03 '14 at 18:01

score 3 · Accepted Answer · answered Jan 03 '14 at 17:07

VARCHAR(1024)

MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

Prior to MySQL 5.0.3, a VARCHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, VARCHAR(500) is converted to TEXT, and VARCHAR(200000) is converted to MEDIUMTEXT.

Ref: http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

http://dev.mysql.com/doc/refman/5.0/en/char.html

MySQL Specified key was too long

1 Answers1

Linked