0

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 :)

Kaare
  • 531
  • 1
  • 7
  • 26
  • good answers to this question can be found here: http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/1814594#1814594 – Jeremy Goodell May 29 '12 at 15:20
  • I guess I plain don't understand those answers – Kaare May 30 '12 at 07:05

1 Answers1

3

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.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177