MySQLで外部キーが貼れない原因とCharsetについて

MySQLでテーブルの作成はできるけど、外部キーを貼ろうとすると謎のエラーが発生しました。
その原因を調べる上でMySQLのCharsetの設定について多少詳しくなったのでメモっておきます。

外部キーが貼れなかったエラー

今回、新しくテーブルを作って、既存テーブルに対して外部キーを貼ろうとしたところ、エラーが発生してダメでした。

エラーログ

エラーログは以下の通りでした。情報が全く足りません。。。

ERROR 1215 (HY000): Cannot add foreign key constraint

そのような場合は、以下のSQLを実行することでエラーの詳細を見ることができます。

show engine innodb status;

今回は、以下のログが出力されていました。

2022-05-18 10:27:06 xxx Error in foreign key constraint of table caisuke/#sql-1_2d8:
FOREIGN KEY
    xxx (hoge_id)
    REFERENCES hoge (id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

英語が難しくてよくわからないのですが、

• 参照先のカラムのインデックスが見つからない
• 参照先のカラムの型が制約に使えない型である

みたいな感じのことが書いてあります。
しかし、今回は参照先のカラムは PK で、UUIDなので型もchar(32)なので問題なさそうです。

原因

ログからはわからなかったのですが、参照元と参照先のテーブルのテーブル定義を調べてみると、charsetが異なっており、これが原因でした。

> -- 参照先テーブル
> show create table hoge;

CREATE TABLE `hoge` (
  `id` char(32) NOT NULL,
  ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

>  -- 参照元テーブル
> show create table fuga;

CREATE TABLE `fuga` (
  `id` char(32) NOT NULL,
  `hoge_id` char(32) NOT NULL,
  ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

このようなことは通常発生しないと思いますが、今回のケースでは、create table時にcharsetは特に設定していません。

ということは、データベースのcharacter_set_databaseの設定に従ってテーブルのcharsetが決定されるはずです。（参照）

よくよく考えてみると、

• 参照先テーブル（hoge）は、stagingのDBをダンプしてlocalのDBにインポートしたもの
• 参照元テーブル(fuga)は、localのDBで今回新しく作成したもの

のように、由来が違うものでした。

なので、各環境のDBの'character_set_database'の設定を確認すると、ビンゴ、やはりutf8とutf8mb4で設定が異なっていました。

-- staging
show variables like 'character_set_database';

Variable_name   Value
character_set_database  utf8mb4

-- local
show variables like 'character_set_database';

Variable_name   Value
character_set_database  utf8

対応

MySQLでは、utf8は4バイト文字には対応しておらず、4バイト文字を格納するには、MySQL 5.5で導入されたutf8mb4を利用する必要があります。なので、今回はutf8mb4に統一することにしました。

具体的には、my.cnfを以下のように修正することにしました。

[mysqld]
- character-set-server = utf8
+ character-set-server = utf8mb4

その上で、テーブルを再作成して、外部キーを貼ってみると問題なく外部キーを貼ることができました。

MySQLのcharset設定

エラー対応のために、ドキュメントを読んでると、なんとなくcharsetの設定内容がわかってきました。

どうやら、大きく2種類のcharsetの設定があるようです。

• サーバサイドのcharset
• クライアントから接続時のcharset

現在の設定は、以下のSQLで確認できます。

show variables like '%character%';

サーバサイドのcharset

サーバサイドの設定は以下の通りになっています。

変数名	値	説明
character_set_server	utf8mb4	DBサーバのデフォルトcharset
character_set_database	utf8mb4	（create database で作る）データベースのデフォルトcharset。テーブルのcharsetも指定この値が利用される
character_set_system	utf8mb4	データベース名、テーブル名、インデックス名などの識別子を格納する時のcharset
character_set_filesystem	binary	load_file関数などで扱うファイル名を解釈するためのcharset。デフォルトのbinaryは無変換を表すが、日本語などのマルチバイド文字列をファイル名に使う場合は別の値を設定する方がいいケースもあるらしい

設定はmy.cnfにて以下のように行います。

[mysqld]
character-set-server = utf8 # server, database, systemの設定に反映される
character-set-filesystem = utf8

クライアントから接続時のcharset

接続時のcharsetに関しては こちら に記載があります。
クライアントの設定は以下の通りになっています。

変数名	値	説明
character_set_client	utf8mb4	クライアントがサーバに送るデータ（=SQL）のcharset
character_set_connection	utf8mb4	サーバ側が受けとったデータ（=SQL）を解釈するcharset
character_set_results	utf8mb4	結果をクライアントに返却する時のcharset

設定はmy.cnfにて以下のように行います。

[client]
default-character-set = utf8 # client, connection, resultsの設定に反映される

ちなみに、接続時の設定はclient側のアプリケーションやソフトにて設定されます。

MySQLのgeneral_logをみていると、自分が使っているDBクライアントで接続した場合に、以下のSQLが実行されていました。

set names = 'utf8mb4';

これによって、上記の3つの接続時のcharsetが変更されていました。