かみぽわーる

kamipo's blog

MySQL(InnoDB) で "Index column size too large. The maximum column size is 767 bytes." いわれるときの対策

mysql innodb

tl;dr: MySQL 5.5.14以降だとinnodb_large_prefixオプションで3072バイトまでインデックス張れる

MySQL(InnoDB)では、ひとつのカラムのキープレフィックスの最大値が767バイトという制限があるので、ついうっかりして

Index column size too large. The maximum column size is 767 bytes.

とか

Specified key was too long; max key length is 767 bytes

といったエラーを見たことある人は多いのではないかと思います。

よくあるケースだと、varchar(256)以上のutf8なカラムにインデックスを張ろうとするとこのエラーとご対面できます。

CREATE TABLE t (c varchar(256), index (c));
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

で、話は変わってもうMySQL 5.5 GAが出てから一年が経ち、MySQL 5.6 GAもそろそろ出るころだし、新規で作るアプリケーションはutf8mb4でいきたいわけじゃないですか。

そこで、Rails(ActiveRecord)をutf8mb4で動かしたいわけなんですが、db:migrateするとしょっぱなからschema_migrationsってテーブルにインデックス張れなくて落ちます。

utf8のときのスキーマはこんなの。

CREATE TABLE `schema_migrations` (
  `version` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `unique_schema_migrations` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

ActiveRecordでstring型のカラムを定義すると、MySQLだとvarchar(255)になるので、utf8mb4だとインデックス張ると767バイトを超えてしまう。

で、なんかきれいに解決できる方法ないかなって考えたんだけど、MySQL 5.5.14以降だとinnodb_large_prefixというオプションが追加されてて、キープレフィックスの制限を3072バイトまで拡張できます。

ただし、ROW_FORMATをDYNAMICかCOMPRESSEDにする必要がある(デフォルトはCOMPACT)ので、ActiveRecordのcreate_tableメソッドをちょいと書き換えてデフォルトのROW_FORMATをDYNAMICにすればよさそうなんで、とりあえずそうしてます。

my.cnfに以下を追加して

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

最終的なスキーマはこんなかんじ。

CREATE TABLE `schema_migrations` (
  `version` varchar(255) NOT NULL,
  UNIQUE KEY `unique_schema_migrations` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

参考