CODEWITHSUNDEEP
mysqlmysql-error-1071
Kaare
Kaare

Reputation: 531

how do I circumvent the max key length in mySQL?

I'm creating a table indexing a lot of measurements, distributed in various files. In a different table, for a different type of data, I have used the absolute path to the data-file as a unique index. That has worked quite well.

However, when I now try to reproduce that setup in my new table, I get

mysql> alter table weights change column path path varchar(1000); ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Which is odd, because the same column in my other table is characterized as:

| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| path                | varchar(1000) | YES  | UNI | NULL    |       |

so, how did I circumvent this restriction last time? I should tell that I have reinstalled linux (& mySQL) since creating the first table, so it is likely that some default values have been changed without my knowledge.

When I start mySQL, the welcome message is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 128
Server version: 5.5.22-0ubuntu1 (Ubuntu)

when I have searched for previous answers, the general response seems to be along the lines of "well, that's just the way it is", but clearly it's not?

Thank you in advance :)

Upvotes: 0

Views: 399

Answers (1)

Eric Petroelje
Eric Petroelje

Reputation: 60549

Check the definition for the primary key - you may have used a partial prefix key index on the first table.

A better solution overall would be to use a hash of the file path as a primary key and store the file path itself in another column.

Upvotes: 3

Related Questions