13
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【Laravel5.4】migrationができない。SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

Last updated at Posted at 2017-09-27

#はじめに
Laravel5.4の環境でmigrationを実行したら、下記エラーが出ました。。。
解決するまでにかなり苦労したので、備忘録的に記載します。

●migration時のエラー
SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

#環境

  • OS: CentOS7
  • RDS: Aurora
  • DB文字コード: utfmb4
  • InnoDB

#原因
InnoDBの行の最大長が約8KBで、それを超えるテーブルを作成しようとして無理だよと怒られていた模様です。

#解決するためにやったこと
下記①と②の対応がどちらも必要そうです(あくまで対応策の1つです)。

①RDSのパラメーターグループを下記の設定に変更

  • innodb_large_prefix: 1
  • innodb_file_format: Barracuda
  • innodb_file_per_table: 1

②Laravelのconfig/database.phpを下記のように編集

●修正前
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => 'null',

●修正後
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => 'InnoDB ROW_FORMAT=DYNAMIC', ★ここを修正

#調査時に行ったこと


  • 文字コード確認
> show variables like '%char%';
  • innodb_large_prefixの確認
> SHOW VARIABLES LIKE '%large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)
  • innodb_file_formatの確認
> SHOW VARIABLES LIKE '%file_format%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+
3 rows in set (0.00 sec)
  • innodb_file_per_tableの確認
> SHOW VARIABLES LIKE '%file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
  • テーブルの状態を確認(ここでROW_FOMATがCOMPACTかDYNAMICか判断する)
> use <Database name>;
> show table status like '%<Table name>%';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| wp_comments | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        81920 |         0 |              2 | 2017-09-08 19:10:27 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

#おわりに
ROW_FOMATをCOMPACTから、DYNAMICに変更して、migrationを実行するとうまく動作しました。
MySQL5.7からは、デフォルトでROW_FOMATはDYNAMICになっているようですが、
AWSのAuroraエンジンは、MySQL5.6に準拠してあり、デフォルトだとCOMPACTになっています。

皆様も気を付けてください。。。。

#その他メモ

  • テーブルのROW_FOMATをSQL文で変更する。
> use <Database name>;
> ALTER TABLE `<Table name>` ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

13
6
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
13
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?