2015年7月1日水曜日

innodb_stats_on_metadataが0でも問題なく(?)統計情報は再計算されるよ

innodb_stats_on_metadata=1でディスク容量激増とCPU負荷が発生 | DEVLAB を読んだ誰か(忘れた)に「innodb_stats_on_metadata= 0だと統計情報ズレない? 手でANALYZE TABLEしないといけないの?」って聞かれて答えたメモ(だと思う)

そもそも8ページとか20ページじゃ全然足りないじゃんというのはここでいう「問題なく」の中には含まれない。

innodb_stats_on_metadataは *メタデータにアクセス(=information_schema, SHOW TABLE STATUSなど)* した時に統計情報を再作成するかどうかのフラグで、データが大量に更新された時の統計情報の再作成は ここらへん でinnodb_stats_on_metadataを見ずにトリガーされている。


(gdb) b dict_stats_update
Breakpoint 1 at 0xa7c997: file /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc, line 3085.

gdbを突き刺しておいて


mysql56> SELECT @@innodb_stats_on_metadata;
+----------------------------+
| @@innodb_stats_on_metadata |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql56> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 99750
 Avg_row_length: 68
    Data_length: 6832128
..

統計情報はこんな感じ。
ここに、統計情報がアップデートされるくらいの行数を変更するクエリーを投げ込むと


mysql56> DELETE FROM t1 WHERE num < 10000;
ここで止まる。
Breakpoint 1, dict_stats_update (table=0x7fdad80180a8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
3085            if (table->ibd_file_missing) {
(gdb) bt
#0  dict_stats_update (table=0x7fdad80180a8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x0000000000a7e25a in dict_stats_process_entry_from_recalc_pool (arg=)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats_bg.cc:313
#2  dict_stats_thread (arg=) at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats_bg.cc:355
#3  0x00000036dac079d1 in start_thread (arg=0x7fdaf75fe700) at pthread_create.c:301
#4  0x00000036da8e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
バックグラウンドスレッドらしい。こんなのいたのか。。 gdbを放してやるとクエリーが返ってきて、
mysql56> DELETE FROM t1 WHERE num < 10000;
Query OK, 9999 rows affected (28.51 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89775
 Avg_row_length: 76
    Data_length: 6832128
..
統計情報は無事に更新されている。
mysql56> DELETE FROM t1 WHERE num < 100;
Query OK, 0 rows affected (0.01 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89775
 Avg_row_length: 76
    Data_length: 6832128
..

テーブルの10%または6.25%が更新されたら、っぽいので、それよりずっと小さい値にすればしばらく待ってても統計情報の更新は走らない。

他に統計情報が更新されるトリガーとしてはこのへん?

* テーブルキャッシュが存在しない場合は統計情報がアップデートされる。
* テーブルキャッシュが足りていないと頻繁にこれが走る羽目に遭う。

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_FETCH_ONLY_IF_NOT_IN_MEMORY)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a4fa4 in dict_stats_init (this=0x7f8bea635410, name=0x7f8bea634f38 "./d1/City", mode=,
    test_if_locked=) at /home/yoku0825/mysql-5.6.25/storage/innobase/include/dict0stats.ic:174
#2  ha_innobase::open (this=0x7f8bea635410, name=0x7f8bea634f38 "./d1/City", mode=,
    test_if_locked=) at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:4859
#3  0x000000000059370e in handler::ha_open (this=0x7f8bea635410, table_arg=,
    name=0x7f8bea634f38 "./d1/City", mode=2, test_if_locked=2) at /home/yoku0825/mysql-5.6.25/sql/handler.cc:2505
#4  0x0000000000766c8c in open_table_from_share (thd=0x7f8be9f45000, share=,
    alias=, db_stat=39, prgflag=, ha_open_flags=0, outparam=0x7f8bea61da00,
    is_create_table=false) at /home/yoku0825/mysql-5.6.25/sql/table.cc:2355
#5  0x000000000068e2fe in open_table (thd=0x7f8be9f45000, table_list=0x7f8bea65a210, ot_ctx=0x7f8bfb8a74d0)
    at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:3167
#6  0x0000000000690d48 in open_and_process_table (thd=0x7f8be9f45000, start=0x7f8bfb8a7588, counter=0x7f8be9f46e38, flags=0,
    prelocking_strategy=0x7f8bfb8a75a0) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:4662
#7  open_tables (thd=0x7f8be9f45000, start=0x7f8bfb8a7588, counter=0x7f8be9f46e38, flags=0,
    prelocking_strategy=0x7f8bfb8a75a0) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:5095
#8  0x0000000000690ea8 in open_normal_and_derived_tables (thd=0x7f8be9f45000, tables=0x7f8bea65a210,
    flags=) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:5791
#9  0x00000000006d64d3 in execute_sqlcom_select (thd=0x7f8be9f45000, all_tables=0x7f8bea65a210)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:5107
#10 0x00000000006db682 in mysql_execute_command (thd=0x7f8be9f45000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:2656
#11 0x00000000006dde67 in mysql_parse (thd=0x7f8be9f45000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#12 0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8be9f45000,
    packet=0x7f8bea6a6001 "SELECT * FROM City", packet_length=3932528674) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#13 0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#14 0x00000000006a7272 in handle_one_connection (arg=0x7f8bea251000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#15 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d56a0) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#16 0x00000030628079d1 in start_thread (arg=0x7f8bfb8a9700) at pthread_create.c:301
#17 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115


* ANALYZE TABLE実行時はもちろん統計情報をアップデートする

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a138d in ha_innobase::info_low (this=0x7f8c1a43e410, flag=28, is_analyze=true)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:10836
#2  0x00000000009a1c23 in ha_innobase::analyze (this=, thd=,
    check_opt=) at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:11186
#3  0x0000000000821cc5 in mysql_admin_table (thd=0x7f8bea738000, tables=0x0, check_opt=0x7f8bea73ab40,
    operator_name=0xbae3c6 "analyze", lock_type=TL_READ_NO_INSERT, open_for_modify=true, repair_table_use_frm=false,
    extra_open_options=0, prepare_func=0, operator_func=
    (int (handler::*)(handler *, THD *, HA_CHECK_OPT *)) 0x592970 ,
    view_operator_func=0) at /home/yoku0825/mysql-5.6.25/sql/sql_admin.cc:654
#4  0x0000000000822a08 in Sql_cmd_analyze_table::execute (this=, thd=0x7f8bea738000)
    at /home/yoku0825/mysql-5.6.25/sql/sql_admin.cc:1077
#5  0x00000000006db2dc in mysql_execute_command (thd=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:4976
#6  0x00000000006dde67 in mysql_parse (thd=0x7f8bea738000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#7  0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8bea738000, packet=0x7f8bea73c001 "",
    packet_length=3933540386) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#8  0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#9  0x00000000006a7272 in handle_one_connection (arg=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#10 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#11 0x00000030628079d1 in start_thread (arg=0x7f8bfb868700) at pthread_create.c:301
#12 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115


* innodb_stats_on_metadata= 1だとSHOW TABLE STATUSでも呼ばれる。

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_FETCH_ONLY_IF_NOT_IN_MEMORY)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a138d in ha_innobase::info_low (this=0x7f8c1a43e810, flag=212, is_analyze=false)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:10836
#2  0x00000000007125f0 in get_schema_tables_record (thd=0x7f8bea738000, tables=0x7f8bea7734d8, table=0x7f8bea472010,
    res=, db_name=, table_name=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:4543
#3  0x000000000070c1ad in fill_schema_table_by_open (thd=0x7f8bea738000, is_show_fields_or_keys=false, table=0x7f8bea472010,
    schema_table=0x12496a0, orig_db_name=0x7f8bfb866210, orig_table_name=0x7f8be9f2e410,
    open_tables_state_backup=0x7f8bfb866170, can_deadlock=false) at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:3588
#4  0x00000000007181c8 in get_all_tables (thd=0x7f8bea738000, tables=, cond=0x7f8bea471eb8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:4252
#5  0x0000000000706a61 in do_fill_table (join=0x7f8bea471a30, executed_place=PROCESSED_BY_JOIN_EXEC)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:7412
#6  get_schema_tables_result (join=0x7f8bea471a30, executed_place=PROCESSED_BY_JOIN_EXEC)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:7513
#7  0x00000000006fa85d in JOIN::prepare_result (this=0x7f8bea471a30, columns_list=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:822
#8  0x00000000006b628c in JOIN::exec (this=0x7f8bea471a30) at /home/yoku0825/mysql-5.6.25/sql/sql_executor.cc:116
#9  0x00000000006fc2d8 in mysql_execute_select (thd=0x7f8bea738000, tables=0x7f8bea7528f8, wild_num=0,
    fields=, conds=0x0, order=, group=0x7f8bea73a590, having=0x0,
    select_options=2684619520, result=0x7f8bea471a08, unit=0x7f8bea739e48, select_lex=0x7f8bea73a490)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:1100
#10 mysql_select (thd=0x7f8bea738000, tables=0x7f8bea7528f8, wild_num=0, fields=, conds=0x0,
    order=, group=0x7f8bea73a590, having=0x0, select_options=2684619520, result=0x7f8bea471a08,
    unit=0x7f8bea739e48, select_lex=0x7f8bea73a490) at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:1221
#11 0x00000000006fcbcf in handle_select (thd=0x7f8bea738000, result=0x7f8bea471a08, setup_tables_done_option=0)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:110
#12 0x00000000006d6655 in execute_sqlcom_select (thd=0x7f8bea738000, all_tables=0x7f8bea7528f8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:5134
#13 0x00000000006db682 in mysql_execute_command (thd=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:2656
#14 0x00000000006dde67 in mysql_parse (thd=0x7f8bea738000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#15 0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8bea738000,
    packet=0x7f8bea73c001 "show table status like 'City'", packet_length=3933540397)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#16 0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#17 0x00000000006a7272 in handle_one_connection (arg=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#18 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#19 0x00000030628079d1 in start_thread (arg=0x7f8bfb868700) at pthread_create.c:301
#20 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115


もっと詳しく知りたい場合、前に翻訳した Yakst - InnoDBのインデックス統計情報についていくつか が役に立つかも。

新ConoHaのMariaDBを試してみた

TL;DR

* Amazon RDS for MySQL的なものを探しているなら、これじゃないです。
* 単にユーザーとして観測できる範囲で調べて推測しているだけなので、違ったらごめんなさい。


ConoHaでマネージドデータベースサービスを始めたらしいので試してみた。MariaDB 10.0系ですってよ奥様。

チュートリアル的なものは本家のものを。

データベースサーバーを使う - このべん(ConoHa)


で、これはどうやら共用サーバーのようです。
WEBコンソール中の「データベースを作成する」は

mysql> CREATE DATABASE database_name CHARSET utf8;

に、

「データベースユーザーを追加する」は

mysql> CREATE USER user_name@hostname IDENTIFIED BY 'password_string';

に、

「ユーザーがアクセスできるデータベースを設定する」は

mysql> GRANT ALL ON database_name.* TO user_name@hostname

にマッピングされているようです。


mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for username@xxx.xxx.xxx.xxx                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'xxx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*E021B6BBA043401E045906A6646199BFADD9A0B1' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

こんなかんじで。

あ、共用サーバーっぽいの関係上、全世界に3306番ポート解放ってことになると思うので、ユーザー名(特に接続元ホスト指定)とパスワードはかなり重要です。たぶん。


取り敢えずデータベース権限だけでグローバル権限は一切無いので、

* レプリケーション関連の機能は一切使えない
* SET GLOBALで設定する必要がある変数は一切いじれない
* INSTALL PLUGINもちろんできない

WordPressユーザー想定してる感じなんですかね。
なので俺はたぶんお呼びでないし、それならWordPressテンプレート(というのがConoHaにはある)でいいんじゃね? って感じがするけれど。。

有料オプションでバックアップがあるんだけど、これってどうやって取ってるんだろう。XtraDBがある以上オンラインで取るならMyDumperかxbかmysqldumpか、整合性を保つためには一度スキーマの中全部をロックしてやらないといけないんだけど、その辺どうなってるのかも気になる。PITRもできるの? MariaDBが落ちたらちゃんとDNS切り替わるの? とか、クォータもなかなか共用サーバーでやろうとすると奥が深い(information_schemaからデータを引くと誤差とか誤差じゃない何かがあったり、REVOKEやFLUSH PRIVILEDGESでは即時反映されなかったり)ので、どうやってんのかなぁとか。


権限は限られていますが、取り敢えずSHOW GLOBAL VARIABLESの中でめぼしいパラメーターを拾い読み。


*************************** 15. row ***************************
Variable_name: aria_recover
        Value: NORMAL
*************************** 16. row ***************************
Variable_name: aria_repair_threads
        Value: 1
*************************** 323. row ***************************
Variable_name: myisam_recover_options
        Value: DEFAULT
*************************** 324. row ***************************
Variable_name: myisam_repair_threads
        Value: 1

MyISAMとAriaのリペアオプションが有効になってる。

( ´-`).oO(あれ、これクラッシュ後にリペアしてる真っ最中のロックってどうなるんだっけ。。


*************************** 25. row ***************************
Variable_name: back_log
        Value: 150

共有サーバーなので大き目なのかな。


*************************** 26. row ***************************
Variable_name: basedir
        Value: /usr
*************************** 45. row ***************************
Variable_name: character_sets_dir
        Value: /usr/share/mysql/charsets/
*************************** 52. row ***************************
Variable_name: datadir
        Value: /var/lib/mysql/



rpmでインストールしたかな?


*************************** 34. row ***************************
Variable_name: binlog_format
        Value: MIXED
*************************** 277. row ***************************
Variable_name: log_bin
        Value: ON

ふむ。一応ONだけど、もちろんREPLICATION SLAVE権限もREPLICATION CLIENT権限も使えない。内部でバックアップ取ってるのかな。


*************************** 131. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 10737418240
*************************** 142. row ***************************
Variable_name: innodb_concurrency_tickets
        Value: 5000
*************************** 144. row ***************************
Variable_name: innodb_data_file_path
        Value: ibdata1:12M:autoextend
*************************** 154. row ***************************
Variable_name: innodb_file_per_table
        Value: ON
*************************** 157. row ***************************
Variable_name: innodb_flush_method
        Value:
*************************** 175. row ***************************
Variable_name: innodb_io_capacity
        Value: 200
*************************** 176. row ***************************
Variable_name: innodb_io_capacity_max
        Value: 2000
*************************** 189. row ***************************
Variable_name: innodb_log_file_size
        Value: 268435456
*************************** 190. row ***************************
Variable_name: innodb_log_files_in_group
        Value: 2

innodb_concurrency_ticketsはデフォルトより大きくしてある。
InnoDBのコンテキストスイッチ的なものを制御する変数で、OLTPっぽい細かいI/O向けのシステムには小さく, OLAPみたいな大きくI/Oを食うシステムには大き目にするっていうのがあるんだけど、これはOLAPっぽく使うだろうと中の人が想定したんだろうか。

HDDの玉で仮想環境(かどうかは知らないけど)とかだとinnodb_flush_methodを未指定(Linux上ではfsync相当)でinnodb_io_capacityは無理にいじらないのは当たりだと思う。


*************************** 143. row ***************************
Variable_name: innodb_corrupt_table_action
        Value: assert

これはXtraDBのオプション なんだけど、吊るしのまま。


*************************** 151. row ***************************
Variable_name: innodb_file_format
        Value: Antelope

Super権限がないのでこの値は変えられないし、ということはROW_FORMAT= DynamicやCompressedはここでは使えないってことだ。。


*************************** 296. row ***************************
Variable_name: max_connect_errors
        Value: 999999999
*************************** 297. row ***************************
Variable_name: max_connections
        Value: 10000

(・∀・)ゞ

TCPポートを叩くだけ叩いてヘルスチェックにしちゃうやつだと、max_connect_errorsを大きくしておかないとそのうちホストごと接続拒否られるというアレか。
この形式だと、mysqldごと落ちてくれるやつはちゃんと切り替わるんだけど、mysqldがストールした場合って切り離してくれないことが多いのよね。

そして、max_connections= 10000が本当に押し寄せたらストールしちゃうんじゃないか感はある。


*************************** 311. row ***************************
Variable_name: max_tmp_tables
        Value: 32

何人で共有するかわからないけれど、max_connections 10000に対してちょっと少なすぎるんじゃ。。
とか思ったら、これセッション変数だった。失礼。


*************************** 312. row ***************************
Variable_name: max_user_connections
        Value: 30

max_user_connectionsが30になっているので、1ユーザーあたりの接続可能コネクションは30まで。

MySQL :: MySQL 5.6 Reference Manual :: 5.1.4 Server System Variables


$ perl -MDBI -e 'my @tmp; while (push(@tmp, DBI->connect("dbi:mysql:db_name:host_name", "user_name", "password"))) { print ++$n, "\n"; }'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DBI connect('db_name:host_name','user_name',...) failed: User user_name already has more than 'max_user_connections' active connections at -e line 1



*************************** 280. row ***************************
Variable_name: log_output
        Value: FILE
*************************** 286. row ***************************
Variable_name: log_warnings
        Value: 1
*************************** 287. row ***************************
Variable_name: long_query_time
        Value: 10.000000

*************************** 435. row ***************************
Variable_name: slow_query_log
        Value: ON

出力されてはいるぽいけど、ユーザーからアクセスする方法はなさげ。


*************************** 385. row ***************************
Variable_name: query_cache_size
        Value: 0
*************************** 387. row ***************************
Variable_name: query_cache_type
        Value: ON

クエリーキャッシュは無効化されてる。
グローバルロックに巻き込まれる心配はなかった :)


*************************** 451. row ***************************
Variable_name: ssl_ca
        Value: /etc/my.cnf.d/ssl/ast.tyo1.database-hosting.conoha.io-ca.crt
*************************** 452. row ***************************
Variable_name: ssl_capath
        Value:
*************************** 453. row ***************************
Variable_name: ssl_cert
        Value: /etc/my.cnf.d/ssl/ast.tyo1.database-hosting.conoha.io-server.crt
*************************** 454. row ***************************
Variable_name: ssl_cipher
        Value:
*************************** 455. row ***************************
Variable_name: ssl_crl
        Value:
*************************** 456. row ***************************
Variable_name: ssl_crlpath
        Value:
*************************** 457. row ***************************
Variable_name: ssl_key
        Value: /etc/my.cnf.d/ssl/ast.tyo1.database-hosting.conoha.io-server.key


一応SSL接続は有効化されてるけど、公開鍵もらえないとSSLで接続できない。。


*************************** 465. row ***************************
Variable_name: system_time_zone
        Value: UTC

( ゚д゚) えっ(ホントにNOW()がUTCで返ってきた)


*************************** 467. row ***************************
Variable_name: table_open_cache
        Value: 400

( ´-`).oO(innodb_stats_on_metadsataがOFFでも、テーブルキャッシュからあふれるとそのたび統計情報の更新がトリガーされちゃうから、このサイズキツくないかなぁ。。


*************************** 470. row ***************************
Variable_name: thread_handling
        Value: one-thread-per-connection

スレッドプール無効。
(もったいない。。)


*************************** 474. row ***************************
Variable_name: thread_pool_size
        Value: 4

thread_pool_sizeの暗黙のデフォルトはプロセッサー数なので、4vCPUってことかな。。


mysql> SELECT plugin_name, plugin_type, plugin_status FROM all_plugins WHERE plugin_status <> 'ACTIVE';
+-------------------------------+--------------------+---------------+
| plugin_name                   | plugin_type        | plugin_status |
+-------------------------------+--------------------+---------------+
| FEEDBACK                      | INFORMATION SCHEMA | DISABLED      |
| QUERY_CACHE_INFO              | INFORMATION SCHEMA | NOT INSTALLED |
| rpl_semi_sync_slave           | REPLICATION        | NOT INSTALLED |
| QUERY_RESPONSE_TIME           | INFORMATION SCHEMA | NOT INSTALLED |
| QUERY_RESPONSE_TIME_AUDIT     | AUDIT              | NOT INSTALLED |
| rpl_semi_sync_master          | REPLICATION        | NOT INSTALLED |
| Mroonga                       | STORAGE ENGINE     | NOT INSTALLED |
| Mroonga_stats                 | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB                        | STORAGE ENGINE     | NOT INSTALLED |
| TokuDB_trx                    | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_lock_waits             | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_locks                  | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_file_map               | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_fractal_tree_info      | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_fractal_tree_block_map | INFORMATION SCHEMA | NOT INSTALLED |
| pam                           | AUTHENTICATION     | NOT INSTALLED |
| METADATA_LOCK_INFO            | INFORMATION SCHEMA | NOT INSTALLED |
| InnoDB                        | STORAGE ENGINE     | NOT INSTALLED |
| INNODB_TRX                    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_LOCKS                  | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_LOCK_WAITS             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP                    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP_RESET              | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMPMEM                 | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMPMEM_RESET           | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP_PER_INDEX          | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP_PER_INDEX_RESET    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_BUFFER_PAGE            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_BUFFER_PAGE_LRU        | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_BUFFER_POOL_STATS      | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_METRICS                | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_DEFAULT_STOPWORD    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_DELETED             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_BEING_DELETED       | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_CONFIG              | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_INDEX_CACHE         | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_INDEX_TABLE         | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_TABLES             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_TABLESTATS         | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_INDEXES            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_COLUMNS            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_FIELDS             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_FOREIGN            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_FOREIGN_COLS       | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_TABLESPACES        | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_DATAFILES          | INFORMATION SCHEMA | NOT INSTALLED |
| LOCALES                       | INFORMATION SCHEMA | NOT INSTALLED |
| unix_socket                   | AUTHENTICATION     | NOT INSTALLED |
| handlersocket                 | DAEMON             | NOT INSTALLED |
| SERVER_AUDIT                  | AUDIT              | NOT INSTALLED |
| SPIDER                        | STORAGE ENGINE     | NOT INSTALLED |
| SPIDER_ALLOC_MEM              | INFORMATION SCHEMA | NOT INSTALLED |
| SEQUENCE                      | STORAGE ENGINE     | NOT INSTALLED |
| SQL_ERROR_LOG                 | AUDIT              | NOT INSTALLED |
| SPHINX                        | STORAGE ENGINE     | NOT INSTALLED |
+-------------------------------+--------------------+---------------+
55 rows in set (0.02 sec)

MariaDBにバンドルされているプラグインはほぼ全滅。 MariaDBである意味がほとんどない。。
InnoDB関連ぽいのが軒並みNOT INSTALLEDになってるのは、InnoDBの代わりにXtraDBが使われているから(WHERE plugin_status= 'ACTIVE'で引くとXtraDB側のInnoDBやInnoDB関連のinformation_schemaがACTIVEになっている)

Mroongaさん有効にしてほしいなぁ。。起動したらそのままSQLで全文検索できるよとか素敵じゃない? クォータかけづらい気もするけど。


という訳で何をどう考えても、ConoHa VPSに自分でMariaDBをyumで突っ込んだ方が良さげな雰囲気がしました。まる。

と書いてから気づいたんだけど、旧ConoHaでいうところのWordPressテンプレート的なものってなんかMySQL 5.1.73になってて(旧ConoHaのはPercona Serverだったはず)なんか劣化してるんじゃ。。:(;゙゚'ω゚'):

2015年6月29日月曜日

MySQLのHandlerレイヤーが何をしているのか探る旅 at #ChugokuDB

過日、このイベントでしゃべってきました。

MySQL・PostgreSQLユーザーグループ(MyNA・JPUG)合同DB勉強会 in 東京 - 中国地方DB勉強会 | Doorkeeper





最初はスライドの副題の通り、主にInnoDB memcached PluginとNDB memcached Engineの違い、要は、memcachedプロトコルをしゃべるmysqldプロセスと、NDB APIをしゃべるmemcachedプロセスの違い…とかなんとかしゃべろうとしてたんですが、気が付いたら各daemon pluginがどの辺のレイヤーまで横取りしているのかを調べていました。

本当は「redisからデータを取り出すストレージエンジンがあってredisプロトコルをしゃべるdaemon pluginがあればほら! redis-cliでredisからデータが取り出せるMySQLのできあがり! 変態!」とかやりたかったんですけど、daemon pluginの壁は高かったです。残念。


このイベント、
MySQLとPostgreSQLのユーザ会の合同データベース勉強会なのに
セミナーは各データベースのNoSQL関連機能縛りという
ユニークなイベントになっています。
[mysql 16234] MyNA・JPUG合同DB勉強会 in 東京 のご案内


ということだったんですけど、MySQLって昔から(少なくとも俺が本格的に触り始めたころには)HandlerSocketがあったしMySQL Clusterのmemcached(NDB memcached Engine)もあったので、InnoDB memcached Pluginも「ああ、また出たのね」って感じだしMySQL HTTP Pluginも「ああ、また出たのね」って感じだし、MySQLにとってはよくあることなんじゃないかなーと思ってます。

世に出回っているdaemon pluginの出来のよさにもちょっと驚きました。MySQL HTTP Plugin *以外は*

あと、PostgreSQLはやっぱり真面目で、JSON(B)型とそれを入出力する関数や演算子の充実を丁寧にやっていて、あー、やっぱりMySQLとは違うんだなぁという感じがしました。

@soudaiさん お疲れ様でした!


【2015/06/29 12:59】

そういえば去年こんなやり取りがあって、



中国地方DB勉強会 in 東京なんていう不思議な勉強会で実現して初めて顔を合わせたの、感慨深い。

2015年6月25日木曜日

MYSQL HTTP PLUGIN確認 の補足

MySQL HTTP Plugin確認 | variable.jp [データベース,パフォーマンス,運用] を写経して失敗したので補足メモ。

2. my.cnfをマニュアルを見て編集
myhttp_default_mysql_user_name = http_sql_user
myhttp_default_mysql_user_passwd = sql_secret
myhttp_default_mysql_user_host = 127.0.0.1

* myhttp_default_mysql_user_nameは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときに使うユーザー名。暗黙のデフォルトは "root"
* myhttp_default_mysql_user_passwdは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときに使うパスワード。暗黙のデフォルトは ""(空文字列)

ということでこの2つは、`3. myhttpデータベースを検証用に作成しテスト用テーブルとユーザーを作成` の後ろの方でCREATE USERしたユーザー/パスワードと合わせる。

このホスト部分はcurlで指定したホスト部分に書き換わるので、↑の記事の通りcurl --url 192.168.56.x:8080 ..ならそのまま通るけど、curl --url 127.0.0.1:8080にした場合はhttp_sql_user@'192.168.56.0/255.255.255.0'に対してhttp_sql_user@127.0.0.1でログインしようとしちゃうので、curlが通らなくなる。罠い。


しかもタチの悪いことに、

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+%2A+FROM+simple"

$ tail data/error.log
..
2015-06-25T07:44:56.207437Z 2 [ERROR] Invalid user

HTTPレスポンス何か返せよ! エラーメッセージ雑だよ! せめて自分がmyhttpプラグインだって名乗れよ!

あと、

* myhttp_default_mysql_user_hostは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときの…ホスト…。

  * ってことは、myhttpプラグインがインストールされているマシンじゃないMySQLにもつなぎにいけるってことか…この変態め…。
  * と思ったけど、127.0.0.1以外の何かに書き換えてもダメ。どうやら、curlでリクエストしたホスト名に置き換えているぽい。

$ curl -s --user basic_auth_user:basic_auth_passwd --url "http://172.17.0.2:8080/sql/myhttp/SELECT+%2A+FROM+simple" | jq .

Breakpoint 1, my_da_sql_init_security_ctx (thd=0x7fdf80005780, user=0x2eeb148 "http_sql_user",
    host=0x7fdf800055a8 "172.17.0.2", db=0x7fdf80005668 "myhttp")
    at /export/home/pb2/build/sb_0-13293843-1411506406.69/mysql-5.7.5-labs-http/sql/sql_da_sql.cc:146
146         char * user_copy= my_strdup(key_memory_Security_context, user, MYF(MY_WME));


$ curl -s --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+%2A+FROM+simple" | jq .

Breakpoint 1, my_da_sql_init_security_ctx (thd=0x7fdf780040e0, user=0x2eeb148 "http_sql_user",
    host=0x7fdf7800e378 "127.0.0.1", db=0x7fdf78001988 "myhttp")
    at /export/home/pb2/build/sb_0-13293843-1411506406.69/mysql-5.7.5-labs-http/sql/sql_da_sql.cc:146
146         char * user_copy= my_strdup(key_memory_Security_context, user, MYF(MY_WME));

一応ホスト名とか受け取れるけど、ロードバランサーとかの下に入れるとどういう動きするんでしょうねこれ。。


curl --userで指定するBASIC認証部分のユーザー名とパスワードはそれぞれ

* myhttp_basic_auth_user_name .. 暗黙のデフォルト "basic_auth_user"
* myhttp_basic_auth_user_passwd .. 暗黙のデフォルト "basic_auth_passwd"


地雷ヤーズのためのメモでした。

2015年6月19日金曜日

現代のMySQLはもう不要にINをEXISTSに書き換えない

漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。

この記事は 2009/3/25 に書かれたもののようである。
2009年3月といえばMySQL 5.1がGAになってわずか半年、MySQL 6.0.10-alphaがリリースされた頃で、MariaDBもまだ姿を見せていない頃だ。


時は流れて2015年、MySQL 5.6がGAになって早2年半、5.7のGAマダァ-? (・∀・ )っ/凵⌒☆チンチン

な頃なので、もういい加減誰か言ってくれてもいいんじゃないかと思う。


もうMySQL(5.6)は不要にINをEXISTSに書き換えたりしないんだよって


mysql51> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
| id | select_type        | table   | type           | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
|  1 | PRIMARY            | Country | ALL            | NULL          | NULL        | NULL    | NULL |  222 | Using where |
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | CountryCode   | CountryCode | 3       | func |    9 | Using where |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
2 rows in set (0.00 sec)


mysql55> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
| id | select_type        | table   | type           | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
|  1 | PRIMARY            | Country | ALL            | NULL          | NULL        | NULL    | NULL |  226 | Using where |
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | CountryCode   | CountryCode | 3       | func |    7 | Using where |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
2 rows in set (0.02 sec)


mysql56> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref             | rows | Extra                            |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
|  1 | SIMPLE      | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  239 | Using where                      |
|  1 | SIMPLE      | City    | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |    9 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
2 rows in set (0.00 sec)

mysql57> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra                            |
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
|  1 | SIMPLE      | Country | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  239 |    14.29 | Using where                      |
|  1 | SIMPLE      | City    | NULL       | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |   18 |    33.33 | Using where; FirstMatch(Country) |
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql57> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'd1.Country.Population' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `d1`.`Country`.`Code` AS `Code`,`d1`.`Country`.`Name` AS `Name`,`d1`.`Country`.`Continent` AS `Continent`,`d1`.`Country`.`Region` AS `Region`,`d1`.`Country`.`SurfaceArea` AS `SurfaceArea`,`d1`.`Country`.`IndepYear` AS `IndepYear`,`d1`.`Country`.`Population` AS `Population`,`d1`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`d1`.`Country`.`GNP` AS `GNP`,`d1`.`Country`.`GNPOld` AS `GNPOld`,`d1`.`Country`.`LocalName` AS `LocalName`,`d1`.`Country`.`GovernmentForm` AS `GovernmentForm`,`d1`.`Country`.`HeadOfState` AS `HeadOfState`,`d1`.`Country`.`Capital` AS `Capital`,`d1`.`Country`.`Code2` AS `Code2` from `d1`.`Country` semi join (`d1`.`City`) where ((`d1`.`City`.`CountryCode` = `d1`.`Country`.`Code`) and (`d1`.`Country`.`Continent` = 'Asia') and (`d1`.`City`.`Population` > (`d1`.`Country`.`Population` / 2)))
2 rows in set (0.00 sec)

クエリーは先頭にリンクを張った、御大のブログ記事のものと一緒。world_innodb.sqlを流してから、3つのテーブルに対してANALYZE TABLEをかけてある。5.1.73, 5.5.44, 5.6.25, 5.7.7の比較だ。

最後の5.7のワーニングは、5.7のデフォルトのEXPLAINがEXPLAIN EXTENDEDになったので、オプティマイズした結果をワーニングバッファに突っ込んでいる。SHOW WARNINGSでオプティマイザーがどうクエリーを書き換えたかが見えるのでよく見ると。

select .. from `d1`.`Country` semi join (`d1`.`City`) where ..


semi-join最適化は5.6.5からの実装なので、MySQL 5.6のGAバージョンならこれは5.6でも同じように最適化できる。
MySQL :: MySQL 5.6 Reference Manual :: 8.2.1.18 Subquery Optimization

もちろんすべてのINが良しなにされるとは限らない(本当に相関サブクエリーが必要なケースは相関サブクエリーになるし、5.7になっても相関サブクエリーは遅い)が、MySQLのオプティマイザーがバカなせいでつらい目を見た過去の俺とか俺とか俺とかにはとても嬉しい。

日本よ、これがMySQL 5.6だッ!! (c) nippondanji


ちなみに、

MariaDB [d1]> SELECT @@version;
+----------------+
| @@version      |
+----------------+
| 5.3.12-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [d1]> EXPLAIN EXTENDED SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra                            |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
|  1 | PRIMARY     | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  269 |   100.00 | Using where                      |
|  1 | PRIMARY     | City    | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |    1 |   100.00 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
2 rows in set, 2 warnings (0.00 sec)

MariaDB [d1]> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'd1.Country.Population' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `d1`.`Country`.`Code` AS `Code`,`d1`.`Country`.`Name` AS `Name`,`d1`.`Country`.`Continent` AS `Continent`,`d1`.`Country`.`Region` AS `Region`,`d1`.`Country`.`SurfaceArea` AS `SurfaceArea`,`d1`.`Country`.`IndepYear` AS `IndepYear`,`d1`.`Country`.`Population` AS `Population`,`d1`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`d1`.`Country`.`GNP` AS `GNP`,`d1`.`Country`.`GNPOld` AS `GNPOld`,`d1`.`Country`.`LocalName` AS `LocalName`,`d1`.`Country`.`GovernmentForm` AS `GovernmentForm`,`d1`.`Country`.`HeadOfState` AS `HeadOfState`,`d1`.`Country`.`Capital` AS `Capital`,`d1`.`Country`.`Code2` AS `Code2` from `d1`.`Country` semi join (`d1`.`City`) where ((`d1`.`City`.`CountryCode` = `d1`.`Country`.`Code`) and (`d1`.`Country`.`Continent` = 'Asia') and (`d1`.`City`.`Population` > (`d1`.`Country`.`Population` / 2)))
2 rows in set (0.00 sec)

MariaDBはもっと前からできるんだけどな! :)
(5.3で試してるけど、もとは5.2くらいだったような気がする。ただし裏は取ってない)

ところでsemi joinって聞くたびに蝉ジョインとか脳内変換されるんだけどこれなんとかなりませんか。

2015年6月9日火曜日

MySQL 5.7.4で導入されたdefault_password_lifetimeがじわじわくる

TL;DR

default_password_lifetime= 0 を秘伝のmy.cnfに入れておくつもり。



MySQL :: MySQL 5.7 Reference Manual :: 5.1.4 Server System Variables


パラメーターの意味は読んで字のごとく、「最後にパスワードが更新されてからこの期間が経つと、パスワードをEXPIREする」。暗黙のデフォルトは360で、単位は日。つまり何もいじらないと、新規作成したユーザーは360日後にパスワードがEXPIREされてアプリが止まる。。


mysql57> CREATE USER yoku0825 IDENTIFIED WITH mysql_native_password AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA';
Query OK, 0 rows affected (0.03 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 2015-06-09 11:06:33
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

こんなユーザーを作ってみる。360日待ってはいられないので、password_last_changedを過去に戻すことで状況を再現させてみる。


mysql57> UPDATE mysql.user SET password_last_changed= '1999-07-31 00:00:00' WHERE user= 'yoku0825'\G
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

mysql57> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

うむ、これはいい。余計なお世話だけど、これはまだいい。
この時にmysql.userの中身を見てみても、


mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

password_expiredは'N'のままだし、password_filetimeはNULLのままだ。

ということは、


mysql57> SET GLOBAL default_password_lifetime= 36500;
Query OK, 0 rows affected (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

mysql57> SET GLOBAL default_password_lifetime= 0;
Query OK, 0 rows affected (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

default_password_lifetimeを大きくしてやればそのまま通るし、default_password_lifetime= 0はデフォルトのライフタイムを無効化する値なのでこちらでも通る。
これ、アカウントの作成時に判定しているのではなくて、ログインのたびに check_password_lifetime関数 の中で判定している。password_lifetimeが設定されていればその値を、NULLならdefault_password_lifetimeを、そして *0なら* 決してEXPIREしない。


mysql57> SELECT @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
|                         360 |
+-----------------------------+
1 row in set (0.00 sec)

いったんもとに戻して、ALTER USERをいろいろ叩いてみた。
MySQL :: MySQL 5.7 Reference Manual :: 13.7.1.1 ALTER USER Syntax


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: 0
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

EXPIRE NEVERにすると、password_lifetimeカラムの値が0になる。default_password_lifetimeは評価されない。


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

EXPIRE DEFAULTにするとpassword_lifetimeがNULLになり、default_password_lifetimeの影響を受ける。


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE INTERVAL 36000 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: 36000
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

EXPIRE INTERVAL .. DAYでライフタイムを決め打ちすると、そちらが優先される(nullでないのでdafault_password_lifetimeは読まない)


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: Y
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: 36000
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

EXPIREだけで他に何もつけない状態だと、password_expiredが'Y'に更新される。この場合、password_last_changedは評価されず(check_password_lifetime関数の中には入るけど、最初のif文ですぐ抜ける)、EXPIRE状態になる。この状態になると、password_expired= 'N'に戻すためにSET PASSWORDで変更するか、UPDATEしてFLUSH PRIVILEGESするしかない。

思いもよらず360日後にこの罠に襲われる人が、1人でも少なくなりますように。


【2015/06/09 18:47】
デフォルトは0にするかワーニングとか出して、ってFeature Requestを上げました。
ダウンロードする時に「No thanks」を知らずにアカウント作っちゃった人とか、"Affects Me"で応援していただけるとありがたいです。

MySQL Bugs: #77277: default_password_lifetime should be set 0 as implicit default value

2015年6月4日木曜日

MySQL 5.7からデフォルトになるSTRICT_TRANS_TABLEはMyISAMにも影響を及ぼす

恥ずかしながら完全に誤解してた。

MySQL :: MySQL 5.6 Reference Manual :: 5.1.7 Server SQL Modes

For nontransactional tables, the behavior is the same for either mode if the bad value occurs in the first row to be inserted or updated: The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict mode is enabled:

For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.6, “Data Type Default Values”.


strictとして扱われるかかどうかは (STRICT_ALL_TABLES | STRICT_TRANS_TABLES) で決まり、ストレージエンジンがトランザクション対応かどうかは関係ない。

つまり、

mysql56> CREATE TABLE t1 (val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql56> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

mysql56> INSERT INTO t1 VALUES ('ab');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql56> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM t1;
+------+
| val  |
+------+
| a    |
+------+
1 row in set (0.01 sec)

sql_modeが空っぽならワーニングで入るけど、


mysql56> CREATE TABLE t2 (val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.10 sec)

mysql56> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql56> INSERT INTO t2 VALUES ('ab');
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t2;
Empty set (0.00 sec)

STRICT_TRANS_TABLESだと入らない。


更に、

mysql56> INSERT INTO t3 VALUES ('a'), ('ab'), ('abc');
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql56> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1406 | Data too long for column 'val' at row 2 |
| Warning | 1406 | Data too long for column 'val' at row 3 |
+---------+------+-----------------------------------------+
2 rows in set (0.01 sec)

mysql56> SELECT * FROM t3;
+------+
| val  |
+------+
| a    |
| a    |
| a    |
+------+
3 rows in set (0.01 sec)

1個目が正しいデータで、2個目移行が間違ったデータを突っ込もうとすると、warningが起こるがINSERTはされる。


mysql56> INSERT INTO t4 VALUES ('abc'), ('ab'), ('a');
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t4;
Empty set (0.00 sec)

1個目が正しくなかった場合、あとに正しいデータが入っていてもそれもabortされる。


STRICT_TRANS_TABLESは操作対象がトランザクション対応の時にstrict_modeになって、トランザクション非対応な時はnon-strict_modeだと完全に誤解していた。。