なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

RDS MySQLで、パラメータグループ指定したCollationでデータベースが作られないバグを踏んだ件

公式ドキュメント通りに操作しても、その通りに作られません

MySQLのCollationはどのように決まるか。そして、3つの落とし穴。 - なからなLifeの「3:Amazon RDSのデフォルトデータベースは、さらにルールを破る」のところで書いたとおりなんですが、大事なところなので、ここだけピックアップしてもう一度触れておきます。


インスタンス作成前にパラメータグループを用意してそれを指定しても、出来上がるデータベースのDefault Collationが違うもので作られることがあります。


具体的には、
1.パラメータグループの「character_set_」系をすべて「utf8mb4」、「collation_」系をすべて「utf8mb4_bin」に設定
2.AWS Management ConsoleからRDSインスタンス起動する時、このパラメータグループを指定
3.出来上がってくるデータベース(インスタンス起動時に名前を指定できるもの)は、
character_set = utf8mb4、collation = utf8mb4_general_ci


という具合です。



なお、パラメータグループの設定値は、立ち上がったMySQLのシステム変数には正しく反映されているので、インスタンスが立ち上がった後に追加でCreate Databaseをオプションなしで実行すると、期待通りのCharacterSet、Collationで作成されます。
なので、あくまで、RDSがデフォルトで1つデータベースを作る処理の中で引き起こされている、ということが分かります。


あくまでデータベースの「Default」 Collation

なので、テーブル作成時のDDLで明示的にCollationを指定していれば、何の影響もありません。


逆に、テーブル作成でCollationを指定せずデフォルト任せにしていると、この影響を受けている可能性大です。

確認は、information_schemaで。

MySQLは、DDLを確認できる「SHOW CREATE DATABASE」「SHOW CREATE TABLE」という便利なコマンドを用意していますが、このバグのケースでは、COLLATIONの指定が見えません。
(COLLATIONを明示指定していないDDLであること、これ自体がバグの原因ではあるのですが、そのへんのロジックは前のMySQLのCollationはどのように決まるか。そして、3つの落とし穴。 - なからなLifeエントリ参照)


なので、現在、どんな設定になっているかは、information_schemaをSELECTして確認してください。
それぞれ、以下のテーブルに情報が格納されています。
データベース:schemata
テーブル:tables
カラム:columns

Collationの設定が違うと、何が問題?

あくまでCollationなので、テーブルに格納されているデータの文字コードは変わりません。
しかし、Collationが「_bin」か「_general_ci」かで違っていると、以下のことが変わってきます。
1.SQLの条件抽出(WHERE、JOIN)
2.ソート順
3.インデックス格納順序


大文字小文字を同一視(Case Insencitive)すると、厳密に区別して戻りが1レコードと期待していた所に、2レコード以上返ってく可能性がありますね。
これで1、2が変わってくるのは、すぐに想像できる世界です。


テスト全部やり直しレベルですね、コレ。


インデックスは、定義た列だけを「あらかじめキレイに並べておくことで検索速度を向上させる」為のものなので、Collationが違えば、「効率のよい並び方」の定義も違ってきます。
なので、Collation変更が発生したら、インデックスは作り直しです。
プライマリキーをvarcharで作っている場合、テーブル側のデータ格納順も変わってしまいますね。


まとめ

・RDS-MySQLインスタンスを起動したら、Collationが正しく設定しているか確かめよう。違っていたらALTER DATABASEで変更しよう。
・すでにテーブルを作ってしまっている場合、information_schemaを参照して、この影響がテーブルやカラムに波及していないか確かめよう。
・正しくないCollationでテーブル作ってデータも格納してしまっている場合、影響をしっかり分析し、入念に計画を立てて変更をかけましょう。


MySQLトラブルシューティング

MySQLトラブルシューティング