🕵️‍♂️

なぜか日本語が文字化けするmysqlの話

2022/11/20に公開

問題発生

DBにmysql5.7を使ってウェブアプリを開発している現場がありました。
ローカルの開発環境をdocker-composeを使って作成しています。
アプリ上ではちゃんと日本語が表示されています。
しかし！ターミナルからmysqlに接続してSELECTしてみると日本語が「???」になってしまっています！！

bash-4.2# mysql -u root -p -e "select id, department from test_db.user where id = 1;"
Enter password: 
+----+------------+
| id | department |
+----+------------+
|  1 | ???        |
+----+------------+

どうにかしてこの問題を解決してみましょう。

現状把握

現在の設定を確認してみましょう。
この段階では関係ありそうな情報を集めていきます。

現状①

手始めに、mysqlの文字コードの設定を見てみましょう。

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.09 sec)

ふむふむ。
utf8mb4になっている部分とlatin1になっている部分が混在しているようです。

現状②

docker-compose.ymlでどう設定されているのか確認してみましょう。

docker-compose.yml

version: '3'
services:
   (略)
    mysql:
        # image: mysql:5.7
        image: mysql/mysql-server:5.7
        container_name: mysql-db
        hostname: mysql-db
        restart: always
        environment:
            TZ: 'Asia/Tokyo'
        command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        volumes:
            - ./mysql57/initdb:/docker-entrypoint-initdb.d
            - ./mysql57/my.cnf:/etc/mysql/conf.d/my.cnf
            - ./mysql57/mysql_data:/var/lib/mysql
        ports:
            - "3306:3306"
        networks:
            - app-network
networks:
    app-network:
        driver: bridge

mysql:5.7のイメージがコメントアウトされ、mysql/mysql-server:5.7になっています。
消し忘れでしょうか？

commandで文字コードの設定をしてますね。なるほど。

my.cnfファイルも使っているようなので見てみましょう。

my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
defaul-password-lifetime=0

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

ここでも文字コードの設定をしていそうです。
あれれ？commandの設定と被っているように見えますね。あやしさ満点。

現状③

docker-compose.ymlの作成者に雑談がてら聞き込み調査してみます。

🕵️ 「mysqlのイメージってもともとmysql:5.7使ってましたか？」

🧑 「使ってました。しかし会社支給のパソコンがM1のMacになってから、エラーになってしまったのでmysql/mysql-server:5.7に変更しました。」

ほうほう。

🕵️ 「mysqlってずっと文字化けしているんですか？」
🧑 「Windows使ってるときは日本語で表示できていたんです。」

なるほど。

つまり、

① 元々Windowsでmysql:5.7のイメージを使っており、そのときはなんの問題もなかった。
② Macになってからもともとのdocker-compose.ymlではうまく動かなかったのでイメージを変更してなんとか動いた。

ということのようです。
重要な証言ですね。

原因分析

現状がわかったところで、なぜ日本語が表示されないのかを考えます。
ここではmysqlの知識が必要になりそうです。

サーバーシステム変数

現状①で文字コードの設定を見てみました。
この設定をサーバーシステム変数といいます。
文字コードに関するサーバーシステム変数が、それぞれが何を表しているのか知っておきましょう。

latin1になっていた設定が何だったのか確認してみます。

character_set_client

クライアントから到達するステートメントの文字セット。この変数のセッション値は、クライアントがサーバーに接続するときにクライアントによってリクエストされる文字セットを使用して設定されます。

character_set_connection

文字セットイントロデューサを持たないリテラル用、および数字から文字列への変換用に使用される文字セット。

character_set_results

結果セットやエラーメッセージなどのクエリー結果をクライアントに返すために使用される文字セット。

クライアントというのは、クライアントプログラムのことですね。

MySQL Database Software は、さまざまなバックエンドをサポートするマルチスレッド形式の SQL Server、複数の異なるクライアントプログラムおよびライブラリ、管理ツール、幅広いアプリケーションプログラミングインタフェース(API)から成るクライアント/サーバーシステムです。

クライアントとサーバーのやり取りをデフォルト値であるlatin1で行っているんですね。
　
表示を日本語するにはcharacter_set_resultsをutf8mb4にしたらいいのだと理解できます。

次はutf8mb4になっていた設定です。

character_set_database

デフォルトデータベースで使用される文字セット。デフォルトのデータベースが変更されるたびに、サーバーはこの変数を設定します。デフォルトデータベースが存在しない場合、変数は character_set_server と同じ値になります。

character_set_server

サーバーのデフォルト文字セット。

デフォルトデータベースとはUSE database;で設定されるデータベースのことです。
これらはmysqldコマンドの設定が生きているのでしょうか。

mysqlのオプション設定方法

サーバーシステム変数をどう設定するのでしょうか。

各システム変数にはデフォルト値があります。システム変数は、コマンド行のオプションを使用するか、オプションファイルでサーバー起動時に設定できます。これらのほとんどは、SET ステートメントを使用してサーバーの実行中に動的に変更でき、これによりサーバーを停止して再起動することなくサーバーの動作を変更できます。システム変数値を式によって参照できます。

つまり、以下の３つの方法で設定できるようです。
① コマンドで指定

mysqld --query_cache_size=16M --max_allowed_packet=1G

② オプションファイルで指定

[mysqld]
query_cache_size=16M
max_allowed_packet=1G

③　SETステートメントを使用

SET sort_buffer_size=10000;
SET @@LOCAL.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@GLOBAL.sort_buffer_size=1000000, @@LOCAL.sort_buffer_size=1000000;

アドホックに対応するのであれば、SETステートメントで良さそうです。
開発メンバーみんなの環境をそろえたいし、毎回日本語が表示されてほしいので、オプションファイルを使いたいですね！

では、my.cnfにどんな値を設定してたらいいのでしょうか。
my.cnfに設定できる値はコマンドに指定できるオプションのみです。
サーバーシステム変数をそのまま記述できるわけではありません。
したがって、サーバーシステム変数に対応するオプションを記載します。
角括弧[]で囲まれているのはオプショングループです。
mysqldコマンドで実行するオプションは[mysqld]に、mysqlコマンドで実行するオプションは[mysql]に記載します。
[client]は複数のオプショングループ(プログラム)を表します。

もとのmy.cnfにはdefaul-password-lifetimeがありました。
これはパスワードの有効期限を設定するオプションのようです。
しかしこのオプションはmysql5.7.4以降に導入されたオプションです。
ここで思い出してほしいのが、mysqlのimageです。
mysql:5.7のイメージmysql5.7.4です。
しかし! mysql/mysql-server:5.7のイメージはmysql5.7.3なのです!!
mysql/mysql-server:5.7のイメージを引き続き使うために、不要なオプションは消しておきます。

docker-compose.ymlの設定

現状②でdocker-compose.ymlを確認しました。
mysqldコマンドとmy.cnfファイル(オプションファイル)が指定されていましたね。
先程の、mysqlのオプション設定方法で確認しましたが、mysqldとmy.cnfのどちらかでよさそうです。

オプションファイルはmysqlサーバー上に配置しますが、グローバルオプションとして読み込ませるには、
/etc/mysql/my.cnfまたは/etc/mysql/my.cnfあたりに配置するとよさそうです。

docker-compose.ymlでは/etc/mysql/conf.d/my.cnfにマウントされていましたね。
設定が間違っているようです。正しく修正しましょう。

計画 & 実行

ここまで調査して、各所でミスしていることが明らかになりました。
docker-compose.ymlとmy.cnfを修正すればよさそうなので、すぐにでも実行できそうです。

ではさっそく、docker-compose.ymlとmy.cnfを修正しましょう。

docker-compose.yml

version: '3'
services:
   (略)
    mysql:
        image: mysql/mysql-server:5.7
        container_name: mysql-db
        hostname: mysql-db
        restart: always
        environment:
            TZ: 'Asia/Tokyo'
        volumes:
            - ./mysql57/initdb:/docker-entrypoint-initdb.d
            - ./mysql57/my.cnf:/etc/mysql/my.cnf
            - ./mysql57/mysql_data:/var/lib/mysql
        ports:
            - "3306:3306"
        networks:
            - app-network
networks:
    app-network:
        driver: bridge

my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

修正したdocker-compose.ymlを使ってコンテナを起動してみます。

docker-compose up -d

では、コンテナに入ります

docker　exec -it mysql-db /bin/bash

クエリを実行してみす。ドキドキ。

bash-4.2# mysql -u root -p -e "SHOW VARIABLES like '%char%';"
Enter password: 
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

おおー！latin1が見事に消え去っています！！！
SELECT文も実行してみましょう。

bash-4.2# mysql -u root -p -e "select id, department from test_db.user where id = 1;"
Enter password: 
+----+------------+
| id | department |
+----+------------+
|  1 | 開発部     |
+----+------------+

完ぺきです🙌

さいごに

巷のブログのみで作られたいびつな環境を、公式サイトも参考にすることで無事まともな環境に大変身しました。
この記事が、誰かの参考になりますように。

こちらの記事の問題解決能力の手順を勝手にに参考にさせていただきました。