文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

MySQLのIllegal mix of collations (latin1_swedish_ci,IMPLICIT)のエラーについて

以外と原因に気づかなかったりするんですよねこれ。


f:id:treeapps:20180418131549p:plain

MySQLを使っていて、たまに以下のようなエラーが起きる事があります。

[SQLで例外(Message=[Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'], ErrorCode=1267, SQLState=HY000)が発生しました。

エラーメッセージ通りなら、文字コードが何か誤っている、という事なのですが、具体的に何が原因なのでしょう。

よくあるエラーケース

MySQLのバージョンは5.5〜5.7が対象になります。この記事の事を実際に試したい場合は、docker-machineでmysqlのコンテナを作るとよいです。以前書いたこちらの記事も合わせてご覧下さい。
www.bunkei-programmer.net

MySQLの設定はこんな感じです。

 mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

文字コードがutf8mb4のデータベースを作成します。

create database hoge charset utf8mb4;

テーブルがutf8mb4以外のケース

文字コードがlatin1のテーブルを作ります。

drop table if exists tbl_table_charset;
create table tbl_table_charset(
    v varchar(10)
)engine=innodb charset=latin1;

このテーブルでwhere句にマルチバイト文字列を使おうとすると、このエラーが起きます。

mysql> select * from tbl_table_charset where v = 'あ';
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

insertでマルチバイトを使おうとすると、また違ったエラーになります。

mysql> insert into tbl_table_charset values('あ');
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\x82' for column 'v' at row 1

データが無い状態でupdateした場合は、エラーになりません。

mysql> update tbl_table_charset set v = 'あ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

データが有る状態でupdataした場合は、先ほどと同じエラーになります。

mysql> insert into tbl_table_charset values('a');
Query OK, 1 row affected (0.00 sec)

mysql> update tbl_table_charset set v = 'あ';
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\x82' for column 'v' at row 1

カラムがutf8mb4以外のケース

utf8mb4のテーブルに、latin1のカラムを混ぜ込みます。

drop table if exists tbl_col_charset;
create table tbl_col_charset(
    v varchar(10) character set latin1
)engine=innodb charset=utf8mb4;

そしてlatin1のカラムに大して前述と同様にwhere句にマルチバイトを使ってしまうと、やはりエラーになります。

mysql> select * from tbl_col_charset where v = 'あ';
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

こんなケース滅多に遭遇しないだろ、と思いますか?以外とそうでもありません。

ケース1)大きすぎるテーブル

InnoDBのファイルフォーマットがAntelopeになっている。その状態でInnoDBのテーブルを作成し、カラムに大きな値のカラムを沢山作ってしまい、大量のデータをinsertしようとして

ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

こんなエラーが出てしまい、テーブル容量の節約をしようとして、シングルバイトしか入らないカラムを片っ端からlatin1にしてしまった場合です。

カラムの文字コードを個別に設定した場合、そういう設定がされている事を調べるには「show create table xxx」するしかありません。「desc xxx」では解らないのです。なので、「え!?このカラムlatin1なの!?」と後から知って愕然とするケースもあります。

ケース2)メールアドレス

メールアドレスのカラムを作る時、最も問題となるのはフィールド長です。

文字コードをutf8mb4にすると、インデックスが貼れるのは最大で191byteまでです。191文字で済む要件の場合は問題無いのですが、「500文字まで受け付けたい!」等と言われると、途中までしかインデックス化できません。そこでよく使われる手法として、メアドのカラムを複数に分割して、ユニークキーのインデックスを貼る事で解決を図ります。その点については大分前に記事を書いたので、合わせてご覧下さい。

www.bunkei-programmer.net

こうしてlatin1のカラムが生まれたりします。

で、何が問題なのか

先ほどのコンソールを見て貰うと解りますが、selectしただけでエラーが発生してしまう点にあります。つまり、アプリケーションからlatin1等のマルチバイト非対応カラムに対してマルチバイトで検索するwhere句を発行されると即エラーになるのです。と言うことは、バリデーション処理が必須になるという事に繋がります。

例えば「商品コード」というvarcharで英数字が入るカラムがあったとして、通常ならそのカラムが「あああ」等と検索されてもエラーにならず、0件の結果が返ります。しかしこの商品コードがlatin1等である場合、「あああ」と検索された場合はSQLExceptionが確定してしまうので、シングルバイトの入力チェックバリデーションが必須になります。商品コードは最初はutf8mb4だったがいつからかlatin1に変更されていて、最近「Illegal mix of collations (latin1_swedish_ci,IMPLICIT)」なんてエラーが出るんだけど、なんぞこれ?というケースがあります。今まで必要無かったバリデーションが必要になってしまうのです。

雑感

このお話、残念ながらノンフィクションです・・・

antelopeとbarracudaの違いも知らず、row_type?何それ?という時代に設計したテーブルが段々と肥大化してしまい、ついにantelope+utf8mb4のInnoDBテーブルのサイズの限界に達してしまい、大きいデータが来た時にのみエラーが出てしまうという状況に遭遇し、以下のエラーに悩まされました。

ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

この制限は確かMySQL5.5以降から発生するので、「joinするの面倒だから1テーブルに全項目を押し込めようぜ!」等と横着をしたりせず、適宜テーブルを分割するようにすれば、このエラーは起きにくくなります。また、もしInnoDBファイルフォーマットがBarracudaではなくAntelopeになっているのであれば、できるならBarracudaに変更し、create tableする際は「row_format=dynamic」を付けると、こういった制限に引っかかりにくくなります。現在のInnoDBファイルフォーマットは以下で確認可能です。

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Antelope  |
+--------------------+-----------+

この辺りを疎かにしていると私のようにアホな目に合うので、手遅れにならない内に確認した方がいいでしょう。