5

When running a Rails migration that creates an index, I get:

Mysql::Error: Specified key was too long; max key length is 1000 bytes: CREATE UNIQUE INDEX `index_matches_on_foo_and_bar_id_and_baz_id` ON `matches` (`foo`, `bar_id`, `baz_id`)

What MySQL variable do I need to set to increase this, and where do I set it so it affects all sessions, and not just the current client session?

Josh Glover
  • 25,142
  • 27
  • 92
  • 129
  • Can you reduce the size of foo? It'll help while taking into account the 2 answers below. – Srdjan Pejic May 05 '11 at 13:45
  • No, unfortunately not. I'm building a database from a blank schema for a production Rails app on my development environment, so no changes allowed (except to the migration that is causing the problem, of course). – Josh Glover May 05 '11 at 15:41
  • The answer to this question was already given [here](http://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes/3489331#3489331) and [there](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/1814594#1814594). – dma_k Nov 10 '11 at 10:24
  • An interesting question would be why you need to index a field of that size... – bobflux May 05 '11 at 15:04

3 Answers3

9

InnoDB actually only allows 768 BYTES in the index. Also, note that UTF-8 encoded strings take up 3 bytes per character, so you only have 768 / 3 chars to play with in the index in that case.

A possible solution is to limit the length of the field use in the index. However, since you also want a unique index, that might not be an acceptable solution for you. Use the following to limit the length of the fields used.

CREATE UNIQUE INDEX `index_matches_on_foo_and_bar_id_and_baz_id` ON `matches` (`foo`(100), `bar_id`(100), `baz_id`(100))
Anders Arpteg
  • 337
  • 1
  • 3
8

From the MySQL manual:

"The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling"

AJ.
  • 27,586
  • 18
  • 84
  • 94
1

Just change the collation from one option to another that supports the key length needed. I personnally had to chnage from server default to latin 1 - default.

D. Maasz
  • 11
  • 1