mita2 database life

主にMySQLに関するメモです

MySQL mysql コマンドからシェルを呼び出せなくする小技

mysql コマンドでは !system によって、シェルを呼び出すことができます。

mysql> \! uname
Linux

mysql> system uname
Linux

実装はシンプルで、標準ライブラリの system 関数が利用されています。

static int
com_shell(String *buffer MY_ATTRIBUTE((unused)),
          char *line MY_ATTRIBUTE((unused)))
{
  char *shell_cmd;

  /* Skip space from line begin */
  while (my_isspace(charset_info, *line))
    line++;
  if (!(shell_cmd = strchr(line, ' ')))
  {
    put_info("Usage: \\! shell-command", INFO_ERROR);
    return -1;
  }
  /*
    The output of the shell command does not
    get directed to the pager or the outfile
  */
  if (system(shell_cmd) == -1)
  {
    put_info(strerror(errno), INFO_ERROR, errno);
    return -1;
  }
  return 0;
}

system を使えなくする

LD_PRELOAD を使って、標準ライブラリの system 関数を差し替えてしまいましょう。 ダミーの system 関数を作ります。

#include <stdlib.h>
#include <stdio.h>

int system(const char *command) {
    fprintf(stderr, "system command is prohibited\n");
    return 0;
}
$ gcc -g -Wall  -fPIC -shared -o nosystem.so nosystem.c
$ LD_PRELOAD=$PWD/nosystem.so mysql
mysql> \! uname
system command is prohibited

mysql> system uname
system command is prohibited

使えなくなりました。めでたしめでたし(LD_PRELOAD を強制する方法は、省略。。。)

MySQL 8.0 で謎のEXPLAIN結果が出なくなってた

時々、頭の中でMySQLの気持ちになって考えた実行計画と違うものが出力されるときがあるんですよね。 まぁ、実用上、問題になることはなかったので、「MySQL ヨクワカランなー」と思って、スルーしてました。

ところが、最近、MySQL 8.0 をいじってたら、イメージしている実行計画が表示されてることに気づきました。ワイワイ!

MySQL 5.7 の謎の Index Condition Pushdown 表示

今回のお題のテーブルはこちら(Employees Sample DBに 適当にカラム c1 を追加したもの)

mysql> SHOW CREATE TABLE salaries \G
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `idx1` (`to_date`,`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

to_datec1 にインデックスを貼ってあります。

説明のため、ベースとなる実行計画を示します。 idx1 インデックスから to_datec1 の条件にマッチするレコードを引き当ててます。ExtraNULL です。 これは違和感のない実行計画です。

mysql> EXPLAIN SELECT * FROM salaries 
  WHERE to_date = '2010-10-10' AND c1 = 0 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ref
possible_keys: idx1
          key: idx1
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

このクエリに ORDER BY salary をつけると、ExtraUsing index condition が出現します。

mysql> EXPLAIN SELECT * FROM salaries
   WHERE to_date = '2010-10-10' AND c1 = 0 ORDER BY salary\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ref
possible_keys: idx1
          key: idx1
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

to_date = '2010-10-10' AND c1 = 0 は、等価比較なので、インデックスコンディションプッシュダウン で解決する必要はありません。 その証拠にベースのクエリでは、Using index condtion は出てない。

ベースのクエリにソートが加わるのみなので、ExtraUsing filesort だけが追加されるんじゃないの・・・?

MySQL 8.0 の実行計画

同じ条件で、MySQL 8.0 の実行計画を見てみます。

mysql> EXPLAIN SELECT * FROM salaries
    ->    WHERE to_date = '2010-10-10' AND c1 = 0 ORDER BY salary\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ref
possible_keys: idx1
          key: idx1
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

謎の Using index condtion がなくなったぞー👏👏👏

ちなみに、実行速度の変化は、未確認です。そもそも、EXPLAIN表示上のだけの問題だった可能性もありそう。

MySQL The client was disconnected by the server because of inactivity の対処方法

3ヶ月ブログをサボっておりました。。。リハビリがてらのエントリー。

The client was disconnected by the server because of inactivity エラー

MySQL 8.0.24 からタイムアウト(wait_timeout / interactive_timeout)時のエラーメッセージがに変更になりました。

8.0.24 以前 (Before)

MySQL server has gone away もしくは Lost connection to MySQL server during query

8.0.24 以降 (After)

The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.

以前のエラーメッセージは単に接続が切れたことのみを示しています。 タイムアウトした場合も、ネットワークが切れた場合も、max_allowed_packet が小さすぎる場合も・・・「MySQL server has gone away」エラーでした。 原因は別途切り分ける必要がありました。

8.0.24 以降では、wait_timeout / interactive_timeout が原因でタイムアウトした場合は、その旨をエラーメッセージで明確に表示してくれるようになりました。 わかりやすい!

対処方法

The client was disconnected by the server because of inactivity エラー はアイドル時間が wait_timeout / interactive_timeout 値を超えたことで発生します。 「wait_timeout (CLIの場合、interactive_timeout)の設定を長くする」もしくは「アイドル時間が長くならないよう処理の流れを見直す」ことで解決します。

mysql> SET GLOBAL wait_timeout=XXX

参考

先月、MySQL8.0.24リリースノートでわいわい言う勉強会 に参加して、8.0.24 の変更点についてLTしてきました。

参考書籍

MySQL Orchestrator RecoveryPeriodBlockSeconds と FailureDetectionPeriodBlockMinutes の違い

Orchestrator

orchestrator は マスターの障害検知およびレプリカのマスター昇格(フェイルオーバー)を自動で行うソフトウェアです。 MySQLのマスター昇格といえば、MHAがデファクトスタンダードでしたが、MHAはメンテナンスモードになって久しい・・・ということで、最近は orchestrator が使われることが多い印象です。メルカリさんで使われたりしているようです。

github.com

アンチ フラッピング機構 RecoveryPeriodBlockSeconds

orchestrator にはアンチフラピング機構が備わってます。orchestratorにおける、フラッピングとは、フェイルオーバーが繰り返し短時間に発生する状況を指します。

障害が短期間に連続して発生する状況は、何か予期せぬ事態が起きている可能性が高いです。このような状況では、ヘタに自動的にフェイルオーバーするよりは、人間がしっかりと状況を確認し、対応するほうが好ましいでしょう。

orchestrator では一度、リカバリ(レプリカのマスター昇格)を実行したら、RecoveryPeriodBlockSeconds で指定した期間(秒)経過するまで、リカバリが発動しないように制御されています。

orchestrator avoid flapping (cascading failures causing continuous outage and elimination of resources) by introducing a block period, where on any given cluster, orchesrartor will not kick in automated recovery on an interval smaller than said period, unless cleared to do so by a human. https://github.com/openark/orchestrator/blob/master/docs/topology-recovery.md

FailureDetectionPeriodBlockMinutes

もう1つ似たようなパラメータとして、FailureDetectionPeriodBlockMinutes があります。 障害を検知したら、FailureDetectionPeriodBlockMinutes で指定した期間(分)、障害の検知を行いません*1

障害検知が行われなければ、リカバリも行われないので、「RecoveryPeriodBlockSeconds と同じやん・・・*2」と思いましたが、以下の記述で納得できました。

Detection does not always lead to recovery. There are scenarios where a recovery is undesired: https://github.com/openark/orchestrator/blob/master/docs/failure-detection.md

orchestrator では、障害の検知(FailureDetection)= リカバリ(マスター昇格)発動 とは限りません。

orchestrator は 障害検知からマスター昇格完了までの各ポイントで、任意のスクリプトを実行するためのHookが設けられています。 PreFailoverProcesses で指定したスクリプトで、エラーを返すことで、リカバリの発動を止めることができます。

フェイルオーバーに関する Hook)

* OnFailureDetectionProcesses
  * 障害検知時
* PreFailoverProcesses
  * フェイルオーバー発動前
* PostMasterFailoverProcesses
  * マスターのフェイルオーバー後
* PostFailoverProcesses
  * レプリカも含めて、全てのフェイルオーバーが完了したタイミング

--

FailureDetectionPeriodBlockMinutes の説明には、anti-spam mechanism と書いてあります。 これは、OnFailureDetectionProcesses フックで、障害通知を行っているようなケースを想定して、「アンチスパム」と表現しているのでしょう。

FailureDetectionPeriodBlockMinutes is an anti-spam mechanism that blocks orchestrator from notifying the same detection again and again and again.

まとめ

  • RecoveryPeriodBlockSeconds は短期間にフェイルオーバーが連続発生しないようにするためのパラメータ
  • 障害の検知(FailureDetection)= リカバリ(マスター昇格)発動 とは限らない
  • FailureDetectionPeriodBlockMinutes は 障害継続時 にOnFailureDetectionProcesses で指定したスクリプトを実行する間隔

僕が本当に知りたかったこと

ごく短時間のダウンではフェイルオーバーを発動させたくないんだが、Hookで作り込むしかないんだろうか(誰か教えてください)

*1:チェックする間隔はまた別のパラメータなので注意

*2:スマートスタイルの記事でも、FailureDetectionPeriodBlockMinutes がフラッピング対策と説明されているので、同じように考えてしまう人は多そう http://blog.s-style.co.jp/2018/11/2875/

MySQL 論理削除に関する個人的見解まとめ

技術顧問や講演の場で、論理削除について見解を聞かれる場面がよくあります。アプリケーション開発者の方にとって、身近なデータベースの疑問なんでしょうね。

しっかり言語化できてなかったので、ブログに書いておきます。

論理削除をどう考えるかは、諸派あるんだろうと思ってます。 自分の意見が正しいと言うつもりはありませんし、求めらる要件や環境で結論が変わることもあると思います。

論理削除とは(おさらい)

以下のように削除日付(deleted_at)のカラムを設けて、レコードの有効・無効を管理する手法を指します。

id name deleted_at
1 aaaaaa NULL
2 bbbbbb 2021-01-12 10:00:00

論理「削除」と呼びますが、実際は、非表示フラグやレコードの状態遷移を表していたり。よくよく考えると「削除」でないパターンも含まれています。逆に、DELETE して完全にデータを削除することを「物理削除」と呼びます。

誤操作の取り消しが、論理削除を採用する代表的な目的でしょう。

誤操作の保険としてはバックアップもありますが、バックアップは、データベース全体の時間軸を戻すことを意図したものです。 特定のレコードに絞って時間軸を戻すのには適していません。

論理削除のデメリットと言われているもの

バグの温床になりやすい

WHERE 句に deleted_at IS NULL を付けまくらないといけないというやつですね。deleted_at IS NULL を付け忘れると削除済みデータが見えてしまうと。

パフォーマンスへの影響

本来必要のないレコードが保存されることで、そのぶんオーバーヘッドになる。

ぼくの見解

論理削除をそれほど、強いアンチパターンだとは考えてません。他のアンチパターンと比較すると問題になる確率も低いように思います。 自分の周囲では論理削除が問題になっているケースをほとんど見たことがないです。

もちろん物理削除のほうがデータがクリーンで好ましいと思います。ただ、論理削除を撲滅するために、大きな労力を割く必要は感じないです。

パフォーマンス影響については、論理削除の有無より他の要素に左右されるケースがほとんどだと思います。

むしろ、現場では、貯め続けていると、そのうち明らかに問題になるデータ量なのに、ノーガードで運用されているケース に出くわすほうが多いです。論理削除云々を気にする以前に、データ量の見積もりと、適切なデータストアの選定をしっかりしてほしい。。。*1

*1:あと、物理削除バッチの作りが悪くて、爆発することも多い

MySQL GISのSRID 0(直行座標)を理解する

MySQLで20年ほど遊んでいますが・・・知らないことも、まだまだあります。 GIS(Geographic Information System、地理・空間情報) はそのうちの1つです。ということで、ほとんど触れたことのない、GISを入門してみます。

なお、このエントリーは MySQL 8.0 を前提としています。

デフォルトのSRIDは0(直行座標)

デフォルトは SRID 0です。 SRID0 は直行座標(Cartesian plane=デカルト座標)です。実際の地球ではなく、数学の授業で出てきたようなXY座標上の地点を表します。

SRID 0 represents an infinite flat Cartesian plane with no units assigned to its axes.

https://dev.mysql.com/doc/refman/8.0/en/spatial-function-argument-handling.html

ST_Distance 関数を使って、直行座標であることを確認してみます。

原点(0,0) から (10, 0) の距離は、10です。

mysql> SET @a = ST_GeomFromText('POINT(0 0)', 0);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = ST_GeomFromText('POINT(0 10)', 0);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ST_Distance(@a, @b);
+---------------------+
| ST_Distance(@a, @b) |
+---------------------+
|                  10 |
+---------------------+
1 row in set (0.00 sec)

原点(0,0) から (10, 10) の距離は、14.142135.. (ルート2 * 10)です。

mysql> SET @c = ST_GeomFromText('POINT(10 10)', 0);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ST_Distance(@a, @c);
+---------------------+
| ST_Distance(@a, @c) |
+---------------------+
|  14.142135623730951 |
+---------------------+
1 row in set (0.00 sec)

SRID 4326を指定すると、地球上の緯度・経度になりST_Distanceの結果(距離)は SRID0 とは大きく異なる値になりました。

mysql> SET @g1 = ST_GeomFromText('POINT(0 0)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @g2 = ST_GeomFromText('POINT(10 10)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
|    1565092.2768577514 |
+-----------------------+
1 row in set (0.00 sec)

SRID0は地球上の緯度・経度ではないので、経度としてはありえない座標(経度は180度まで)でも指定できます。

-- ref) https://sakaik.hateblo.jp/entry/20191229/mysql_gis_axis_order_srid_0
mysql> SET @d = ST_GeomFromText('POINT(190 10)', 0);
Query OK, 0 rows affected (0.00 sec)

-- SRID を指定して、地球上の地点を表すとエラー
mysql> SET @d = ST_GeomFromText('POINT(10 190)', 4326);
ERROR 3616 (22S02): Longitude 190.000000 is out of range in function st_geomfromtext. It must be within (-180.000000, 180.000000].

ところで、ST_Distance 関数では距離を表す単位を変更できるそうです。デフォルトの単位はメートルです。

next4us-ti.hatenablog.com

SRID0 で 単位を指定してみます。抽象的な座標には単位が存在しないため、単位が変換できない旨のエラーが出ますね。

mysql> SELECT ST_Distance(@a, @c, 'centimetre');
ERROR 3882 (SU001): The geometry passed to function st_distance is in SRID 0, which doesn't specify a length unit. Can't convert to 'centimetre'.

あわせて読みたい

sakaik さんのGIS関連のエントリーが非常に参考になります

sakaik.hateblo.jp

MySQL 1レコード 8000バイトの壁を確認するSQL ROW_FORMAT=DYNAMIC 編

InnoDBのレコード長の限界

InnoDBのデフォルトのページサイズは、16Kです。この場合、最大レコード長は約8000バイトです。マニュアルで「約8000バイト」と曖昧な言い回しになっているのは、データの保存に使える領域以外にメタデータを保存する領域が必要だからでしょう。

可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除き、行の最大長はデータベースページの半分より少し短くなります。つまり、デフォルトページサイズの 16K バイトでは、行の最大長が約 8000 バイトになります。

https://dev.mysql.com/doc/refman/5.6/ja/column-count-limit.html

また、「可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除き」は可変長カラムはオーバーフローページに分割して保存されることを指し示しています。 可変長カラムは複数のページに分割して保存されるため、可変長カラムを含むテーブルでは、8000バイト以上保存できます。

-- 固定長のCHARだと、250 x 33 = 8250 バイトでエラー
mysql> CREATE TABLE table_with_char
(pk int primary key,
   c1 CHAR(250) NOT NULL,
   c2 CHAR(250) NOT NULL,
   c3 CHAR(250) NOT NULL,
:
   c33 CHAR(250) NOT NULL
) ROW_FORMAT=DYNAMIC CHARACTER SET 'latin1';
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

-- varchar にすれば、一行8000バイトの壁を越えられる
mysql> CREATE TABLE table_with_varchar
(pk int primary key,
   c1 VARCHAR(250) NOT NULL,
   c2 VARCHAR(250) NOT NULL,
   c3 VARCHAR(250) NOT NULL,
:
   c33 VARCHAR(250) NOT NULL
) ROW_FORMAT=DYNAMIC CHARACTER SET 'latin1';
Query OK, 0 rows affected (0.02 sec)

では、可変長カラムであれば、いくらでも保存できるかというとそうではありません。 可変長カラムであっても、ポインタ分の 20バイト 消費します。

ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED で作成されたテーブルでは、カラムの長さおよび行全体の長さによっては、BLOB、TEXT、または VARCHAR カラムの値が完全にオフページに格納される場合もあります。オフページに格納されるカラムでは、クラスタ化されたインデックスのレコードに、オーバーフローページへの 20 バイトのポインタのみがカラムごとに 1 つずつ含まれます。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression-internals.html

また、TEXT/BLOB 型では、実際のデータの長さによっては、20バイトではなく、40バイト消費するケースもあります。

ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED で作成されたテーブルでは、40 バイト以下の TEXT および BLOB カラムは、常にインラインに格納されます。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression-internals.html

ROW_FORMAT=DYNAMIC で 8000バイトの壁を確認するSQL

さて、上記をふまえて、各テーブルのレコード長(オーバーフローページ分を除く)を表示するSQLを考えてみます。

こちらの記事のSQLを参考にさせて頂きました。この記事のSQLROW_FORMAT=COMPACT 用のSQLです。 ROW_FORMAT=DYNAMIC 用に上記の可変長カラムのオーバーフローページを考慮して微修正しました。

qiita.com

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
SUM(
    CASE
      WHEN DATA_TYPE = 'tinyint' then 1
      WHEN DATA_TYPE = 'smallint' then 2
      WHEN DATA_TYPE = 'mediumint' then 3
      WHEN DATA_TYPE = 'int' then 4
      WHEN DATA_TYPE = 'bigint' then 8

      WHEN DATA_TYPE = 'float' then 4
      WHEN DATA_TYPE = 'double' then 8

      WHEN DATA_TYPE = 'decimal' then 0
          + FLOOR((NUMERIC_PRECISION - NUMERIC_SCALE) / 9) * 4
          + CEIL((NUMERIC_PRECISION - NUMERIC_SCALE) % 9 / 2)
          + FLOOR((NUMERIC_SCALE) / 9) * 4
          + CEIL((NUMERIC_SCALE) % 9 / 2)

      WHEN DATA_TYPE = 'char' then CHARACTER_OCTET_LENGTH
      WHEN DATA_TYPE = 'binary' then CHARACTER_OCTET_LENGTH

      -- varchar/varbinary ではポインタ分 20BYTE
      WHEN DATA_TYPE = 'varchar' then 20
      WHEN DATA_TYPE = 'varbinary' then 20

      -- text/blob では 40BYTE
      WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?text$' then 40
      WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?blob$' then 40

      WHEN DATA_TYPE = 'datetime' then 8
      WHEN DATA_TYPE = 'date' then 3
      WHEN DATA_TYPE = 'time' then 3
      WHEN DATA_TYPE = 'year' then 1
      WHEN DATA_TYPE = 'timestamp' then 4

      WHEN DATA_TYPE = 'enum' then IF((CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH) < 256, 1, 2)

      ELSE NULL
    END
  ) as SIZE
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') 
GROUP BY TABLE_SCHEMA, TABLE_NAME ORDER BY TABLE_SCHEMA, TABLE_NAME;

実行結果例)

+--------------+--------------------+------+
| TABLE_SCHEMA | TABLE_NAME         | SIZE |
+--------------+--------------------+------+
| test         | col1017            | 1020 |
| test         | t2                 |   44 |
| test         | table_with_varchar |  664 |
| test         | text_x_197         | 7884 |
| test         | text_x_200         | 7884 |
| test         | text_x_null197     | 7884 |
| test         | var1               |   20 |
+--------------+--------------------+------+
7 rows in set (0.03 sec)

ソースコード

MySQL 5.6 では storage/innobase/dict/dict0dict.ccdict_index_too_big_for_treeCREATE TABLE 時のレコード長のチェックがされてました。 rec_max_size の値を追いかけると、各型が何バイトで計算されるかわかりました。

/****************************************************************//**
If a record of this index might not fit on a single B-tree page,
return TRUE.
@return TRUE if the index record could become too big */
static
ibool
dict_index_too_big_for_tree(
/*========================*/
    const dict_table_t* table,      /*!< in: table */
    const dict_index_t* new_index)  /*!< in: index */
{
    ulint   zip_size;
    ulint   comp;
    ulint   i;
    /* maximum possible storage size of a record */
    ulint   rec_max_size;
    /* maximum allowed size of a record on a leaf page */
    ulint   page_rec_max;
    /* maximum allowed size of a node pointer record */
    ulint   page_ptr_max;

<snip>


add_field_size:
        rec_max_size += field_max_size;

        /* Check the size limit on leaf pages. */
        if (UNIV_UNLIKELY(rec_max_size >= page_rec_max)) {

            return(TRUE);
        }

        /* Check the size limit on non-leaf pages.  Records
        stored in non-leaf B-tree pages consist of the unique
        columns of the record (the key columns of the B-tree)
        and a node pointer field.  When we have processed the
        unique columns, rec_max_size equals the size of the
        node pointer record minus the node pointer column. */
        if (i + 1 == dict_index_get_n_unique_in_tree(new_index)
            && rec_max_size + REC_NODE_PTR_SIZE >= page_ptr_max) {

            return(TRUE);
        }