エラー対応:Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes with query…

Pocket
2 interests

42000 – SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes with query: “CREATE UNIQUE INDEX `username_email` ON `users` (`username`, `email`)” in COREPATH/classes/database/pdo/connection.php on line 175

FuelPHPでSimpleAuthを利用したアプリケーションを作成している過程で、上記のようなエラーが出て、引っかかったので対処法をメモしておきます。FuelPHP側の問題ではなく、MySQLのDEFAULT CHARACTER SETにutf8mb4を指定していたのが原因です。

環境

環境は以下の通りです。パスやバージョン等は、必要に応じて読み替えてください。

ソフトウェア バージョン 備考
FuelPHP 1.3
MySQL 5.5.9 MAMP2.0.5のMySQLを利用しました

SimpleAuth用のUsersテーブルを作成する

SimpleAuthを利用するための流れを確認します。
SimpleAuthを利用するためには、SimpleAuth – Introductionにある通り、以下のようなUsersテーブルが必要になります。(config周りの説明は端折ります。)

UsersテーブルをoilでGenerateしようとすると、以下のようなものになります。

生成されたMigrationは、以下のようなものになります。

ユニークキーの指定がありませんので、22行目に以下の一文を追加しましょう。
\DBUtil::create_index('users', array('username', 'email'), '', 'UNIQUE');

あとは、マイグレーションを実行しテーブルを作成します。通常は特に問題は発生しないはずです。

問題発生と対処方法

マイグレーションを実行しようとすると私の環境では以下のようなエラーが出ました。

42000 – SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes with query: “CREATE UNIQUE INDEX `username_email` ON `users` (`username`, `email`)” in COREPATH/classes/database/pdo/connection.php on line 175

MySQLのCharacter Setをutf8mb4としており、対象としたキーの長さが767バイトを超えてしまったためです。これはMySQLの仕様なので仕方がありません。ここではemailのCharacter Setをutf8やlatin1等に変更することで対応しました。対応方法は以下のサイトが参考になります。
参考リンク:MySQLのUNIQUEなINDEXには長さ767byteまでしか使えない件と対策

以下のようにCharacter Setを指定したGenerate文を実行します。

生成されたMigrationは、以下のようなものになります。前述の通り、ユニークキーの指定がありませんので、23行目に一文を追加しました。

マイグレーションを実行しテーブルを作成します。

以上で完了です。

補足:latin1を指定する場合はさらに気をつける

ジェネレータで email:varchar[255]:charset[latin1]とすると、Character Setをlatin1に指定することができます。生成されたものも、当然ながら 'charset' => 'latin1'となっているのですが、このまま oil r migrateを実行すると、Character Setが latin1_swedish_ciとなってしまいました。
私の環境だけで起きたことかもしれませんが、生成されたMigrationを編集して 'charset' => 'latin1_general_ci'等としておくと良いと思います。
ジェネレータで email:varchar[255]:charset[latin1_general_ci]とした場合は、期待通りのファイルが作成されませんでした。

Pocket
2 interests