2018年5月30日水曜日

MySQL 8.0にはperformance_schema.events_statements_summary_by_digest にQUERY_SAMPLE_TEXTカラムが追加された

MySQL 5.7からMySQL 8.0でのevents_statements_summary_by_digestのカラム変更。
$ diff -y --suppress-common-lines <(mysql57 -sse "DESC p_s.events_statements_summary_by_digest")
 <(mysql80 -sse "DESC p_s.events_statements_summary_by_digest")
DIGEST  varchar(32)     YES             NULL                  | DIGEST  varchar(64)     YES             NULL
FIRST_SEEN      timestamp       NO              0000-00-00 00 | FIRST_SEEN      timestamp(6)    NO              0000-00-00 00
LAST_SEEN       timestamp       NO              0000-00-00 00 | LAST_SEEN       timestamp(6)    NO              0000-00-00 00
                                                              > QUANTILE_95     bigint(20) unsigned     NO              NULL
                                                              > QUANTILE_99     bigint(20) unsigned     NO              NULL
                                                              > QUANTILE_999    bigint(20) unsigned     NO              NULL
                                                              > QUERY_SAMPLE_TEXT       longtext        YES             NULL
                                                              > QUERY_SAMPLE_SEEN       timestamp(6)    NO              0000-
                                                              > QUERY_SAMPLE_TIMER_WAIT bigint(20) unsigned     NO
  • FISRST_SEEN, LAST_SEEN のマイクロ秒対応
  • QUANTILE_*, QUERY_SAMPLE_* カラムの追加
そのうち、 QUERY_SAMPLE_* の動作に関わる performance_schema_max_digest_sample_age のはなし。

を読む感じ、
  • performance_schema_max_digest_sample_age = 0 の時
    • “Resampling based on wait times” で、 TIMER_WAIT が現在の QUERY_SAMPLE_TIMER_WAIT を超えたらサンプルを更新する
  • performance_schema_max_digest_sample_age > 0 の時
    • 最後に QUERY_SAMPLE_* を保管した時刻から performance_schema_max_digest_sample_age 秒以上経ってまた同じダイジェストが記録された時にサンプルを更新する
という動きなようす。
サンプルは STATEMENT_DIGEST みたいなものでノーマライズされる前の生のSQL。
というわけでPKを変えながら10万回SELECTを流しながら、このテーブルを覗いてみた。
$ for n in $(seq 1 100000) ; do
> mysql80 -e "SELECT * FROM d1.t1 WHERE num = $n" > /dev/null
> done

mysql80 100022> SELECT * FROM p_s.events_statements_summary_by_digest WHERE count_star > 10\G...
*************************** 3. row ***************************
                SCHEMA_NAME: NULL
                     DIGEST: d214d5d8f31ce686d36be01a22bc7cfff76dd8b7b131644c7fcad28e76f78489
                DIGEST_TEXT: SELECT * FROM `d1` . `t1` WHERE `num` = ?
                 COUNT_STAR: 100000
             SUM_TIMER_WAIT: 18943715535000
             MIN_TIMER_WAIT: 121014000
             AVG_TIMER_WAIT: 189437000
             MAX_TIMER_WAIT: 5925412000
              SUM_LOCK_TIME: 8737613000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 100000
          SUM_ROWS_EXAMINED: 100000
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-05-30 14:44:59.376251
                  LAST_SEEN: 2018-05-30 15:00:46.526876
                QUANTILE_95: 251188643
                QUANTILE_99: 363078054
               QUANTILE_999: 660693448
          QUERY_SAMPLE_TEXT: SELECT * FROM d1.t1 WHERE num = 96806
          QUERY_SAMPLE_SEEN: 2018-05-30 15:00:17.487788
    QUERY_SAMPLE_TIMER_WAIT: 1973581000
3 rows in set (0.01 sec)
ちょくちょく QUERY_SAMPLE_* が変わったり変わらなかったりする。
個人的には performance_schema_max_digest_sample_age = 0 にして「一番時間がかかった時のサンプル」を残しておくのがいいかなと思ったり思わなかったり。
ちなみに、ビューに対するクエリーの場合、サンプルはビューに対するアクセスのクエリーで DIGEST_TEXT はビューを展開した後のクエリーをノーマライズするので一瞬「んっ?」となった。
慣れれば平気。
mysql80 100022> SELECT * FROM p_s.events_statements_summary_by_digest WHERE count_star > 10\G
*************************** 1. row ***************************
                SCHEMA_NAME: p_s
                     DIGEST: 9cbd44d9fdf48860a2f21597fd1d543130319eb800204c6ed51491e68ff55dcd
                DIGEST_TEXT: SELECT `performance_schema` . `events_statements_summary_by_digest` . `SCHEMA_NAME` AS `SCHEMA_NAME` , `performance_schema` . `events_statements_summary_by_digest` . `DIGEST` AS `DIGEST` , `performance_schema` . `events_statements_summary_by_digest` . `DIGEST_TEXT` AS `DIGEST_TEXT` , `performance_schema` . `events_statements_summary_by_digest` . `COUNT_STAR` AS `COUNT_STAR` , `performance_schema` . `events_statements_summary_by_digest` . `SUM_TIMER_WAIT` AS `SUM_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `MIN_TIMER_WAIT` AS `MIN_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `AVG_TIMER_WAIT` AS `AVG_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `MAX_TIMER_WAIT` AS `MAX_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `SUM_LOCK_TIME` AS `SUM_LOCK_TIME` , `performance_schema` . `events_statements_summary_by_digest` . `SUM_ERRORS` AS `SUM_ERRORS` ,
                 COUNT_STAR: 12
             SUM_TIMER_WAIT: 24253861000
             MIN_TIMER_WAIT: 925197000
             AVG_TIMER_WAIT: 2021155000
             MAX_TIMER_WAIT: 6172994000
              SUM_LOCK_TIME: 6760000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 25
          SUM_ROWS_EXAMINED: 64
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 12
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 12
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-05-30 17:20:00.342417
                  LAST_SEEN: 2018-05-30 17:21:16.207898
                QUANTILE_95: 6309573444
                QUANTILE_99: 6309573444
               QUANTILE_999: 6309573444
          QUERY_SAMPLE_TEXT: SELECT * FROM p_s.events_statements_summary_by_digest WHERE count_star > 10
          QUERY_SAMPLE_SEEN: 2018-05-30 17:21:11.760156
    QUERY_SAMPLE_TIMER_WAIT: 1947630000

2018年5月29日火曜日

default_collation_for_utf8mb4 なんてパラメーターが追加になっていた

何このパラメーター、と思ったら、 utf8mb4 のデフォルトコレーションが utf8mb4_general_ci (MySQL 5.7とそれ以前) から utf8mb4_0900_ai_ci (MySQL 8.0)に変わったことに対する経過措置っぽかった。
これを utf8mb4_general_ci にセットしておくと、コレーションを指定せずに utf8mb4 を使った時に今まで通り utf8mb4_general_ci を使ってくれるということ。
当然 CREATE TABLE, ALTER TABLE では想像したように動いて
mysql80 13> SELECT @@default_collation_for_utf8mb4;
+---------------------------------+
| @@default_collation_for_utf8mb4 |
+---------------------------------+
| utf8mb4_general_ci              |
+---------------------------------+
1 row in set (0.00 sec)

mysql80 13> CREATE TABLE t1 (num serial, val varchar(32) CHARSET utf8mb4);
Query OK, 0 rows affected (0.03 sec)

mysql80 13> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)
さらにコレーションを指定しなかった時のキャラクターセットのキャスト(知ってましたかこんな機能。俺は知ってたけど使ったことはない)でもそのように動く。
mysql80 13> SELECT @@default_collation_for_utf8mb4;
+---------------------------------+
| @@default_collation_for_utf8mb4 |
+---------------------------------+
| utf8mb4_general_ci              |
+---------------------------------+
1 row in set (0.00 sec)

mysql80 13> SELECT _utf8mb4 'A' = _utf8mb4 'A';
+-------------------------------+
| _utf8mb4 'A' = _utf8mb4 'A'  |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)

mysql80 13> SELECT @@default_collation_for_utf8mb4;
+---------------------------------+
| @@default_collation_for_utf8mb4 |
+---------------------------------+
| utf8mb4_0900_ai_ci              |
+---------------------------------+
1 row in set (0.00 sec)

mysql80 13> SELECT _utf8mb4 'A' = _utf8mb4 'A';
+-------------------------------+
| _utf8mb4 'A' = _utf8mb4 'A'  |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)
日本人には kamipoのハハ=パパ問題 を引き起こすことで有名だけれど、ヨーロッパ圏もやっぱりアクセントが区別されなくなるのは大事だったんだなぁ、とか思ったり思わなかったり。
なお、経過措置なのでこのパラメーターには初め(導入はMySQL 8.0.11)からDEPRECATEDなワーニングを生成するようになっている。
mysql80 13> SET SESSION default_collation_for_utf8mb4 = 'utf8mb4_0900_ai_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 13> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                |
+---------+------+--------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. |
+---------+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


【2018/05/29 14:48】
ちなみに、取りうる値は utf8mb4_general_ciutf8mb4_0900_ai_ci なので、 utf8mb4_ja_0900_as_cs とか指定しようとしてもダメなのです!


mysql80 13> SET SESSION default_collation_for_utf8mb4 = 'utf8mb4_ja_0900_as_cs';
ERROR 3721 (HY000): Invalid default collation utf8mb4_ja_0900_as_cs: utf8mb4_0900_ai_ci or utf8mb4_general_ci expected






2018年5月24日木曜日

"「7の倍数」を表す正規表現" をMySQL 8.0で試す

1年半くらい前に書かれたらしいけれど、ふと今日 「7の倍数」を表す正規表現 - Qiita を見つけて読んだ。
(取り敢えず今の俺の中で)正規表現といえばMySQL 8.0。
そして(取り敢えず今の俺の中で)forループ的に数値をテストするといえばCTE、CTEといえばMySQL 8.0。
やってみます。

さすがに元の正規表現は長くて直接クエリーに記述してるとめげるのでストアドファンクションにラップする。
mysql80 26> CREATE FUNCTION regexp_7(n BIGINT UNSIGNED) RETURNS INT DETERMINISTIC RETURN n RLIKE '\\A(((((([07]|(6[29]*3) <snip> )))))))))+\\z';
Query OK, 0 rows affected (0.03 sec)
バックスラッシュは二重にしなければならない、くらいで意外とすんなりストアドファンクションにできた。動くかどうかはわからない。
mysql80 26> WITH RECURSIVE seq AS(
    -> SELECT 1 AS n
    -> UNION ALL
    -> SELECT n + 1 FROM seq WHERE n < 10
    -> )
    -> SELECT n FROM seq;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)
そしてまあ簡単な再起CTE。
組み合わせるとこうなる。
mysql80 26> WITH RECURSIVE seq AS( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 10 ) SELECT n, regexp_7(n) AS r FROM seq;
+------+------+
| n    | r    |
+------+------+
|    1 |    0 |
|    2 |    0 |
|    3 |    0 |
|    4 |    0 |
|    5 |    0 |
|    6 |    0 |
|    7 |    1 |
|    8 |    0 |
|    9 |    0 |
|   10 |    0 |
+------+------+
10 rows in set (0.12 sec)
nが正規表現にマッチした時はrが1、マッチしなければ0。
取り敢えず1~10の範囲では問題なく(MySQLの正規表現エンジンが)動いている様子。
mysql80 26>  WITH RECURSIVE seq AS(
    -> SELECT 1 AS n, 0 AS r
    -> UNION ALL
    -> SELECT n + 1, regexp_7(n + 1) FROM seq WHERE n < 49
    -> )
    -> SELECT n, r FROM seq WHERE r = 1;
+------+------+
| n    | r    |
+------+------+
|    7 |    1 |
|   14 |    1 |
|   21 |    1 |
|   28 |    1 |
|   35 |    1 |
|   42 |    1 |
|   49 |    1 |
+------+------+
7 rows in set (0.56 sec)
いい感じに見やすくなったのでnを1~49まで増やしてる。重い。
ついでにnと前回のn(LAG(n))の差を取れば常に7になるはずだと思ってWindow関数にも手を出してみる。
mysql80 26>  WITH RECURSIVE seq AS(
    -> SELECT 1 AS n, 0 AS r
    -> UNION ALL
    -> SELECT n + 1, regexp_7(n + 1) FROM seq WHERE n < 49
    -> )
    -> SELECT n, r, n - LAG(n) OVER (ORDER BY n) AS diff FROM seq WHERE r = 1;
+------+------+------+
| n    | r    | diff |
+------+------+------+
|    7 |    1 | NULL |
|   14 |    1 |    7 |
|   21 |    1 |    7 |
|   28 |    1 |    7 |
|   35 |    1 |    7 |
|   42 |    1 |    7 |
|   49 |    1 |    7 |
+------+------+------+
7 rows in set (0.62 sec)
じゃあここまでをもう一段CTEに閉じ込めて、diff <> 7のものを探してみる。
mysql80 26> WITH RECURSIVE seq AS( SELECT 1 AS n, 0 AS r UNION ALL SELECT n + 1, regexp_7(n + 1) FROM seq WHERE n < 1000 ),
    ->                     ret AS( SELECT n, r, n - LAG(n) OVER (ORDER BY n) AS diff FROM seq WHERE r = 1)
    -> SELECT * FROM ret WHERE diff <> 7;
Empty set (12.60 sec)
1~1000で12秒なら、1万件で2分くらいかしらん、と思ったら↓に当たった。
mysql80 26> SET cte_max_recursion_depth = 100000;
Query OK, 0 rows affected (0.00 sec)

mysql80 26> WITH RECURSIVE seq AS( SELECT 1 AS n, 0 AS r UNION ALL SELECT n + 1, regexp_7(n + 1) FROM seq WHERE n < 10000 ),
    ->                     ret AS( SELECT n, r, n - LAG(n) OVER (ORDER BY n) AS diff FROM seq WHERE r = 1)
    -> SELECT * FROM ret WHERE diff <> 7;
Empty set (2 min 19.87 sec)
うん、楽しい、と、思う。

2018年5月21日月曜日

yum版のMySQL 8.0をCentOSなコンテナーにインストールすると Operation not permitted って言われる… (ビルド時にsetcap cap_sys_nice+ep されてた)

TL;DR

  • docker run する時に --cap-add=SYS_NICE を付け加えてやると上手くいく

$ docker run -it centos:centos7 bash
[root@457e75eaa657 /]# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
[root@457e75eaa657 /]# yum install -y mysql-community-server
[root@457e75eaa657 /]# mysqld --version
bash: /usr/sbin/mysqld: Operation not permitted
なんだこりゃ。
MySQL 5.7では起こらない。
$ docker run -it centos:centos7 bash
[root@05b4d1450b64 /]# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
[root@05b4d1450b64 /]# yum install -y --disablerepo="mysql80-community" --enablerepo="mysql57-community" mysql-community-server
[root@05b4d1450b64 /]# mysqld --version
mysqld  Ver 5.7.22 for Linux on x86_64 (MySQL Community Server (GPL))
MySQL 8.0.11でも、”Linux Generic” のtarボールでは起こらない。
$ docker run -it centos:centos7 bash
[root@cb720d43d956 /]# yum install -y wget tar libaio numactl
[root@cb720d43d956 /]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
[root@cb720d43d956 /]# tar xf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
[root@cb720d43d956 /]# mv -i mysql-8.0.11-linux-glibc2.12-x86_64 /usr/local/mysql
[root@cb720d43d956 /]# /usr/local/mysql/bin/mysqld --version
/usr/local/mysql/bin/mysqld  Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
straceで叩いてみたりしたけど、最初の execve で直接EPERMが返ってきちゃって何をしようとした時に権限が足りないのか全く分からない…。
[root@cb720d43d956 /]# strace -f mysqld --version
execve("/usr/sbin/mysqld", ["mysqld", "--version"], [/* 8 vars */]) = -1 EPERM (Operation not permitted)
write(2, "strace: exec: Operation not perm"..., 38strace: exec: Operation not permitted
) = 38
exit_group(1)                           = ?
+++ exited with 1 +++
取り敢えず地味な切り分けの結果、 SYS_NICE を追加すれば動くことはわかった。
何だろう。 mbind もこのケーパビリティーで制御されるっぽいから ヌゥ馬 かしら。
$ docker run -it --cap-add=SYS_NICE centos:centos7 bash
[root@2c042726caa1 /]# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
[root@2c042726caa1 /]# yum install -y mysql-community-server
[root@2c042726caa1 /]# mysqld --version
/usr/sbin/mysqld  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)
MySQL公式のDockerリポジトリー で使ってる、 mysql-community-server-minimal のパッケージではこれは起きない。
$ docker run -it centos:centos7 bash
[root@6ff99c37bb08 /]# yum install -y https://repo.mysql.com/yum/mysql-8.0-community/docker/x86_64/mysql-community-server-minimal-8.0.11-1.el7.x86_64.rpm
[root@6ff99c37bb08 /]# mysqld --version
/usr/sbin/mysqld  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)
いまいち原因ははっきりしないけど、コンテナーでほげほげしようとしている誰かに届けば幸い。


【2018/05/22 10:08】
@withgod さんに教えていただいた。












MySQL :: MySQL 8.0 Reference Manual :: 8.12.5 Resource Groups

微妙に書いてあった。

2018年5月18日金曜日

なるべく負荷をかけずにInnoDBバッファプールに載っているページの情報を見る

TL;DR

  • information_schema.innodb_buffer_page は重い
  • ib_buffer_pool にはテーブルスペースIDが記録されるので、それを使ってほげほげする
  • こんな感じ?
mysql> SET GLOBAL innodb_buffer_pool_dump_now = 1;
mysql> SELECT space, name FROM information_schema.innodb_sys_tablespaces INTO OUTFILE '/tmp/space.txt';

$ awk -F, '{print $1}' /var/lib/mysql/ib_buffer_pool | sort | join - <(sort /tmp/space.txt) | uniq -c | sort -n -r -k 1 | head
  54570 50 hogehoge/fugafuga
  12192 27 hogehoge/message
  10494 31 hogehoge/piyopiyo
   9683 42 hogehoge/magomago
   6103 30 hogehoge/message_inbox

このバグレポートを見て「うん、知ってた」感があって( sys.innodb_buffer_statsu_by_tableinformation_schema.innodb_buffer_page をベーステーブルにした ビュー なので)なんかのたびに「このテーブル刺さるよ」みたいな話もしていた気がするけれど、そういえば最近編み出したワークアラウンドって書いてないなと思ったので書いておく。
で、やることは ib_buffer_pool ファイル(InnoDBの暖気に使うアレ)からテーブルスペースIDを引っ張ってきて、 information_schema.innodb_sys_tablespaces (テーブルスペースIDとテーブル名の紐づけができる)と突き合わせるだけ。
mysql> SET GLOBAL innodb_buffer_pool_dump_now = 1;
mysql> SELECT space, name FROM information_schema.innodb_sys_tablespaces INTO OUTFILE '/tmp/space.txt';

$ awk -F, '{print $1}' /var/lib/mysql/ib_buffer_pool | sort | join - <(sort /tmp/space.txt) | uniq -c | sort -n -r -k 1 | head
  54570 50 hogehoge/fugafuga
  12192 27 hogehoge/message
  10494 31 hogehoge/piyopiyo
   9683 42 hogehoge/magomago
   6103 30 hogehoge/message_inbox
innodb_file_per_table = 0 だとたぶん上手くいかない(共有テーブルスペースに載ったテーブルはスペースID 0で出てくるので join しようがない)
これならほとんど負荷なくInnoDBバッファプールの中身をチラ見することができる。ただし、ページNoとインデックス名を紐づけられるような情報は information_schema にはないので、あくまでテーブル単位。

2018年4月20日金曜日

MySQL 8.0のnutshellを読んで秘伝のタレをどうこうしようと思っているメモ

たぶん本当にメモ。
caching_sha2_password, it is now the preferred authentication plugin
innodb_undo_log_truncate is enabled by default.
The default innodb_autoinc_lock_mode setting is now 2 (interleaved).
The default character set has changed from latin1 to utf8mb4.
Added support in MySQL 8.0.2 for partial, in-place updates of JSON column values
The TempTable storage engine replaces the MEMORY storage engine as the default engine for in-memory internal temporary tables.
[mysqld]
default_authentication_plugin= mysql_native_password ### For client compatibility

innodb_undo_log_truncate= OFF

innodb_autoinc_lock_mode= 1 ### For binlog_format != ROW

character_set_server= utf8mb4  ### Default.
collation_server = utf8mb4_bin ### or utf8mb4_ja_0900_as_cs

##binlog_row_value_options= PARTIAL_JSON ### When stepping into mine-field

## internal_tmp_mem_storage_engine = MEMORY ### 5.7 style
## max_heap_table_size= 128M ### 128M is an example, 5.7 style
internal_tmp_mem_storage_engine = TempTable ### 8.0 style, default
temptable_max_ram= 128M ### is default 1G too large?


【2018/03/20 17:18】
ナッツシェルには書いてないけど、X Pluginを無効化するための設定。


mysqlx= OFF

2018年4月17日火曜日

MySQL 8.0のSTATEMENT_DIGEST関数を使ってストアドプロシージャでSQLにホワイトリストを適用する

STATEMENT_DIGEST関数 はSQLステートメントから定数をノーマライズしたもの(ダイジェスト)をハッシュ化して返してくれる関数。
MySQL 5.6とそれ以降の performance_schema.events_statements_summary_by_digest なんかで使われているアレを関数で引くことができる。
パッと思いつく感じだと、「今まではダイジェストの値を直接計算できなかったから QUERY_SAMPLE_TEXT カラムの値とかから何となく探していたけど、これからは直接 WHERE digest = STATEMENT_DIGEST('SELECT ..') とかで検索できる」というのがメリットとしてあるんだけれど、クエリーをノーマライズして一元化できるってことはつまりホワイトリストっぽいものが作れるんじゃないかなと思ったので軽くテスト。
まずはホワイトリストを登録するためのテーブルを作る。
大事なのは digest だけであって、 digest_text は単なるおまけ(後々見るのに楽かなって)
mysql80 7> SHOW CREATE TABLE myeval.whitelist\G
*************************** 1. row ***************************
       Table: whitelist
Create Table: CREATE TABLE `whitelist` (
  `digest` varchar(64) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `digest_text` text COLLATE utf8mb4_ja_0900_as_cs
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.01 sec)
( ´-`).oO(utf8mb4_binのがいいかな
これに例えばこんなクエリーでホワイトリスト登録する。
mysql80 7> INSERT INTO myeval.whitelist VALUES (STATEMENT_DIGEST('SELECT * FROM d1.t1 WHERE num = 1'), STATEMENT_DIGEST_TEXT('SELECT * FROM d1.t1 WHERE num = 1'));
Query OK, 1 row affected (0.00 sec)

mysql80 7> SELECT * FROM myeval.whitelist;
+------------------------------------------------------------------+--------------------------------------------+
| digest                                                           | digest_text                                |
+------------------------------------------------------------------+--------------------------------------------+
| d214d5d8f31ce686d36be01a22bc7cfff76dd8b7b131644c7fcad28e76f78489 | SELECT * FROM `d1` . `t1` WHERE `num` = ?  |
+------------------------------------------------------------------+--------------------------------------------+
1 row in set (0.01 sec)
num = 1 の部分はどうせノーマライズされるのでテキトーな値。
これに、「 myeval.whitelist に登録があればそのクエリーを実行、なければError: 1142をレイズする」ストアドプロシージャを用意する。
delimiter //
CREATE PROCEDURE myeval.eval_query (IN sql_statement TEXT)
BEGIN
  DECLARE is_white TINYINT;
  SELECT (digest IS NOT NULL) FROM myeval.whitelist WHERE digest = STATEMENT_DIGEST(sql_statement) INTO is_white;
  IF is_white = 1 THEN
    SET @sql_statement := sql_statement;
    PREPARE st FROM @sql_statement;
    EXECUTE st;
    DEALLOCATE PREPARE st;
  ELSE
    SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = "Query isn't registored in myeval.whitelist", MYSQL_ERRNO = 1142;
  END IF;
END
//
delimiter ;
このプロシージャーを実行する権限だけを持たせたユーザーを用意して
mysql80 7> CREATE USER yoku0825;
Query OK, 0 rows affected (0.00 sec)

mysql80 7> GRANT EXECUTE ON PROCEDURE myeval.eval_query TO yoku0825;
Query OK, 0 rows affected (0.03 sec)
そのアカウントでログイン。
mysql80 9> SHOW GRANTS;
+--------------------------------------------------------------------+
| Grants for yoku0825@%                                              |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%`                               |
| GRANT EXECUTE ON PROCEDURE `myeval`.`eval_query` TO `yoku0825`@`%` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
当然このアカウントでは直接 d1.t1 に対するアクセスはできないけれども、 SQL SECURITY DEFINER なストアドプロシージャを通せば、ストアドを作ったアカウントの権限でそのSQLが実行できるようになる。
mysql80 9> SELECT * FROM d1.t1 WHERE num = 1;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 't1'

mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num = 1");
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num = 2");
+-----+------+
| num | val  |
+-----+------+
|   2 | two  |
+-----+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
ダイジェストが一致すれば通すので、細かい定数部分が違っても問題なく。
mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE val = 'one'");
ERROR 1142 (42000): Query isn't registored in myeval.whitelist

mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num < 2");

ERROR 1142 (42000): Query isn't registored in myeval.whitelist

mysql80 9> CALL myeval.eval_query("INSERT INTO d1.t1 VALUES (3, 'three')");
ERROR 1142 (42000): Query isn't registored in myeval.whitelist
ただしステートメントがそもそも違うものや、対象カラム、演算子が違うとダイジェストが変わるので弾かれる。
mysql80 7> INSERT INTO myeval.whitelist VALUES (STATEMENT_DIGEST('INSERT INTO d1.t1 VALUES (1, "one")'), STATEMENT_DIGEST_TEXT('INSERT INTO d1.t1 VALUES (1, "one")'));
Query OK, 1 row affected (0.01 sec)

mysql80 7> SELECT * FROM myeval.whitelist;
+------------------------------------------------------------------+--------------------------------------------+
| digest                                                           | digest_text                                |
+------------------------------------------------------------------+--------------------------------------------+
| d214d5d8f31ce686d36be01a22bc7cfff76dd8b7b131644c7fcad28e76f78489 | SELECT * FROM `d1` . `t1` WHERE `num` = ?  |
| bcaf175197bfc4753d6de62d76dcd05484a9cb5ca65f4cb2f4b1b065c5e6ae0d | INSERT INTO `d1` . `t1` VALUES (...)       |
+------------------------------------------------------------------+--------------------------------------------+
2 rows in set (0.00 sec)
テキトーに myeval.whitelist に登録してやれば
mysql80 9> CALL myeval.eval_query("INSERT INTO d1.t1 VALUES (3, 'three')");
Query OK, 0 rows affected (0.01 sec)

mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num = 3");
+-----+-------+
| num | val   |
+-----+-------+
|   3 | three |
+-----+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
特に再読み込みとかせずに実行できるようにできる。
MySQL Enterprise Firewall みたいなことをSQLだけでできそうな予感がしたのでやってみました。
SQLだけでできるってことは、某RDSとかでも8.0が来たら出来るかも? とか :-P