エラー対応：Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes with query…
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周りの説明は端折ります。）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `username` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `password` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `group` INT NOT NULL DEFAULT 1 , `email` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `last_login` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 0, `login_hash` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `profile_fields` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `created_at` INT( 11 ) UNSIGNED NOT NULL , UNIQUE ( `username` , `email` ) ) |
UsersテーブルをoilでGenerateしようとすると、以下のようなものになります。
1 2 3 4 5 6 7 8 9 10 11 | oil g model user username:varchar[50] password:varchar[255] group:int:default[1] email:varchar[255] last_login:int login_hash:varchar[255] profile_fields:text # 読みにくいので改行、実際は上記の1行コマンドを実行します oil g model user username:varchar[50] password:varchar[255] group:int:default[1] email:varchar[255] last_login:int login_hash:varchar[255] profile_fields:text |
生成されたMigrationは、以下のようなものになります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | <?php namespace Fuel\Migrations; class Create_users { public function up() { \DBUtil::create_table('users', array( 'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true), 'username' => array('constraint' => 50, 'type' => 'varchar'), 'password' => array('constraint' => 255, 'type' => 'varchar'), 'group' => array('constraint' => 11, 'type' => 'int', 'default' => '1'), 'email' => array('constraint' => 255, 'type' => 'varchar'), 'last_login' => array('constraint' => 11, 'type' => 'int'), 'login_hash' => array('constraint' => 255, 'type' => 'varchar'), 'profile_fields' => array('type' => 'text'), 'created_at' => array('constraint' => 11, 'type' => 'int'), 'updated_at' => array('constraint' => 11, 'type' => 'int'), ), array('id')); } public function down() { \DBUtil::drop_table('users'); } } |
ユニークキーの指定がありませんので、22行目に以下の一文を追加しましょう。
\DBUtil::create_index('users', array('username', 'email'), '', 'UNIQUE');
あとは、マイグレーションを実行しテーブルを作成します。通常は特に問題は発生しないはずです。
1 | oil r migrate |
問題発生と対処方法
マイグレーションを実行しようとすると私の環境では以下のようなエラーが出ました。
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文を実行します。
1 2 3 4 | oil g model user username:varchar[50] password:varchar[255] group:int:default[1] email:varchar[255]:charset[utf8] last_login:int login_hash:varchar[255] profile_fields:text # 変更個所だけ抽出 email:varchar[255]:charset[utf8] |
生成されたMigrationは、以下のようなものになります。前述の通り、ユニークキーの指定がありませんので、23行目に一文を追加しました。
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | // 省略 public function up() { \DBUtil::create_table('users', array( 'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true), 'username' => array('constraint' => 50, 'type' => 'varchar'), 'password' => array('constraint' => 255, 'type' => 'varchar'), 'group' => array('constraint' => 11, 'type' => 'int', 'default' => '1'), 'email' => array('constraint' => 255, 'type' => 'varchar', 'charset' => 'utf8'), 'last_login' => array('constraint' => 11, 'type' => 'int'), 'login_hash' => array('constraint' => 255, 'type' => 'varchar'), 'profile_fields' => array('type' => 'text'), 'created_at' => array('constraint' => 11, 'type' => 'int'), 'updated_at' => array('constraint' => 11, 'type' => 'int'), ), array('id')); \DBUtil::create_index('users', array('username', 'email'), '', 'UNIQUE'); } // 省略 |
マイグレーションを実行しテーブルを作成します。
1 | oil r migrate |
以上で完了です。
補足：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]とした場合は、期待通りのファイルが作成されませんでした。