mercoledì 9 marzo 2011

Specified key was too long MySQL error creating index

I stumbled across this error while coding my Ultimate RSS Reader[tm]. I was using (admittedly, not the best choice) TEXT fields to store URI for RSS feeds. Anyway, my table looked like this:

mysql> create table simple (ID integer auto_increment primary key 
not null, content text) charset utf8 engine innodb;

Query OK, 0 rows affected (0.07 sec)

mysql> show create table simple;
| Table  | Create Table                                            |
| simple | CREATE TABLE `simple` (
  `content` text,
1 row in set (0.00 sec)

And my create index query (and error) was something along these lines:

mysql> create unique index `content_idx` on `simple` (content(256));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

I didn't understand why 256 (or more) characters were too much for the 767 bytes limit on InnoDB tables. Turns out that UTF8 could use up to 4 bytes for each character, and MySQL 5,1 uses by default 3 bytes for each character (this has changed in MySQL 5.5), then the limit for this value is 255 characters, or 255*3 bytes and (255*3) is less than 767.

By the way, since MySQL 5.0 the VARCHAR data type could host up to 64k characters, so the correct data type for storing your URIs could be something like VARCHAR(4096).

Many thanks to Patrizio, Stardata CEO for clarifying this issue to me!

Nessun commento:

Posta un commento