AWS RDSへのimport時に「Specified key was too long;」で失敗したときの対策

株式会社NoSchoolでCTOをしている@mejilebenです。
AWS RDSに入門しているとちょっと詰まったことがあったので記事に書きました。

TL;DR

RDSにデータをインポートしようとSQLを流し込んだら

ERROR 1071 (42000) at line 27705: Specified key was too long; max key length is 767 bytes

と怒られました。この対策にはinnodb_large_prefixを1に設定する必要があるのですが、RDSではコマンドラインからMySQLの設定値を更新できませんでした。

AWS Consoleから「パラメーターグループ」なるものを編集することで対応したので、そのログを書きます。

インポート時のコマンド

MySQLのデータインポート・エクスポート - Qiita
を参考に実行しました。標準入力からSQLを流し込みました。

なぜエラーが起きたのか

Keyに設定した文字長が長すぎた

テーブルのKeyに指定しているカラムのバイト数が、インデックスの限界で規定されているバイト数を超えたから起きたエラーです。

MySQLのインデックスサイズに767byteまでしかつかえない問題と対策より、UTF-8を指定している場合は1文字あたりのバイト数が4バイトになることがあり、varchar(255)のカラムではその4倍が限界値である767をゆうに越してしまいます。

あまりに文字長が長いとインデックスを貼っても効果が薄れるような背景があるのでしょうか。そのへんはまた詳しく調べてみるとして・・・

今回私が実行した文でエラーが起きたのは以下の箇所です。

sql

CREATE TABLE `hogehoge` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hoge` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`name`),
  UNIQUE KEY `hoge_unique` (`hoge`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CHARSETがutf8mb4になっており、KEYを貼っているカラムがvarchar(255)なので、見事に767バイトを超えてしまうことがわかります。

回避方法

テーブルの文字コードを変えたり、バイナリに変換するなりの方法が先のブログでは紹介されていますが、面倒くさいのでやりたくないと思いました。

そこで、innodb_large_prefixを使ってERROR 1071を回避するに書いてあるとおり、innodb_large_prefixを1に設定することで回避しようとしました。

要はバイト数を超えても怒られないように、データベース側の設定値を変えてしまうという発想です。

RDSではset globalコマンドが実行できない

これで回避できると一安心したのですが、そうは行きませんでした。

mysql> set global innodb_large_prefix = ON;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

権限不足でコマンドが実行できません。
これは[AWS] RDS/MySQLではユーザにSUPER権限が付与できない – 端くれプログラマの備忘録
にかかれている通り、そもそもAWS RDSではユーザーにSUPER権限を付与することができないので、解決不能な問題だということがわかりました。

ということは、今回のようにMySQLの設定値を変更することはRDSだと叶わないのでしょうか。

RDSでMySQLの設定値を変更するには

そんなわけないと思いながら必死にググっていると、
mysql - Amazon RDS unable to execute SET GLOBAL command - Stack Overflow
ちょうど同じ状況のStackOverflowを見つけました。

use an RDS parameter groupということで、RDSの「パラメーターグループ」なるものを使いなさいと書いてあります。

RDSのConsoleを開いてみると、下記画像のように、「設定」タブの左下に 「パラメーターグループ default:mysql5.7」 と書いてあることが読み取れます。

パラメーターグループをクリックしてみると

こんな画面に飛びます。これはdefaultのパラメーターグループで編集不可なので、

に戻って「パラメーターグループの作成」からパラメーターグループを作ります。

作り終わったら先程と同様、パラメーターグループ名をクリックして詳細に飛び、innodb_large_prefixと入力して、

出てきたinnodb_large_prefixに対して値1を設定します。

ここまで終わったら、RDSのデータベースの方の画面に戻りまして、画面右上の「変更」を押します。

ここからパラメーターグループを変更できます。

ここで注意が2点ありまして、

• 変更後は設定が反映されるまで数分かかります
• パラメーターグループが対象となっているMySQLのバージョンと本体のバージョンがズレていたらそもそも設定できません

という罠がありますのでお気をつけください。また、確証はできないのですが、削除保護をONにしているとどうやら再起動してくれなくて設定値が反映されない気がします。もしいつまでたっても反映されなかったらそちらも確認してください。

ここまで終わったら設定値を確認しに行きましょう。新しいパラメーターグループに変わっているはずです。

ここまで終わったら再度importコマンドを走らせましょう。
以上です。

RDSのパラメーターグループは初耳だったので、勉強になりました。

最後に

僕が働いているNoSchoolでは仲間を募集中です！
創業期真っ只中、社員は僕と社長の2人きりでEdTechを変えに行ってます！

興味あればお茶でもしましょう。Twitterからの連絡でも大歓迎です！
https://www.wantedly.com/companies/noschool