PHP5.6とMySQL8で「Server sent charset (255) unknown to the client.」ハマった…

開発で使っている環境php5.6.4＋MySQL5.6.43からMySQLだけバージョンアップしようとした時の備忘録。

文字コード関連でかなりハマりました。

実証環境
経緯
テストDB
PHP5.6からMySQL8に接続
MySQLの文字コード確認
Windows10のmy.ini
my.iniを作成
my.iniが複数存在
Windows10でMySQL8の文字コード設定
まとめ

実証環境

Windows10
Apache2.4.10
PHP5.6.4
MySQL8.0.15
(旧)MySQL5.6.43

PHPはmod_phpでApacheと連携、MySQLにはPDOで接続しています。

経緯

開発環境で使用していたphp5.6.4＋MySQL5.6.43をMySQL8に移行しようとしました。

MySQL8.0.15をインストールして、既存のMySQL5.6.4から必要なDBだけdumpして移行する予定。

MySQL8.0.15をインストール後にphp5.6.4から接続確認をする時にハマりました。

テストDB

MySQL8.0.15をインストール後に下記DBとTABLEを作成。

テスト用のDB

mysql> CREATE DATABASE test;

テスト用のTABLE

mysql> CREATE TABLE sample(id int,name text);

テストデータ

mysql> INSERT INTO sample VALUES(1,"first");
mysql> INSERT INTO sample VALUES(2,"名前");

PHP5.6からMySQL8に接続

PHP5.6.43からMySQL8.0.15にPDOで接続すると下記のエラーが出て接続できない。

Warning: PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers in ***.

まぁ、サクッといかないだろうとは予想はしていた。

PHP5.6はかなり昔のバージョンでサポートも切れているから、最新のMySQL8とは相性が悪いのかも。

ただ、エラー内容が「サーバーから不明なcharsetが送られてきた」なので接続はできている気がする。

PDO以外の方法でMySQLと接続を確認してみた。

・mysqli

Warning: mysqli::mysqli(): Server sent charset (255) unknown to the client.
Warning: mysqli::mysqli(): (HY000/2054): Server sent charset unknown to the client.

・mysql_connect

Warning: mysql_connect(): Server sent charset (255) unknown to the client.
Warning: mysql_connect(): Server sent charset unknown to the client.

同じだ、どのコネクタを使用しても「不明なcharaset」でエラーとなっている。

これはPHPのバージョンアップが必要なのか？？

MySQLの文字コード確認

エラー内容が「サーバーから不明なcharsetが送られてきた」なのでMySQL8.0.15の設定の可能性もある。

とりあえずMySQL8.0.15の文字コード関連を調査。

mysql> SHOW VARIABLES like "char%";
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | cp932   |
| character_set_connection | cp932   |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | cp932   |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+

mysql> SHOW VARIABLES like "coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | cp932_japanese_ci  |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

むむっ、これはなんだ！？

character_set_databaseなどのMySQLのデフォルト文字コードが「utf8mb4」になっているではないか！？

そういえばインストール後に文字コード設定するの忘れていたが、MySQLってデフォルト「latin1」では？

まぁ、デフォルト文字コードが何であれ文字化けするだけで接続できないわけではないと思うが。

PHP5.6はバージョン古いから4バイトUTF8の「utf8mb4」を知らないのか？？

MySQL公式サイトで確認

MySQLの公式サイトのリリースノートを確認すると。

「The default character set has changed from latin1 to utf8mb4.」

MySQL8.0.1からデフォルトの文字コードを「latin1」から「utf8mb4」に変更したってある。

character_set_serverとcharacter_set_databaseを「utf8mb4」。

collation_serverとcollation_databaseを「utf8mb4_0900_ai_ci」。

前のデフォルトに戻す

MySQL8.0.1以前のデフォルト状態に戻すには設定を追加しろって書いてある。

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

もうこれはPHP5.6がutf8mb4を知らなって線で調査する。

Windows10のmy.ini

MySQL8.0.1のデフォルト文字コードを変更するため「my.ini」を編集する。

余談だが、「.cnf」はLinux系OS、Windowsの設定ファイルは「.ini」が多い。

mysqlのhelpでどの場所のmy.iniを参照しているかを確認。

bin>mysql --help | findstr cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
C:\WINDOWS\my.ini
C:\WINDOWS\my.cnf
C:\my.ini
C:\my.cnf
D:\Program Files\MySQL\MySQL Server 8.0\my.ini
D:\Program Files\MySQL\MySQL Server 8.0\my.cnf

今時のWindowsでC直下やWINDOWSフォルダにファイルを置く人は少ない、いちいち警告が出てでうざいから。

MySQLのインストールフォルダに「my.ini(my.cnf)」を置けばいいようだ。

※これが後に大きな罠になる…

my.iniを作成

とりあえずMySQLのインストールフォルダにmy.iniを作成。

MySQLのサイトにあったデフォルト変更の設定を書き込む。

私はMySQLを「D:\Program Files\MySQL\MySQL Server 8.0\」にインストールしている。

D:\Program Files\MySQL\MySQL Server 8.0>more my.ini

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

そしてMySQLを再起動をPHP5.6から接続確認。

Warning: PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers in ***.

あれ、予想外れた？デフォルトに戻せばつながると思ったのに。

MySQLの文字コードを確認。

mysql> SHOW VARIABLES like "char%";
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | cp932   |
| character_set_connection | cp932   |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | cp932   |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+

utf8mb4から変わってねーじゃねーか！！公式サイトめ嘘つきやがったな！！

my.iniが読み込まれていない可能性

僕の設定が悪い可能性もあるので、my.iniに他の設定を追加。

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

再起動後にMySQLの文字コードを確認。

mysql> SHOW VARIABLES like "char%";
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8    |
| character_set_connection | utf8    |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8    |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+

喧嘩売ってんのかコノヤロー！！！

character_set_databaseとcharacter_set_serverは変わらず。

他はutf8になっているのでmy.iniの場所が悪いわけではない。

変更できないのか？デフォルトはutf8mb4とずぶずぶな関係なのか？？

ここから二日くらいめんどくさくて移行を放棄した。

my.iniが複数存在

二日経ったある日、ふと「my.iniが上書きされているんじゃないか？」と思って調べた。

bin>mysql --help | findstr cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
C:\WINDOWS\my.ini
C:\WINDOWS\my.cnf
C:\my.ini
C:\my.cnf
D:\Program Files\MySQL\MySQL Server 8.0\my.ini
D:\Program Files\MySQL\MySQL Server 8.0\my.cnf

このどこかにmy.iniやmy.cnfが存在して上書き読み込まれているのではないのか。

しかし、自分で作成した「D:\Program Files\MySQL\MySQL Server 8.0\my.ini」以外は存在しない。

お手上げだ、デフォルトはutf8mb4から変更できないしPHPから接続できないし。

PHP5.6バージョンアップも考えたが、既にあるシステムをPHP7に対応させるのが面倒だ。

他に設定ファイルがある可能性は…

Windows10のサービスからMySQL8.0.15を起動しているので起動オプションを確認した。

D:\Program Files>sc qc mysql80
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: mysql80
        TYPE               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 2   AUTO_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : "D:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="D:\ProgramData\MySQL\MySQL Server 8.0\my.ini" MySQL80
        LOAD_ORDER_GROUP   :
        TAG                : 0
        DISPLAY_NAME       : MySQL80
        DEPENDENCIES       :
        SERVICE_START_NAME : NT AUTHORITY\NetworkService

なんだと、「BINARY_PATH_NAME」の起動オプションに「–defaults-file」でmy.iniが指定されているではないか。

しかも「D:\ProgramData\MySQL\MySQL Server 8.0\my.ini」ってmysqlのhelpにはなかった場所だぞ！？

MySQLのデータが保存されているD:\ProgramData\MySQL\MySQL Server 8.0を調べたら「my.ini」があった。

しかもいろいろ書かれている。これか、ここに文字コードを設定すればいいのか。

自分で作成したmy.iniは削除して、D:\ProgramData\MySQL\MySQL Server 8.0の「my.ini」に設定を追加。

※[mysql]や[mysqld]のセクションは存在しているのでそこに追記。

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

MySQL8を再起動して文字コード確認。

mysql> SHOW VARIABLES like "char%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | cp932 |
| character_set_connection | cp932 |
| character_set_database   | latin1|
| character_set_filesystem | binary|
| character_set_results    | cp932 |
| character_set_server     | latin1|
| character_set_system     | utf8  |
+--------------------------+-------+

変わった！character_set_databaseやcharacter_set_serverがlatin1になった！！

デフォルトの文字コードを変更することに成功したが、character_set_clientなどがcp932でutf8になっていない。

なぜだ、D:\Program Files\MySQL\MySQL Server 8.0\my.iniに書き込んだ時には設定できたのに。

ただ、この時点でPHP5.6からPDOで接続すると成功し、test.sampleのデータをSELECTできた。

array(4) {
  ["id"]=>
  string(1) "1"
  ["name"]=>
  string(5) "first"
}
array(4) {
  ["id"]=>
  string(1) "2"
  ["name"]=>
  string(6) "名前"
}

やっぱりPHP5.6からMySQL8の「utf8mb4」には接続できないようだ。

Windows10でMySQL8の文字コード設定

今までの調査から、my.iniが2つ必要なことがわかった。

D:\Program Files\MySQL\MySQL Server 8.0\my.ini
D:\ProgramData\MySQL\MySQL Server 8.0\my.ini

インストールディレクトリmy.ini

D:\Program Files\MySQL\MySQL Server 8.0\my.iniには下記の設定

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

データディレクトリmy.ini

D:\ProgramData\MySQL\MySQL Server 8.0\my.iniには下記の設定

[mysqld]
character_set_server=utf8
collation_server=utf8_general_ci

MySQL8の文字コード確認

mysql> SHOW VARIABLES like "char%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | utf8  |
| character_set_connection | utf8  |
| character_set_database   | utf8  |
| character_set_filesystem | binary|
| character_set_results    | utf8  |
| character_set_server     | utf8  |
| character_set_system     | utf8  |
+--------------------------+-------+

きたーー！すべてutf8に変更できた。

PHP5.6.4からMySQL8.0.15にPDOで接続できました。

mysqliやmysql_connectでも接続可能。

PHPのバージョンアップ必要なかったですね。

まとめ

MySQL8.0.1からデフォルト文字コードはutf8mb4
PHP5.6.4からMySQL8.0.15へはutf8mb4では接続できない
Windows10のMySQL8の文字コードを変更するにはmy.iniは2つ必要

ネットでもあまり情報が出回っておらず、1つ1つ潰して調査しました。

WINDOWS10にMySQL8入れて使う人が少ないのかもしれません。

PHPのバージョンも古いですし。

ただ、このままだとPHP5.6からutf8mb4を使用できないことになります。

なんかしっくりきませんが、MySQL8の文字コードを「latin1」や「utf8」に変更したら接続できたので事実なのかと。

暇なときにPHPのバージョンを上げてutf8mb4をテストしてみますか。

DB移行も必要だけど、パトラッシュ僕はもう疲れたよ…