2018年11月12日月曜日

mysql_native_password, caching_sha2_password の速度測り比べ

TL;DR


  • まずは mysql_native_password, caching_sha2_password, sha256_password それぞれを指定したアカウントを作ります。
CREATE USER yoku0825_native_password IDENTIFIED WITH mysql_native_password BY 'a';
CREATE USER yoku0825_caching_sha2 IDENTIFIED WITH caching_sha2_password BY 'a';
CREATE USER yoku0825_sha256 IDENTIFIED WITH sha256_password BY 'a';
  • 別のマシンから1000回接続してquitするのにかかった時間の平均値を出します。それを5回繰り返して最大値と最小値を捨て、残った3回分の平均を取ります。
$ for n in $(seq 1 5) ; do time for m in $(seq 1 1000) ; do  MYSQL_PWD="a" mysql -h 172.31.32.165 -uyoku0825_native_password -e "quit"; done ; done
接続オプション mysql_native_password caching_sha2_password sha256
非SSL 7.8ms 7.5ms x
SSL 34.8ms 35.0ms 38.1ms
非SSL + サーバーの公開鍵 7.6ms 7.4ms 11.2ms
値はそこまでアテにしないとして、傾向としては
  • mysql_native_passwordcaching_sha2_password はそこまで大きな違いはない(個人によって感想は異なります)
  • やはり暗号化しないと速い
  • 非SSLで sha256_password を使うとかなりオーバーヘッドが見える
リモートから公開鍵を指定する MYSQL_SERVER_PUBLIC_KEY は少なくともMySQL 8.0のConnector/Cには mysql_options に実装されてるけど、それ以外のコネクターでは使えるのかどうかわからない。
と考えると、平文の速さが欲しければ mysql_native_password が安牌、SSLに切り替えるよって場合は caching_sha2_password でいいやってことになるかしらん。
【2018/11/14 15:22】
ご指摘をいただいたので追試。 @methane = サンありがとうございます。




mysqld はグリーティングの時に default_authentication_plugin のつもりでソルトを送り、そのプラグインを使っていないユーザーを指定された場合はそのプラグイン用のソルトを送りなおす = AuthSwitchRequest の1往復分
以下、全てSSLの場合。
caching_sha2 & default: caching_sha2 -> 34.6646666666667
caching_sha2 & default: native -> 35.148
native & default: caching_sha2 ->35.1036666666667
native & default: native -> 35.0216666666667

2018年11月8日木曜日

MySQL 5.5とそれ以前の古いDATETIME型が残っているとMySQL 8.0が起動できない

mysqldが起動できないバグなのか、「 avoid_temporal_upgrade なんてことすると将来的にサポート対象外だよ」がドキュメントに記載されてないのかどっちだか迷っている。

MySQL 5.6からDATETIME型のデータ構造が新しくなった。
MySQL 5.5とそれ以前で CREATE TABLE したテーブルは8バイトの旧DATETIME型、MySQL 5.6とそれ以降で CREATE TABLE したテーブルは5バイトの新DATETIME型で、SQL上の扱いには互換性があり、これらを区別することはあんまりできない(切り替えるオプションもない)
CREATE TABLE した時のバージョンで決まるので、 mysqldump を取ってリストアした時はリストアした時のバージョンのDATETIME型を使う。
MySQL 5.5で CREATE TABLE しても、ダンプを取って5.6にリストアすると5.6の中で CREATE TABLE されるので、リストア後のデータは新DATETIME型になる。
なので、表題のようなことは「MySQL 5.5またはそれ以前からインプレースアップグレードのみで8.0に到達しようとしているインスタンス」でしか起こらない。
更に、MySQL 5.7の mysql_upgrade の中では、旧DATETIME型を新DATETIME型に書き換える処理が入っているので、これを素直にやっている場合も表題のケースには到達しない。
MySQL 5.7にインプレースアップグレードした時、 mysql_upgrade でこの新旧DATETIMEの変換処理をスキップさせている場合にタイトルのケースに引っ掛かる。
思い付く限りでは、 mysql_upgrade --upgrade-system-tables ( mysql, performance_schema, sys あたりのみアップグレードし、ユーザーが作ったテーブルには触らない)もしくは mysqldavoid_temporal_upgrade オプションを有効にしている場合、くらいか。
エラーログに大した情報もなくいきなり「データディクショナリー(= DD)への引っ越しに失敗した。大佐、中断する( ーдー´)」と言われてmysqldが起動しないので、Table upgrade required. Please do “REPAIR TABLE ..” or dump/reload to fix it!` もへったくれもないのである。
2018-11-06T07:58:54.851647Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.13/bin/mysqld (mysqld 8.0.13) starting as process 1585
2018-11-06T07:58:58.491844Z 2 [ERROR] [MY-010923] [Server] Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
2018-11-06T07:58:59.067735Z 2 [Warning] [MY-010772] [Server] db.opt file not found for test database. Using default Character set.
2018-11-06T07:58:59.424516Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2018-11-06T07:58:59.424562Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-11-06T07:59:00.712682Z 0 [System] [MY-010910] [Server] /usr/mysql/8.0.13/bin/mysqld: Shutdown complete (mysqld 8.0.13)  Source distribution.
mysqld には —no-dd-upgrade なんていう如何にもこれを回避できそうなオプションがあるんだけど、これは「データディクショナリーのアップグレード後に、必要なら再起動を行うのがデフォルトだけどそれをしない」ためのオプションらしく、これを加えてもやっぱり同じエラーで止まった。
なのでワークアラウンドは5.7で起動しなおして、 ALTER TABLE .. FORCE で新旧DATETIME型の返還を走らせるなり mysqldump とリストアの手順で8.0にアップグレードするなり…といったところか。
ちなみに、MySQL 5.7にアップグレードしていないdatadirをそのままインプレースアップグレードで8.0にしようとすると、「5.7じゃないよ!」というエラーで落ちるのでこっちはわかりやすい。
旧DATETIME型のカラムがあるかどうかはMySQL ShellのUpgrade Checkerを使えばちゃんと検出できるので、良い子はちゃんとこれを使ってからバージョンアップしよう。

2018年11月6日火曜日

MySQL 8.0では「GROUP BYによる暗黙のソート」がされなくなるよ

MySQL 5.7の時点で既に宣言はされていた。
GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators), but relying on implicit GROUP BY sorting in MySQL 5.7 is deprecated.
どういうことが起こるかというと、こう。
$ perl -ML -E 'for (my $n= 1; $n <= 10; $n++) { for (my $m= 1; $m <= 100; $m++) { printf("%d\t%d\n", $n, $m) } }' | sort -R > /tmp/seq

mysql80 165> CREATE TABLE t1 (n int, m int);
Query OK, 0 rows affected (0.05 sec)

mysql80 165> LOAD DATA INFILE '/tmp/seq' INTO TABLE t1;
Query OK, 1000 rows affected (0.09 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

mysql80 165> SELECT * FROM t1 LIMIT 10;
+------+------+
| n    | m    |
+------+------+
|    2 |   18 |
|    4 |    7 |
|    6 |   85 |
|    1 |   69 |
|    4 |   66 |
|    7 |   52 |
|    3 |   25 |
|    2 |   19 |
|    1 |   61 |
|    5 |   80 |
+------+------+
10 rows in set (0.01 sec)
こんなデータに対して、 SELECT n, COUNT(m) FROM t1 GROUP BY n とか打つ。
今まではこうだった。
mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n;
+------+----------+
| n    | COUNT(m) |
+------+----------+
|    1 |      100 |
|    2 |      100 |
|    3 |      100 |
|    4 |      100 |
|    5 |      100 |
|    6 |      100 |
|    7 |      100 |
|    8 |      100 |
|    9 |      100 |
|   10 |      100 |
+------+----------+
10 rows in set (0.00 sec)
これからはこうなる。
mysql80 165> SELECT n, COUNT(m) FROM t1 GROUP BY n;
+------+----------+
| n    | COUNT(m) |
+------+----------+
|    2 |      100 |
|    4 |      100 |
|    6 |      100 |
|    1 |      100 |
|    7 |      100 |
|    3 |      100 |
|    5 |      100 |
|   10 |      100 |
|    9 |      100 |
|    8 |      100 |
+------+----------+
10 rows in set (0.00 sec)
よく見ると n は「見つかった順」にはなっているから、 KEY (n) を追加してそのインデックスを使わせて GROUP BY n すれば n でソート済にはなるんだけど、オプティマイザーのご機嫌とか伺いながらになるようなそんな手は選べない。
おとなしく ORDER BY を足しましょう。
mysql80 165> SELECT n, COUNT(m) FROM t1 GROUP BY n ORDER BY n;
+------+----------+
| n    | COUNT(m) |
+------+----------+
|    1 |      100 |
|    2 |      100 |
|    3 |      100 |
|    4 |      100 |
|    5 |      100 |
|    6 |      100 |
|    7 |      100 |
|    8 |      100 |
|    9 |      100 |
|   10 |      100 |
+------+----------+
10 rows in set (0.00 sec)

2018年11月1日木曜日

ER_OPTION_PREVENTS_STATEMENT(Error: 1290) The MySQL server is running with the .. について

$ perror 1290
MySQL error code MY-001290 (ER_OPTION_PREVENTS_STATEMENT): The MySQL server is running with the %s option so it cannot execute this statement
要は、「 %s だからそのSQLは実行できないよ」というエラー。
%s の部分に何が入るかは何パターンかあるけど、基本的にオプションの名前が入るので、SQLを成功させたければ %s をOFFにしてやれば上手くいくはず。

MySQL 5.7.24 のコードから ER_OPTION_PREVENTS_STATEMENT を投げるところを引いてみた感じ、あり得る %s のパターンはたぶんこう。

おそらくここまでがよくあるやつら。
ここからはコードをさらって見つけた変なやつら。
  • “—event-scheduler=DISABLED or —skip-grant-tables”
    • これ全部で1区切り
    • 実は event_schedulerON, OFF, DISABLED の3通りを取ることができて、 DISABLED で起動すると SET GLOBAL でONにできなくなる(それをやろうとするとこれが出る)
    • skip_grant_tables でもイベントスケジューラーいじれないのね(知らなかった)
  • embedded
    • 組み込み用mysqldである libmysqld を使っている場合に行ベースのバイナリーログイベント( mysqlbinlog でデコードしたものを含む)を食わせると発生
    • 初めて知った…
あー面白かった。

2018年10月30日火曜日

mysql.userテーブルの認証周りのカラムあれこれ

TL;DR

version passwordカラム(CHAR(41) NOT NULL) authentication_stringカラム(TEXT NULL) pluginカラム 認証プラグインの選択
5.0.96 パスワードハッシュ カラムなし カラムなし ダイジェスト長
5.1.73 パスワードハッシュ カラムなし カラムなし ダイジェスト長
5.5.62 パスワードハッシュ 常に空文字 認証プラグイン pluginカラム、空文字列の場合はダイジェスト長
5.6.42 SHA256プラグイン以外のパスワードハッシュ SHA256プラグインの時のパスワードハッシュ 認証プラグイン pluginカラム、空文字列の場合はダイジェスト長
5.7.24 カラムなし パスワードハッシュ 認証プラグイン pluginカラムのみ
8.0.13 カラムなし パスワードハッシュ 認証プラグイン pluginカラムのみ

日々の覚書: MySQL 5.7.6でmysql.userテーブルのパスワードのカラム名がなんか変わった で「MySQL 5.7ではパスワードハッシュが格納されるカラムが password から authentication_string に変更になった」としていたけれど、ちょっと調べてみたらなんかそれなりに歴史的経緯っぽいものがありました。
  • 旧来(MySQL 5.0, 5.1)は2種類の認証プラグイン( mysql_native_password = 俗称41桁ハッシュ、 mysql_old_password = 俗称16桁ハッシュ) があったが、それを識別するための plugin カラムは無く、「クライアントから送られてきたダイジェストの長さ」でどちらのプラグインを使うか決めて password カラムに入っているパスワードハッシュを引き出して使っていた
  • MySQL 5.5から plugin カラムが追加される。これは5.5で「認証プラグインAPI」を解放した時に一緒に実装されたのだと思う。
    • MySQL :: MySQL 5.5 Reference Manual :: 6.5.1 Authentication Plugins
    • たぶん過渡期の例外措置として、 plugin が空文字ならば旧来と同じダイジェストの長さによって mysql_native_passwordmysql_old_password を打ち分ける実装になっている
    • authentication_string カラムも実装されたけど、この時点でこのカラムを使っているっぽい認証プラグインは少なくともコミュニティー版のソースコード上にはない。
  • MySQL 5.6では商用版限定ながら sha256_password プラグインが登場する。
    • MySQL :: MySQL 5.6 Reference Manual :: 6.5.1.4 SHA-256 Pluggable Authentication
      • MySQL 8.0では同じ名前の sha256_password プラグインがコミュニティー版でも使えるようになった。実装まで同じかどうかは知らない。
    • MySQL 8.0の sha256_password プラグインと同じものだとすると、パスワードハッシュが $5$ から始まる66桁になるので、 Password CHAR(41) NOT NULL には入らずAuthentication_string TEXT に入れることになったのかな
      • password カラムの長さを変えるのはリスキーだなって感じで
  • MySQL 8.0では sha256_password と同じく256bitのSHA2を使う cache_sha2_password がデフォルトになるのもあって、この時点までに password カラムがなくなって authentication_string に一本化されるのは確定だっただろうから、ちょっと前倒ししてMySQL 5.7で消すことにしたんじゃないかなと思う。
動作とかの部分はそれなりに調べたけど、経緯の部分は想像しているだけで裏付けとかはないです。

2018年10月29日月曜日

MySQLのロール周りのあれこれ

TL;DR

  • デフォルトロールはログイン時に有効化
  • mandatory_roles はログイン時に有効化されない、全ユーザーから SET ROLE できるロール
  • ロールのホスト部は意味を持たないような気がする( role@127.0.0.1192.168.0.0 なアカウントにも割り当てができる)
  • activate_all_roles_on_login = ON にすると、デフォルトロールも割り当てたロールも mandatory_roles も全部いっぺんにログイン時に有効になる
ロールの有効化 ロールの作成 ロールの割り当て 割り当ての解除
ロール SET ROLE CREATE ROLE GRANT REVOKE
デフォルトロール ログイン時 CREATE ROLE CREATE USER .. DEFAULT ROLE または GRANT && ALTER USER .. DEFAULT ROLE REVOKE
必須ロール SET ROLE CREATE ROLE mandatory_roles を設定した時に自動で mandatory_roles からなくなった時に自動で

デフォルトロールとか必須ロールとかごちゃごちゃしてきたのでメモ。
  • フツーに CREATE ROLE して CREATE USER .. && GRANT <role_name> で与える場合、 GRANT したタイミングで mysql.role_edgesINSERT される
  • CREATE ROLE して CREATE USER .. DEFAULT ROLE .. した場合、 CREATE USER のタイミングで mysql.role_edges に入る
  • SET GLOBAL mandatory_roles .. の場合は mysql.role_edges には入らない
存在しないロールを SET GLOBAL mandatory_roles で指定すると↓のエラーになる(ただしステートメントはエラーにならず、エラーログにピヨッと現れるだけ)
2018-10-29T02:23:39.444341Z 108 [Warning] [MY-010968] [Server] Can't set mandatory_role: There's no such authorization ID role@localhost.
mandatory_rolesREVOKE したり DROP ROLE しようとしたりすると怒られる。
mysql80 120> REVOKE role@localhost FROM yoku0825;
ERROR 3628 (HY000): The role `role`@`localhost` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.
mandatory_roles はコンマ区切りで複数指定可能。
ただし、ロールのホスト部でフィルタリングがかけられるわけではない。
mysql80 127> SET GLOBAL mandatory_roles = 'role@1.2.3.4,role@localhost';
Query OK, 0 rows affected (0.02 sec)

$ mysql80 -uyoku0825
mysql80 130> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for yoku0825@localhost                                       |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`localhost`                        |
| GRANT `role`@`1.2.3.4`,`role`@`localhost` TO `yoku0825`@`localhost` |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 130> SET ROLE role@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql80 130> SET ROLE role@1.2.3.4; <-- localhost != 1.2.3.4 だけど判定されないSET ROLEできる
Query OK, 0 rows affected (0.00 sec)

とするとアレかね、
  • GRANT SELECT ON d1.* TO read_only_role
  • SET GLOBAL mandatory_roles = read_only_role
  • GRANT ALL ON d1.* TO read_write_role
  • CREATE USER apuser DEFAULT ROLE read_write_role
  • CREATE USER dev1 IDENTIFIED BY ..
とかやると、 apuser はデフォルトで読み書き可能、新規追加するアカウント(たとえば dev1 の次に dev2 とか)はデフォルトで読み取りのみ可能(なロールに SET ROLE できる)、とかの表現になるのか。
あるいは、 activate_all_roles_on_login = ON しておくと SET ROLE read_only_role とかしなくてもデフォルトで読み取りだけ可能とかになる、と。