リストアしたデータベースがちょっとだけ文字化けする問題
さくらインターネットのスタンダードプランで運用しているWordpressのDBバックアップデータを、VirtualBoxで作った検証用Webサーバーにリストアして使っていたのだが、一見ちゃんとリストアできているように見えて実はちょっとだけ文字化けしていることが発覚した。全部化けていてくれたらぱっと見で分かったのだけれど、ほんの少しだけ文字化けしていたので気づかなかった。通常の文字部分は普通に判読可能だったのが、”～”が”？”になってしまっている。これは間違いなくあれだ。どこかとどこかの文字コードが一致してないからだ。全てUTF-8で統一していたつもりだった。よって、Webサーバー、PHP、データベースのどこかにUTF-8でない文字コードがセットされていたらそいつが犯人だ。それぞれの環境のそれぞれのソフトウェアで設定されている文字コードを確認して犯人の特定を行う必要がある。
テーブルスペースの文字コード確認
本番環境でうまくいくDBのバックアップを検証環境にもってきてうまくいかない場合、検証環境に問題があると考えるのが普通だ。でも今回はリストア先となる検証環境はすべてUTF-8で統一されていて問題は見つからなかった。今一度バックアップ取得側の環境を確認してみる。まず、“SHOW TABLE STATUS”を使ってワードプレスで使われているDBのテーブルにちゃんとUTF-8が指定されているか確認する。
% mysql -u DBユーザー名 -p -h DBサーバーのIP
Enter password:
mysql>
mysql> show table status like "wp%";
+-----------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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_commentmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2017-08-20 04:03:00 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_comments | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 81920 | 0 | 1 | 2017-08-20 04:03:01 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_links | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 16384 | 0 | 8 | 2017-08-20 04:03:01 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_options | InnoDB | 10 | Compact | 355842 | 87 | 30998528 | 0 | 35307520 | 5242880 | 597712 | 2017-08-20 04:03:10 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_postmeta | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 32768 | 0 | 203126 | 2017-08-20 04:03:10 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_posts | InnoDB | 10 | Compact | 11831 | 46005 | 544292864 | 0 | 14729216 | 8388608 | 1131279 | 2017-08-20 04:03:16 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_term_relationships | InnoDB | 10 | Compact | 316404 | 44 | 14204928 | 0 | 8929280 | 4194304 | NULL | 2017-08-20 04:03:18 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_term_taxonomy | InnoDB | 10 | Compact | 3398 | 77 | 262144 | 0 | 245760 | 0 | 3470 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_termmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_terms | InnoDB | 10 | Compact | 3535 | 115 | 409600 | 0 | 475136 | 0 | 3453 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_usermeta | InnoDB | 10 | Compact | 31 | 528 | 16384 | 0 | 32768 | 0 | 34 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_users | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 49152 | 0 | 2 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
+-----------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
12 rows in set (0.01 sec)
mysql>
mysql>
すごくたくさん情報が出た。その中からCollationカラムを見ると、ちゃんと”utf8mb4_unicode_ci”になっているのが確認できる。ちなみに“SHOW TABLE STATUS”では出力が横長になりすぎて折り返してしまって見にくいが、”information_schema.TABLES”に対してSELECT文を投げても同じ情報が引き出せる。“SHOW TABLE STATUS”と同じ出力を出すSELECT文は以下の通り。
mysql>
mysql> SELECT TABLE_NAME as Name, Engine, Version, Row_format, TABLE_ROWS as Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, TABLE_COLLATION as Collation, Checksum, Create_options, TABLE_COMMENT as Comment from information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();
+-----------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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_commentmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2017-08-20 04:03:00 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_comments | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 81920 | 0 | 1 | 2017-08-20 04:03:01 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_links | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 16384 | 0 | 8 | 2017-08-20 04:03:01 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_options | InnoDB | 10 | Compact | 354552 | 87 | 30998528 | 0 | 35307520 | 5242880 | 597722 | 2017-08-20 04:03:10 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_postmeta | InnoDB | 10 | Compact | 22 | 744 | 16384 | 0 | 32768 | 0 | 203147 | 2017-08-20 04:03:10 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_posts | InnoDB | 10 | Compact | 21348 | 25496 | 544292864 | 0 | 14729216 | 8388608 | 1131300 | 2017-08-20 04:03:16 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_term_relationships | InnoDB | 10 | Compact | 316404 | 44 | 14204928 | 0 | 8929280 | 4194304 | NULL | 2017-08-20 04:03:18 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_term_taxonomy | InnoDB | 10 | Compact | 2794 | 93 | 262144 | 0 | 245760 | 0 | 3470 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_termmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_terms | InnoDB | 10 | Compact | 3610 | 113 | 409600 | 0 | 475136 | 0 | 3453 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_usermeta | InnoDB | 10 | Compact | 31 | 528 | 16384 | 0 | 32768 | 0 | 34 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_users | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 49152 | 0 | 2 | 2017-08-20 04:03:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
+-----------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
12 rows in set (0.02 sec)
mysql>
テーブル名と文字コードだけ出すとこんなにすっきり！
mysql>
mysql> SELECT TABLE_NAME as Name, TABLE_COLLATION as Collation from information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();
+-----------------------+--------------------+
| Name | Collation |
+-----------------------+--------------------+
| wp_commentmeta | utf8mb4_unicode_ci |
| wp_comments | utf8mb4_unicode_ci |
| wp_links | utf8mb4_unicode_ci |
| wp_options | utf8mb4_unicode_ci |
| wp_postmeta | utf8mb4_unicode_ci |
| wp_posts | utf8mb4_unicode_ci |
| wp_term_relationships | utf8mb4_unicode_ci |
| wp_term_taxonomy | utf8mb4_unicode_ci |
| wp_termmeta | utf8mb4_unicode_ci |
| wp_terms | utf8mb4_unicode_ci |
| wp_usermeta | utf8mb4_unicode_ci |
| wp_users | utf8mb4_unicode_ci |
+-----------------------+--------------------+
12 rows in set (0.00 sec)
mysql>
テーブルの文字セットが”utf8mb4_unicode_ci”になっているので、WordPressの設定ファイルwp-config.phpの”DB_CHARSET”の項目を”utf8mb4″にしておくと、絵文字が使える。”utf8″だと絵文字を使った場合にエラーが出たり文字が化けたりするので要注意だ。
クライアント側の文字コード確認
テーブルスペースに問題がないことが確認できたので続いて、“SHOW VALIABLES”を使ってMySQL システム変数の設定値を確認してみる。
mysql>
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql>
おわかりいただけただろうか。見事にujisが並んでいる。ujis、つまりUNIX化拡張JIS、またの名をEUC-JPである。さくらインターネットのレンタルサーバーはシェル環境がデフォルトではEUC-JP(ujis)になっているのだ。それに合わせてMySQLサーバーもujisになっている。デフォルトはujisだが、WordPressを”utf8mb4″に設定してインストールしたので、WordPressで使うDBのテーブル作成時には”utf8mb4_unicode_ci”になっており、php.iniで”default_charset = UTF-8″を設定しているので、WordPressの世界はUTF-8に統一されている。そこに接続しに行くクライアント環境がujisのままだったので文字化けを起こしてしまったものと思われる。
WordPressとPHPとテーブルスペースはUTF-8で統一されているが、そこにシェルから接続したクライアント環境はujisのままだった。これを修正できればよさそうだ。自分で建てたサーバーなら/etc/my.cnfを編集して、[client]欄に”default-character-set = utf8mb4″を設定すれば一発なのだが、間借りしているレンタルサーバーでは、そうはいかない。自分の借りているユーザー環境だけ設定するには、ユーザーディレクトリの直下に”.my.cnf”ファイルを作成し、そこに書き込んでおくとユーザーごとにクライアントの設定を変更することができる。
%
% cd ~/
%
% vi .my.cnf[client]
default-character-set = utf8mb4 %
上記のように”.my.cnf”を作成した後にもう一度、接続しなおして“SHOW VALIABLES”で確認してみる。
mysql>
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql>
これで必要なところを”utf8mb4″にできた。”character_set_database”と”character_set_server”はサーバー側の設定なので間借りしているユーザーからは変更できないが、テーブルがちゃんと”utf8mb4″で作成されているので問題ないはず。これで、コマンドラインから取得したり、シェルスクリプトから取得したダンプでも、リストア後に文字化はなくなった。長々と書いたが、結局のところEUC-JPとUTF-8の間の変換で変換しきれない文字があるらしいので、UTF-8にそろえちゃえば問題ないよね！って話でした。めでたしめでたし。
