GA

2020/07/30

MySQL 8.0.17とそれ以前では、CREATE USER .. DEFAULT ROLE ..構文を使うと、ロールの情報が正しくレプリケーションされない

TL;DR

  • mysql.role_edges テーブルと mysql.default_roles テーブルがマスターとスレーブでズレる
    • マスターでは登録されるけどスレーブでは登録されない
    • つまりスレーブでは「そのロールを使う権限もそのロールがデフォルトロールである情報も失われる」
  • See MySQL Bugs: #93252: Default role is not logged into the binary log
    • Fixed in 8.0.18

8.0.15を使ってレプリケーションを組んでいたらハマった。
バイナリログへの記録がそもそもおかしいことになる。

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.17    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE ROLE myrole;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER yoku0825 DEFAULT ROLE myrole;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW BINLOG EVENTS IN 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                           |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         124 | Server ver: 8.0.17, Binlog ver: 4                                              |
| binlog.000002 | 124 | Previous_gtids |         1 |         155 |                                                                                |
| binlog.000002 | 155 | Anonymous_Gtid |         1 |         232 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                           |
| binlog.000002 | 232 | Query          |         1 |         333 | CREATE ROLE myrole /* xid=7 */                                                 |
| binlog.000002 | 333 | Anonymous_Gtid |         1 |         410 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                           |
| binlog.000002 | 410 | Query          |         1 |         559 | CREATE USER 'yoku0825'@'%' IDENTIFIED WITH 'caching_sha2_password' /* xid=8 */ |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

そもそも、 binlog_format によらずSQLがそのまま転記されるタイプの CREATE USER ステートメントだが「俺が打ったものと若干違う(そしてそこには DEFAULT ROLE 句はない)」ことがお分かりいただけるだろうか。

この CREATE USER ステートメントの書き換え自体は ずっと昔からやっていてIDENTIFIED BY で平文パスワードを指定しても「平文はバイナリログに載せず、ハッシュ後の値でバイナリログに載せる」ために構文を書き換えたりしていた。あとは認証プラグインの情報とかも追記している(これはいつからだったか…)

で、その書き換え部分にバグがあったっぽい。
修正コミットはこれ。

Bug#28948915 DEFAULT ROLE IS NOT LOGGED INTO THE BINARY LOG · mysql/mysql-server@d278a87

CREATE USER .. DEFAULT ROLE .. は「ユーザー作成」「ロールの許可」「デフォルトロールの指定」がいっぺんに指定できて便利なんだけど、MySQL 8.0.17とそれ以前を使っている場合は注意。

日々の覚書: CREATE USER .. DEFAULT ROLE .. で指定すると一発でROLEも許可される

( ´-`).oO(↑の時点では気が付いてなくて、最近スレーブをスイッチオーバーさせようとして初めて気が付いた…つらい…

pt-table-checksum で mysql.role_edges とmysql.default_roles だけがズレてたらこれかも知れないので気を付けて!

2020/07/21

DEBUG_SYNCことはじめ

TL;DR


DEBUG_SYNCとは、デバッグビルドのmysqldの中だけでブレークポイントみたいなものを設定して、ブレークポイントに差し掛かったら他のコネクションを使って操作を試してみられる…と思ってもらえれば多分大丈夫。

ただし任意の箇所でブレークできるわけではなく、コードの中に埋め込まれた “synchronization points” の場所にだけ仕掛けられる。以下、わかりやすさ優先で ブレークポイント と呼ぶことにする。

基本的な使い方はこんな感じ。

  1. mysqld --debug-sync-timeout=n で起動する (デフォルトは0でDEBUG_SYNC無効)
  2. ブレークポイントを通らせたいスレッドで SET DEBUG_SYNC = 'synchronization_pointsの名前 SIGNAL 送信シグナル名 WAIT_FOR テキトーなシグナル名'
  3. 2. のスレッドがブレークポイントを通った時に操作をしたいスレッドで SET DEBUG_SYNC = 'now WAIT_FOR 送信シグナル名
  4. 3. のスレッドはその時点で debug_sync_timeout 秒間の待機に入るので、その間に 2. のスレッドでブレークポイントを通るような操作をする。
  5. 2. のスレッドがブレークポイントを通ると SIGNAL 句で設定した 送信シグナル名 がブロードキャストされて、 3. のスレッドがそれを受け取って復帰する。 2. のスレッドはそのまま WAIT_FOR 句で テキトーなシグナル名 がブロードキャストされるのを待つ
  6. 3. のスレッドなりシェルなりで好きな操作をする
  7. 3. のスレッドで SET DEBUG_SYNC = 'now SIGNAL テキトーなシグナル名'テキトーなシグナル名 がブロードキャストされて 2. のスレッドが復帰して残りの処理をする

3. のスレッドは本質ではない(別にこれがなくてもブレークポイントを通ったあと待ちに入る)けれど、これがないと「ブレークポイントを通るまでの処理をしているのか、通ったあとに来ないシグナルを待っているのか」の区別がつかないのでこうするのが吉だと思う。


デバッグビルドしたmysqldを --debug-sync-timeout=n (n > 0) で起動。

$ ./bin/mysqld --max-allowed-packet=1G --gtid_mode=ON --enforce_gtid_consistency=ON --debug-sync-timeout=600 --daemonize

autocommit環境下でストレージエンジンへのINSERT後COMMITになる前の状態、とかを観測してみたりできる。

conn1> SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL here WAIT_FOR yoku0825';
Query OK, 0 rows affected (0.01 sec)

conn2> SET DEBUG_SYNC = 'now WAIT_FOR here'; -- nowは特殊なキーワードで常にブレークする。nowにヒットしたのでhereシグナル待ちに入る

conn1> INSERT INTO d1.t1 SET lo = REPEAT('a', 128 * 1024 * 1024); -- ib_after_row_insertに到達したあと hereシグナルを送ってyoku0825シグナルを待つ

conn2> SET DEBUG_SYNC = 'now WAIT_FOR here'; -- hereシグナルを受け取って叩き起こされる
Query OK, 0 rows affected (7.64 sec)

conn2> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+---------------------------------------+-----------------------------------------------------------+
| Id | User            | Host      | db   | Command | Time | State                                 | Info                                                      |
+----+-----------------+-----------+------+---------+------+---------------------------------------+-----------------------------------------------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  |   87 | Waiting on empty queue                | NULL                                                      |
|  3 | root            | localhost | NULL | Query   |    0 | starting                              | SHOW PROCESSLIST                                          |
|  4 | root            | localhost | NULL | Query   |   20 | debug sync point: ib_after_row_insert | INSERT INTO d1.t1 SET lo = REPEAT('a', 128 * 1024 * 1024) |
+----+-----------------+-----------+------+---------+------+---------------------------------------+-----------------------------------------------------------+
3 rows in set (0.03 sec)

conn2> SELECT COUNT(*) FROM d1.t1; -- ib_after_row_insertはCOMMITの手前なのでautocommitでも行は見えない
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

conn2> quit -- 1回抜けてシェルに
Bye

### バイナリログにも書かれていない
$ mysqlbinlog /usr/mysql/5.7.23/data/bin.000011 | tail 
mysqlbinlog: [Warning] unknown variable 'loose-default-character-set=utf8mb4'.
# at 134218325
#200721 15:34:00 server id 1  end_log_pos 134218402 CRC32 0x8f60827e    Query   thread_id=4     exec_time=1     error_code=0
SET TIMESTAMP=1595313240/*!*/;
TRUNCATE d1.t1
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

$ mysql -S /tmp/mysql.sock -uroot
mysql> SET DEBUG_SYNC = 'now SIGNAL yoku0825';  -- yoku0825シグナルを送ってやる
Query OK, 0 rows affected (0.00 sec)

conn1> INSERT INTO d1.t1 SET lo = REPEAT('a', 128 * 1024 * 1024); -- yoku0825シグナルに叩き起こされてINSERTが完了する
Query OK, 1 row affected (2 min 28.38 sec)

楽しい、いろいろ捗りそう(要出典: 何が)

2020/07/02

MySQLで CURDATE() - 1 は「昨日の日付」を返さない

TL;DR

  • 意図したことをMySQLでやるには CURDATE() - INTERVAL 1 DAY
  • というか俺はむしろDATE型から数値を引くことに違和感があるんですがこれってOracleの書き方なんでしたっけ?

昨日の朝、こんなのを見た。

昨日の夜、このブログを読んだ。

けんつさんのブログは「文字列とDATE型」だけれど、バグレポートの方は「数値とDATE型」に起因する。
折角なのでちょっと書いておこうかと思った。


まず、DATE型と数値型を演算しようとするとDATE型が数値型にキャストされて戻り値も数値型になる。
DATE型から数値型へのキャストは 年 * 1000000 + 月 * 1000 + 日 で行われる(DATETIME型の場合は時分秒を格納するために更に桁数が増える)。逆の操作で有効な日付が生成できれば、数値型からDATE型へのキャストもできる。

mysql80 13> SELECT CURDATE(), CAST(CURDATE() AS SIGNED), CAST(20200702 AS DATE);
+------------+---------------------------+------------------------+
| CURDATE()  | CAST(CURDATE() AS SIGNED) | CAST(20200702 AS DATE) |
+------------+---------------------------+------------------------+
| 2020-07-02 |                  20200702 | 2020-07-02             |
+------------+---------------------------+------------------------+
1 row in set (0.00 sec)

よって(2020/7/2から)1を引くと20200701になるし、これはDATE型にキャストし返せる。 - INTERVAL 1 DAY にしておけば最初からDATE型で返ってくる。

mysql80 13> SELECT CURDATE() - 1, CAST(CURDATE() - 1 AS DATE), CURDATE() - INTERVAL 1 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 1 | CAST(CURDATE() - 1 AS DATE) | CURDATE() - INTERVAL 1 DAY |
+---------------+-----------------------------+----------------------------+
|      20200701 | 2020-07-01                  | 2020-07-01                 |
+---------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

これが月をまたぐと雲行きが怪しくなってくる。

mysql80 13> SELECT CURDATE() - 2, CAST(CURDATE() - 2 AS DATE), CURDATE() - INTERVAL 2 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 2 | CAST(CURDATE() - 2 AS DATE) | CURDATE() - INTERVAL 2 DAY |
+---------------+-----------------------------+----------------------------+
|      20200700 | 2020-07-00                  | 2020-06-30                 |
+---------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

mysql80 13> SELECT CURDATE() - 3, CAST(CURDATE() - 3 AS DATE), CURDATE() - INTERVAL 3 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 3 | CAST(CURDATE() - 3 AS DATE) | CURDATE() - INTERVAL 3 DAY |
+---------------+-----------------------------+----------------------------+
|      20200699 | NULL                        | 2020-06-29                 |
+---------------+-----------------------------+----------------------------+
1 row in set, 1 warning (0.00 sec)

閑話休題。今年1年分の日付だけをDATE型で突っ込んだ cal というテーブルを用意した。そういえば今年は閏年だったのか。

mysql80 15> SELECT * FROM cal;
+------------+
| _date      |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |

..
| 2020-12-29 |
| 2020-12-30 |
| 2020-12-31 |
+------------+
366 rows in set (0.00 sec)

これで >= を使って比較してみる(「n日前から今日まで」みたいなイメージ)。
やはり月をまたぐとおかしくなるが、「本当に6月分のレコードが存在しなければこういう結果セットでもおかしくないよね」みたいな結果セットであるところがにくい。

mysql80 15> SELECT MIN(_date), CURDATE() - 1, CURDATE() - INTERVAL 1 DAY FROM cal WHERE _date >= CURDATE() - 1;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 1 | CURDATE() - INTERVAL 1 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 |      20200701 | 2020-07-01                 |
+------------+---------------+----------------------------+
1 row in set (0.00 sec)

mysql80 15> SELECT MIN(_date), CURDATE() - 2, CURDATE() - INTERVAL 2 DAY FROM cal WHERE _date >= CURDATE() - 2;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 2 | CURDATE() - INTERVAL 2 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 |      20200700 | 2020-06-30                 |
+------------+---------------+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql80 15> SELECT MIN(_date), CURDATE() - 3, CURDATE() - INTERVAL 3 DAY FROM cal WHERE _date >= CURDATE() - 3;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 3 | CURDATE() - INTERVAL 3 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 |      20200699 | 2020-06-29                 |
+------------+---------------+----------------------------+
1 row in set, 1 warning (0.00 sec)

なおこのパターン、体感として「30日」か「90日」を超えたあたりで気が付く人が多い。

30日以上過去の日付は MONTH(_date) の値が変わることが多くの場合期待されるが、数値型になって30を引かれても100の位は変わらないから、先月ぶんはまるまる引っ掛からない。これに違和感を覚えるのが「30日」のパターン。

mysql80 15> SELECT MIN(_date), CURDATE() - 60, CURDATE() - INTERVAL 60 DAY FROM cal WHERE _date >= CURDATE() - 60;
+------------+----------------+-----------------------------+
| MIN(_date) | CURDATE() - 60 | CURDATE() - INTERVAL 60 DAY |
+------------+----------------+-----------------------------+
| 2020-07-01 |       20200642 | 2020-05-03                  |
+------------+----------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

90日以上過去の日付を数値型にキャストするとだいたい 再びDATE型にキャスト可能な10の位の範囲 に戻ってきて、到底期待されない日付として扱われる。これに違和感をおぼえるのが「90日」のパターン。

mysql80 15> SELECT MIN(_date), CURDATE() - 90, CURDATE() - INTERVAL 90 DAY FROM cal WHERE _date >= CURDATE() - 90; -- 7/1で70だと上手くいかないので90にしたけど

+------------+----------------+-----------------------------+
| MIN(_date) | CURDATE() - 90 | CURDATE() - INTERVAL 90 DAY |
+------------+----------------+-----------------------------+
| 2020-06-12 |       20200612 | 2020-04-03                  |
+------------+----------------+-----------------------------+
1 row in set (0.00 sec)

こっちはワーニングすら出ないので、仕様を知らないと混乱するかも。

90日以外のパターンは SHOW WARNINGS でも拾える。
8.0なら performance_schema.events_errors_* でそのワーニングが発生しているかどうかも多少はあたりがつけられる(文字列を数値型にキャストしようとした時にも出るワーニングだから100%とは言えないけれども…

mysql80 15> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '20200699' for column '_date' at row 1 |
+---------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 15> SELECT * FROM performance_schema.events_errors_summary_global_by_error WHERE error_number= 1292;
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME               | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         1292 | ER_TRUNCATED_WRONG_VALUE | 22007     |             1110 |                 0 | 2020-07-02 11:56:07 | 2020-07-02 14:15:55 |
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
1 row in set (0.00 sec)

ワーニングは拾った方が後々楽だと思うけれど、自作しないとなかなか挟むのムズカシイんだよなぁ。。

2020/07/01

MySQL徹底入門 第4版の執筆に参加しました

TL;DR


掲題の通り、MySQL徹底入門の執筆に参加させていただきました。

第3版 を「読んで勉強していた」のが、第4版を書く側として声をかけていただいて、人生何があるかわからないものだなあ。

飽くまで「入門」なので、「実践ハイパフォーマンスMySQL」のような玄人向け成分はほぼありません。たぶん。

俺の担当は 6章(運用), 8章(レプリケーション), 9章(バックアップとリストア), 10章(プログラミングのうちPerl), 14章(逆引きMySQL辞典の一部) となっています。

6章(運用)

  • いわゆる MySQLを運用しましょう 的な内容ではない
  • 「運用するにはこれくらいの基礎知識は必要だよね?」な内容が詰まっています
    • SET GLOBAL で即時有効にならないパラメーターの話とか
    • my.cnfの読み込まれる順番の話とか
    • MySQLが「インデックスをどう使うからWHEREやORDER BY .. LIMITが速くなるのか」とか
      • 一部の方にご好評いただいている「トランプを使ったたとえ」が初めて文書化されています :)
    • レプリケーションを使ったローリングアップグレードの仕組みの話とか

8章(レプリケーション)

  • (非同期)レプリケーションとグループレプリケーションの「仕組み」に言及しています
    • 非同期レプリケーションではBinlog Dumpスレッド、I/Oスレッド、SQLスレッド、じゃあグループレプリケーションは? :)
      • というくらいのレベル感です
  • レプリケーション関連のオプションのオススメ設定や、設定時に考えることが割と列挙されています
  • なんとレプリケーションの構築手順が 全てMySQL Shellから構築する方法しか紹介されていません よってgtid_mode=ON も前提です。
    • 8.0のこの時代に「入門」する人は、もうMySQL Shellからの方法だけおぼえればいいんじゃないかな…

9章(バックアップとリストア)

  • 目新しいところは Cloneステートメントを使ったサンプルがあるところと、 mysqlbinlog --read-from-remote-server --stop-never --raw を使ったサンプルがあるところでしょうか
  • GTIDを前提に解説しているのでGTIDに不慣れな人には良い鴨

10章(プログラミング - Perl)

  • たぶん中括弧と代入の使い方がキモチワルイと思うんですが、これは(CにできてPerlにできない書き方を除いて)「 昔の MySQLのコーディング規約」に毒された俺の末路です。

14章(逆引きMySQL辞典)

  • 本編(?)とは違ったテンションでお送りする一問一答…とは限らないQ&Aっぽいコーナー
  • ここ書いてて本当に楽しかった章で、読み返しの時に「俺が書いたような気がしたものが別は他の人が書いた部分」「この人が書いただろうなと思ったものを書いたのは実は俺」みたいな錯覚がありました

参加できたことを嬉しく思いますし、全体通してもなかなか面白く読み応えのある本になっていますので、是非お手に取っていただければと。

俺が一番読んでて面白かったのは第11章の「文字コードと日本語環境」です :D