ハマログ

株式会社イーツー・インフォの社員ブログ

MySQLのインデックスサイズに767byteまでしかつかえない問題と対策

Laravel5.4でmigrationを実行したところ、エラーになりました。
  [Illuminate\Database\QueryException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 
Specified key was too long; max key length is 767 bytes 
(SQL: alter table `users` add unique `users_email_unique`(`email`))

[Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 
Specified key was too long; max key length is 767 bytes

  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 
Specified key was too long; max key length is 767 bytes
実行したマイグレーションは以下です。
public function up()
{        
Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->integer('status')->nullable()->unsigned();
            $table->rememberToken();
            $table->timestamps();
    });
}
MySQLのリファレンスによると、
デフォルトでは、単一カラムインデックスのインデックスキーを最大で 767 バイトにすることができます。
ということで、768バイト以上のカラムに対するインデックスキーは設定できないようです。
さて、Laravelの場合、データベースのキャラクターセットは、config/database.phpで定義されています。Laravel5.3までは、以下の通りUTF-8でした。
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
UTF-8は1文字が3バイトのため、varchar(255)は、255文字✕3バイト=765バイトということで、問題なくインデックスを設定することができていました。

Laravel5.4からはutf8mb4が標準になりました。
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
utf8mb4は、1文字が4バイトとしてあつかわれるため、制限の767バイトを超過してしまい、MySQLのエラーが出力されるようになりました。

解決方法

その1. データベースのキャラクターセットをUTF-8にする。
絵文字をあきらめます。

その2. varcharの最大長を191バイトにする
767 ÷ 4 = 191.75 の、191バイトにします。

https://laravel-news.com/laravel-5-4-key-too-long-error曰く、AppServiceProviderに以下のように記載すればいいようです。
use Illuminate\Support\Facades\Schema;

public function boot()
{
  Schema::defaultStringLength(191);
}
その3. MySQLの行フォーマットの変更
https://dev.mysql.com/doc/refman/5.6/ja/innodb-restrictions.htmlや、https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefixを参考に、my.cnfで以下の設定をおこないます。
[mysqld]
innodb_large_prefix
innodb_file_per_table
innodb_file_format=Barracuda
これで、key長の制限が767バイトから、3072バイトに拡張されます。 ※MySQL5.7からは標準で、innodb_file_formatはBarracudaとなるため、記述が不要になります。

まとめ

なにもなければ、その3.レンタルサーバとかで設定を変更できない場合は、その2.
BarracudabytecharindexInnoDBkeyLaravelLaravel5.3Laravel5.4lengthMariaDBmysqlPHPstorageuniquevarchar

kaneko tomo   kaneko tomo   2017年4月17日


関連記事

VagrantとAnsibleで仮想環境のCentOS6.5にWordPress環境構築!WebサーバーはNginxですか?(中編)

はい、どーも! てるおです。 前編の戦いを経た今、恐れるものはありません。 早速…

Maven Cookbook にあった Jetty の手順を試してみる

はじめに Apache Maven 3クックブック という本を買いました そこで…

WordPressのContact Form 7でサンキューページを表示する

こんにちは、かねこです。 今回はWordPressの定番プラグインContact…


← 前の投稿

次の投稿 →