2025-04-21

MySQLとMariaDBで接続collationが決定される方法の違い

mysql

MySQL 8.0がutf8mb4のデフォルトcollationを utf8mb4_0900_ai_ci に変更したことは有名です。 MariaDBも11.5からデフォルトcollationを変更したのですが、 utf8mb4_0900_ai_ci ではなく utf8mb4_uca1400_ai_ci になりました。

この2つのcollationについてはMySQLやMariaDBの識者に任せて、私はMySQLとMariaDBで接続のcollation (collation_connection) がどう設定されるかを調べてみました。

MySQL 8

MySQL 8.4のクライアントからサーバーに --default-character-set=utf8mb4 を指定して接続すると、 collation_connection は utf8mb4_0900_ai_ci になります。 utf8mb4のデフォルトcollationが utf8mb4_0900_ai_ci になっていて、クライアントは utf8mb4 のデフォルトcollationのcollation idをハンドシェイクパケットで送信しています。

$ docker run --network host -it mysql:8.4 mysql -h 127.0.0.1 --default-character-set=utf8mb4
...
Server version: 8.4.5 MySQL Community Server - GPL
...

mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4' AND ID<256;
+------------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME         | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+------------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_general_ci     | utf8mb4            |  45 |            | Yes         |       1 | PAD SPACE     |
| utf8mb4_bin            | utf8mb4            |  46 |            | Yes         |       1 | PAD SPACE     |
...
| utf8mb4_0900_ai_ci     | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
+------------------------+--------------------+-----+------------+-------------+---------+---------------+
27 rows in set (0.01 sec)

ただし、MySQL 8.4同士でも注意が必要なケースがあります。

デフォルトのcharsetがutf8mb4になっていても、 --default-charcter-set=utf8mb4 を指定しないと utf8mb4 にならないことがあります。たとえばmysql:8.4のコンテナのmysqlを使うとこうなります。

$ docker run --rm --network host -it mysql:8.4 mysql  -h 127.0.0.1
mysql> show variables like '%_connection';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
+--------------------------+-------------------+
2 rows in set (0.00 sec)

これはmysqlクライアントが、 --default-character-set が指定されていないときにlocaleからcharsetを選択するためです。例えば環境変数 LANG=C.UTF-8 を設定すると utf8mb4 が使われます。

$ docker run --rm --network host -it --env LANG=C.UTF-8 mysql:8.4 mysql  -h 127.0.0.1
...
mysql> show variables like '%_connection';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_connection | utf8mb4            |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
2 rows in set (0.01 sec)

もう一つ注意する必要があるのがビルド時のオプションです。Homebrewのmysql-clientは -DDEFAULT_COLLATION=utf8mb4_general_ci をつけてビルドされています。(mysqlではこのオプションは消されているのでmysql-clientでも消すPRを作っています。)

mysqlが選択したcharsetが、DEFAULT_COLLATIONのcharsetと一致している場合、charsetのデフォルトのcollationではなく DEFAULT_COLLATIONが使われます。

https://github.com/mysql/mysql-server/blob/ff05628a530696bc6851ba6540ac250c7a059aa7/sql-common/client.cc#L3954-L3958

そのためHomebrewのmysql-clientのmysqlコマンドを使うと utf8mb4_general_ci が使われます。utf8mb4のデフォルトcollationが変わっている訳では無いので、 SET NAMES utf8mb4 を実行すると utf8mb4_0900_ai_ci になります。

$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -h 127.0.0.1 -uroot --default-character-set=utf8mb4
...

mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.00 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.00 sec)

MariaDB

MariaDBは組み込みのutf8mb4のデフォルトcollationをMySQL 5.7時代の utf8mb4_general_ci にしたまま、追加の character_set_collations というシステム変数でutf8mb4のデフォルトcollationを変更しています。試しにこの変数を空にして information_schema.COLLATIONS を見てみると utf8mb4_general_ci がデフォルトであることが分かります。

# $ docker run --rm --name mariadb --network host --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:latest --character_set_collations=''

mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4' AND ID<256;
+------------------------------+--------------------+------+------------+-------------+---------+---------------------------------------+
| COLLATION_NAME               | CHARACTER_SET_NAME | ID   | IS_DEFAULT | IS_COMPILED | SORTLEN | COMMENT                               |
+------------------------------+--------------------+------+------------+-------------+---------+---------------------------------------+
| utf8mb4_general_ci           | utf8mb4            |   45 | Yes        | Yes         |       1 | UTF-8 Unicode                         |
| utf8mb4_bin                  | utf8mb4            |   46 |            | Yes         |       1 | UTF-8 Unicode                         |
| utf8mb4_unicode_ci           | utf8mb4            |  224 |            | Yes         |       8 |                                       |
...
+------------------------------+--------------------+------+------------+-------------+---------+---------------------------------------+
27 rows in set (0.00 sec)

今度はデフォルトの設定でMariaDBを起動してみると、 information_schema.COLLATIONS からデフォルトのcollationが消えました。 character_set_collations 変数を確認するとUnicode系のcharsetに対して *_uca1400_ai_ci をデフォルトのcollationとして設定していることが分かります。

# $ docker run --rm --name mariadb --network host --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:latest

mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4' AND IS_DEFAULT='Yes';
Empty set (0.00 sec)

mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb3' AND IS_DEFAULT='Yes';
Empty set (0.00 sec)

mysql> select @@character_set_collations;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| @@character_set_collations                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| utf8mb3=utf8mb3_uca1400_ai_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

character_set_collations システム変数は MariaDB 11.2.1 で追加されましたが、 uca1400_ai_ci をデフォルトにしたのは MariaDB 11.5 からです。

MariaDBで collation_connection が設定されるまでの流れは次のようになります。

mariadbクライアントはmysqlと違い DEFAULT_COLLATION によるcollationの上書きをしないので、 --default-character-set=utf8mb4 を指定すると utf8mb4_general_ci のID(45)をハンドシェイクパケットで送信します。
mariadbサーバーは、 utf8mb4_general_ci のIDを受け取ると、これが utf8mb4 のデフォルトのcollationであり、なおかつ character_set_collations に utf8mb4=utf8mb4_uca1400_ai_ci が設定されているので、 utf8mb4_uca1400_ai_ci を使います。

相互接続

--default-character-set=utf8mb4 を指定して接続するときの、MySQL/MariaDB間やバージョン間の挙動をまとめます。

MySQLへの接続

MySQLサーバーは(オプションで無効化されていなければ)ハンドシェイクパケットで指定されたcollationを優先します。

mariadbクライアントからMySQLに接続すると、ハンドシェイクパケットで指定された utf8mb4_general_ci が利用されます。これは MySQL 5.7 のクライアントから接続された場合も同じです。またHomebrew の mysql-client, mysql-client@8.4, mysql-client@8.0 なども、 -DDEFAULT_COLLATION=utf8mb4_general_ci をつけてビルドされているので utf8mb4_general_ci が利用されます。(近い将来に修正されるかもしれません。)

# $ docker run --rm --name mysql --network host -e MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:8.4

# mysql -> mysqld では utf8mb4_0900_ai_ci
$ /opt/homebrew/Cellar/mysql@8.4/8.4.5/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+

# mariadb -> mysqld では utf8mb4_general_ci.
# SET NAMES utf8mb4 で utf8mb4_0900_ai_ci になる
$ /opt/homebrew/Cellar/mariadb/11.7.2/bin/mariadb -uroot -h127.0.0.1 --default-character-set=utf8mb4
Server version: 8.4.5 MySQL Community Server - GPL

MySQL [(none)]> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.008 sec)

MySQL [(none)]> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.005 sec)

MySQL [(none)]> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.005 sec)


# Homebrewのmysql-client -> mysqld でも utf8mb4_general_ci
$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+

MariaDBへの接続

MariaDBサーバーはハンドシェイクパケットで utf8mb4_general_ci が指定されていると、 character_set_collations で指定されたcollation(デフォルトでは utf8mb4_uca1400_ai_ci)を利用します。ただし、ハンドシェイクパケットで指定されたcollationがそのcharsetのデフォルトcollationでなければ、 character_set_collations は無視されます。

MySQL 8.xのクライアントは、(ビルド時にDEFAULT_COLLATIONが指定されていなければ) utf8mb4のデフォルトcollationである utf8mb4_0900_ai_ci をハンドシェイクパケットで指定しますが、これはMariaDBでは utf8mb4 のデフォルトcollationではないので character_set_collations は無視され、そのまま collation_connection に設定されます。

# $ docker run --rm --name mariadb --network host --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:latest

# mariadb -> mariadb では utf8mb4_uca1400_ai_ci
$ /opt/homebrew/Cellar/mariadb/11.7.2/bin/mariadb -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_uca1400_ai_ci  |
+------------------------+


# mysql -> mariadb では utf8mb4_0900_ai_ci
$ /opt/homebrew/Cellar/mysql@8.4/8.4.5/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+

# SET NAMES utf8mb4 すると utf8mb4_uca1400_ai_ci になる。
$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_uca1400_ai_ci  |
+------------------------+
1 row in set (0.01 sec)


# Homebrewのmysql-client -> mariadb では utf8mb4_uca1400_ai_ci (近い将来修正される可能性あり)
$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_uca1400_ai_ci  |
+------------------------+

まとめ

collation_connection は実際に影響するケースは少ない変数ですが、だからこそ稀にハマった場合に原因がなかなかわからない事があります。

クライアント側では charset はエスケープ処理に関係するものの、collationは全く使いません。そのため、私がメンテナンスしている go-sql-drivers/mysql, mysqlclient, PyMySQL では、サーバーのデフォルトのcollationを優先するように、接続後に SET NAMES utf8mb4 を実行するようになっています。

ほかの接続ライブラリでも、例えばPHPのmysqliにある MYSQLI_INIT_COMMAND のように新規接続時に実行するコマンドを指定できる場合は、 SET NAMES utf8mb4 を実行することをお勧めします。

2025-03-24

PEP 781: Adding __type_checking__ constant を書いた

python

まだPR段階なので peps.python.org では表示されていません。Discussionはこちらです。

PEP 781: Adding __type_checking__ constant - PEPs - Discussions on Python.org

今までもtypingのインポートを避けるために from typing import TYPE_CHECKING の代わりに TYPE_CHECKING = False や if False: # TYPE_CHECKING のようなコードが書かれてきました。 typing.TYPE_CHECKING と同じ役割を持つ定数として __type_checking__ を追加することで、これらのワークアラウンドを不要にします。

また、 __type_checking__ は False と同じく本物の定数です。コンパイル時にコードを削除できます。

たとえば次のコードでは、実際に関数オブジェクトを4つ生成し、3つを overload のレジストリに追加しています。

# https://github.com/sqlalchemy/sqlalchemy/blob/dabd77992d785cad89ed110acd2f648a454fb7ae/lib/sqlalchemy/sql/elements.py#L133-L191

@overload
def literal(
    value: Any,
    type_: _TypeEngineArgument[_T],
    literal_execute: bool = False,
) -> BindParameter[_T]: ...


@overload
def literal(
    value: _T,
    type_: None = None,
    literal_execute: bool = False,
) -> BindParameter[_T]: ...


@overload
def literal(
    value: Any,
    type_: Optional[_TypeEngineArgument[Any]] = None,
    literal_execute: bool = False,
) -> BindParameter[Any]: ...


def literal(
    value: Any,
    type_: Optional[_TypeEngineArgument[Any]] = None,
    literal_execute: bool = False,
) -> BindParameter[Any]:
    r"""Return a literal clause, bound to a bind parameter.
    ...
    """
    return coercions.expect(
        roles.LiteralValueRole,
        value,
        type_=type_,
        literal_execute=literal_execute,
    )

overloadの定義部分を if TYPE_CHECKING: で囲えばオーバーロードのための関数オブジェクトの生成とレジストリへの追加を避けられますが、 if __type_checking__: で囲うことでその関数オブジェクトを生成するために必要なバイトコードも削って pyc ファイルを小さくできるので、WASMなどの環境でなるべくコードを小さくしたい場合でも積極的にtype hintをかけるようになります。

2025-03-01

ssh で Host / hostname をエイリアスに使いつつ Match host で設定する

兄弟のように同じような設定でログインするサーバーが複数台あるとする。 Host *.myproj.example.com でまとめて設定できるものの、 ssh コマンドでホスト名を指定するのが面倒だ。

その場合、Host と hostname で短いエイリアスを作ることができるが、そうすると Host *.myproj.example.com の設定は適用されなくなる。 Host がマッチングするのは本当のホスト名ではなく、コマンドで指定されたエイリアスの方だからだ。

Host エイリアスのパターン でもいけるが、今度はエイリアスじゃないホスト名でログインする場合に設定が適用されなくなる。 Match host を使うと本当のホスト名でマッチできるので、 Host より使いやすい。

# エイリアスの設定
Host isu1
  hostname isu1.myproj.example.com

Host isu2
  hostname isu2.myproj.example.com

Host isu3
  hostname isu3.myproj.example.com

# エイリアスに効かない
Host *.myproj.example.com
  User ubuntu

# エイリアスを使った時しか効かない
Host isu?
  User ubuntu

# エイリアスと本当のホスト名どっちを ssh に指定しても効くように両方のパターンを書く
Host isu? *.myproj.example.com
  User ubuntu

# Match host を使えば本当のホスト名のパターンを書くだけでエイリアスにも効く
Match host *.myproj.example.com
  User ubuntu

2025-01-14

Python 3.13 + PyMySQL で MySQL にSSL接続できない

python mysql

Python 3.13 を PyMySQL のCIに追加したら、次のようなエラーが発生した。

"Can't connect to MySQL server on '127.0.0.1' ([SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Missing Authority Key Identifier (_ssl.c:1018))"

調べてみると、Python 3.13からsslモジュールがデフォルトで利用するフラグが更新されてていた。

Security improvements:

ssl.create_default_context() sets ssl.VERIFY_X509_PARTIAL_CHAIN and ssl.VERIFY_X509_STRICT as default flags.

What’s New In Python 3.13 — Python 3.13.1 documentation

MySQLの証明書は、MySQLが初回起動時に自動で生成する自己署名証明書をそのまま使っている。そこで作られる ca.pem に Authority Key Identifier が欠けていて、それを VERIFY_X509_STRICT が許さないようだ。

github.com

とりあえずPyMySQLでは当面 VERIFY_X509_STRICT を無効にしようと思う。MySQLがデフォルトでこれに対応した証明書を作ってくれるようになるまで、ユーザーにいちいち自己署名証明書を作る方法を教えるのは面倒だ。

2025-01-10

setuptoolsで作ったwheelをPyPIにアップロードできない

mysqlclientの新バージョンをリリースするために uv publish *.whl したら、次のようなエラーが発生した。

Uploading mysqlclient-2.2.7-cp310-cp310-win_amd64.whl (202.9KiB)
error: Failed to publish `mysqlclient-2.2.7-cp310-cp310-win_amd64.whl` to https://upload.pypi.org/legacy/
  Caused by: Upload failed with status code 400 Bad Request. Server says: 400 license-file introduced in metadata version 2.4, not 2.2. See https://packaging.python.org/specifications/core-metadata for more information.

setuptools のバグで、出力しているメタデータのバージョンが古いのに、より新しいバージョンのメタデータで定義されている license-file を書いてしまっているらしい。それをPyPIが厳密にチェックするようになったのでアップロードできなくなったようだ。

github.com

twineがlicense-fileを消すworkaroundを実装してくれているので、 uv publish の代わりに uvx twine upload を使えばアップロードできた。