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

2018年4月16日月曜日

MySQL 8.0.3とそれ以降では expire_logs_days は非推奨なパラメーターになりました

TL;DR


MySQL 8.0.1 で導入された binlog_expire_logs_seconds 当初は expire_logs_days足し合わせる という互換性に考慮した 結果余計ややこしい ユニークな設定方法になっていたのですが、 MySQL 8.0.4
  • binlog_expire_logs_secondsが設定されいてる場合はbinlog_expire_logs_secondsのみ適用、expire_logs_daysは無視される
  • binlog_expire_logs_secondsが未設定または0の時のみexpire_logs_daysが適用される
に変わっていた。
一緒に指定しようとするとこんなワーニングになる。
2018-04-16T04:37:43.284839Z 0 [Warning] [MY-011079] The option expire_logs_days cannot be used together with option binlog_expire_logs_seconds. Therefore, value of expire_logs_days is ignored.
ところで、MySQL 8.0.11で binlog_expire_logs_seconds のデフォルトが2592000(30日)に変わるらしいんだけど、この場合binlog_expire_logs_secondsが未設定だとこの値が適用されそうだから、明示的に binlog_expire_logs_seconds=0 にした時だけ expire_logs_days の評価に入るのかしらん、という感じ。
ちなみにMySQL 8.0.4現在では、 binlog_expire_logs_secondsexpire_logs_days は特に連動していない( expire_logs_days の値が有効になっている状態でも binlog_expire_logs_seconds に反映してくれたりはしない)。
飽くまで「どちらか片方だけが設定されているていで、バイナリーログがパージされる関数の中で計算する」だけだった。
というわけで、MySQL 8.0向けの秘伝のタレは expire_logs_daysbinlog_expire_logs_seconds に書き換えておきましょう。
やらかした ありがちなミスとしては、
  • expire_logs_seconds って書いて「そんなパラメーター知らん」って言われる
  • 値を変えるときに 日 -> 秒への変換を忘れてバイナリーログがあっという間にパージされている
くらいでしょうか。お気を付けください。

2018年4月13日金曜日

MySQL 8.0の再帰CTE(WITH RECURSIVE)で1000行以上の結果セットを作りたいとき

TL;DR


単なる連番のテストデータを作りたい時とか、再帰CTEは便利(というかMySQLerは今まで再帰CTEが使えなかったので、そもそもそれ以外の使い方は思いつかないわけだが)だけれど、 ↓ を訳した時点ではWHERE句を間違えるとさっくりとクエリーが逝きっぱなしになっていた。
それが、MySQL 8.0.3から cte_max_recursion_depth が追加されて、「これを超えるステップの再起CTEはエラー」にするようになっていた。
mysql80 36> SELECT @@session.cte_max_recursion_depth;
+-----------------------------------+
| @@session.cte_max_recursion_depth |
+-----------------------------------+
|                              1000 |
+-----------------------------------+
1 row in set (0.01 sec)

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < 10) SELECT * FROM t;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < 1001) SELECT * FROM t;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
ちなみにこれ系のパラメーターって 0 にセットすると無制限になると思うじゃろ? 何故かこいつは違うんじゃ
mysql80 36> SELECT @@session.cte_max_recursion_depth;
+-----------------------------------+
| @@session.cte_max_recursion_depth |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < 10) SELECT * FROM t;
ERROR 3636 (HY000): Recursive query aborted after 1 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < -1) SELECT * FROM t; -- シードSELECTの1行しか返さないような条件でもアウト
ERROR 3636 (HY000): Recursive query aborted after 1 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
0にすると再帰CTEが一切合切拒否される仕様になっております。
ほわー。

MySQL 8.0は SELECT .. FOR UPDATE SKIP LOCKED とJSON_TABLES関数で「取り敢えずJSON」が捗る?

TL;DR

  • auto_increment + JSON型(あるいはBLOB型やTEXT型でもいいけど)に生のJSONを突っ込む
  • 後から SELECT .. FOR UPDATE SKIP LOCKED でワーカーが取り出して、 INSERT .. SELECT JSON_TABLE(..) FROM .. で正規化したテーブルに突っ込みなおす
  • 外部APIからの戻りのJSONを取り敢えずログテーブルに格納して…みたいな感じを想像している

PoC

こんなテーブルを用意した。
mysql80 13> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `seq` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `raw_json` json DEFAULT NULL,
  PRIMARY KEY (`seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)
Twitterから1~10件のTweetを取ってきて、そのままテーブルに突っ込む。
my $count= int(rand(10) + 1);
my $json= to_json($twitter->search({q => "'MySQL'", lang => "ja", count => $count}));

$conn->do("INSERT INTO t2 (raw_json) VALUES (?)", undef, $json);
何回か叩いてテーブルの中身を確認するとこんな感じ。
mysql80 29> SELECT seq, JSON_LENGTH(raw_json->'$.statuses') FROM t2;
+-----+-------------------------------------+
| seq | JSON_LENGTH(raw_json->'$.statuses') |
+-----+-------------------------------------+
|   1 |                                   7 |
|   2 |                                   8 |
|   3 |                                  10 |
|   4 |                                  10 |
|   5 |                                   8 |
|   6 |                                   1 |
|   7 |                                   6 |
+-----+-------------------------------------+
7 rows in set (0.00 sec)
この状態で別々のクライアントから SELECT .. ORDER BY seq ASC LIMIT 1 FOR UPDATE SKIP LOCKED することで、「今他のクライアントがロックしていない行をseqの若い順に1件ロック」が表現できる。
mysql80 33> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 33> SELECT seq, raw_json FROM t2 ORDER BY seq ASC LIMIT 1 FOR UPDATE SKIP LOCKED INTO @seq, @raw_json;
Query OK, 1 row affected (0.00 sec)

mysql80 33> SELECT @seq;                                                                              +------+
| @seq |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

------

mysql80 34> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 34> SELECT seq, raw_json FROM t2 ORDER BY seq ASC LIMIT 1 FOR UPDATE SKIP LOCKED INTO @seq, @raw_json;
Query OK, 1 row affected (0.00 sec)

mysql80 34> SELECT @seq;
+------+
| @seq |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
それぞれのクライアントで seq=1seq=2 の行をロックするついでに、 @raw_json 変数に raw_json カラムの中身を代入している。
JSON_TABLES関数は残念ながらカラムの値を直接参照できない(びっくりした)ので、一度変数の中に入れてやらないといけない。むむむ。
mysql80 34> SELECT tweet_id, text FROM JSON_TABLE(@raw_json, '$.statuses[*]' COLUMNS (tweet_id NUMERIC(32) PATH '$.id', text VARCHAR(300) PATH '$.text')) AS json;
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id           | text                                                                                                                                                                                                                                                 |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 984620024198414338 | MySQL派でした                                                                                                                                                                                                                                        |
| 984619948621230080 | MySQLくらいしか触ったことない                                                                                                                                                                                                                        |
| 984616323194896384 | なんか最近mysqlでやべえアプデとかあった?                                                                                                                                                                                                            |
| 984614613839888384 | この前調べてみたらレンタカーよりカーシェアリングの方が安いという事実を知った。そんなに進んでいたのね。。。 #PHP #MySQL                                                                                                                               |
| 984613344014290944 | 速効!図解プログラミングPHP   MySQL―Windows/Linux PHP5対応 PHP5の基本から一歩ずつ学習。MySQLとの連携もマスター。力だめしの練習問題付き https://t.co/oT1wBBVkKV                                                                                      |
| 984608290205085696 | ISBB@東京の案件情報です。動画配信サイトシステム構築@都内。PHP(LAMP環境における開発経験3年以上必須。MySql経験尚可、アジャイル経験尚可。詳細はこちらhttps://t.co/Mtw5EpBQNO                                                                          |
| 984605840580591616 | ををを。。。MySQLのクエリの後に \G ってつけると、縦表示になるのか!初めて知った。

SELECT * FROM `table` LIMIT 10 \G;                                                                                                                                |
| 984603973939150850 | RT @yoku0825: デフォルトのままでもCPUを食い尽くすような正規表現はちゃんとタイムアウトするようになってた。えらい。

日々の覚書: 「危険な正規表現」 vs MySQL 8.0 https://t.co/hNpozpQnWz                                                               |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
JSON_TABLES は結構書式がややこしくて、FROM (JSON_TABLE(..)) AS dummy のFROM句サブクエリーの形で SELECT に受けるっぽい。
ちょっと思ったより使いにくくてびっくりしたけど、MySQLサーバー側の変数で完結できればJSONをパースするのにクライアントに転送しなくていいので通信量の削減にはなると思う。
ともあれ、 SELECT で受けられれば INSERT INTO .. SELECT .. の形に受けられるので、別のテーブルにINSERTして
mysql80 34> INSERT INTO t3 (tweet_id, text) SELECT tweet_id, text FROM JSON_TABLE(@raw_json, '$.statuses[*]' COLUMNS (tweet_id NUMERIC(32) PATH '$.id', text VARCHAR(300) PATH '$.text')) AS json;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql80 34> DELETE FROM t2 WHERE seq = @seq;
Query OK, 1 row affected (0.00 sec)

mysql80 34> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql80 34> SELECT seq, JSON_LENGTH(raw_json->'$.statuses') FROM t2;
+-----+-------------------------------------+
| seq | JSON_LENGTH(raw_json->'$.statuses') |
+-----+-------------------------------------+
|   1 |                                   7 |
|   3 |                                  10 |
|   4 |                                  10 |
|   5 |                                   8 |
|   6 |                                   1 |
|   7 |                                   6 |
+-----+-------------------------------------+
6 rows in set (0.00 sec)
JSON_TABLES関数がアレなことを除けば 割ときれいにジョブワーカーっぽいことができそうな予感。
ただこれ t2 (取り敢えずJSONで突っ込む)側のロックは分けられるけど、 t3 (正規化して突っ込む先)側はフツーにネクストキーロックとかデッドロックとかのアレを食らうので、 t3 側のロックはちゃんと設計しないと死ねそう。