今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQL EXPLAINがデータベースを破壊する可能性がある場合の日。

目次

とある日

Uncommon Sense MySQL – When EXPLAIN Can Trash Your Database

とあるバグ報告の記事見つけた。

Google翻訳にかけた内容。

クエリでEXPLAINを実行するとデータベースが変更される可能性があるかどうかを尋ねると、おそらくNOと表示されます。それは常識です。EXPLAINは、クエリを実行するのではなく、クエリがどのように実行されるかを示す必要があるため、データを変更することはできません。

残念ながら、これは常識がMySQLに適用されない場合です(この記事の執筆時点ではMySQL 8.0.21以前のバージョン)–このバグが示すように、EXPLAINが実際にデータベースを変更できるエッジケースがあります。

どうやら、DMLを実行するストアドファンクションをEXPLAINで実行計画を見ると、実行されてしまうというバグらしい。

オモシロイと思ったのでやってみる。

環境

Oracle VM VirtualBox

バージョン 6.0.14 r133895 (Qt5.6.2)

OS

mysql> \! cat /etc/os-release
NAME="CentOS Linux"
VERSION="8 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

MySQL

mysql> status;
--------------
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)

Connection id:          27
Current database:       hobby
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.21 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 11 hours 56 min 31 sec

Threads: 5  Questions: 1817  Slow queries: 0  Opens: 409  Flush tables: 3  Open tables: 312  Queries per second avg: 0.042

実践

  1. テーブル作成
  2. データ挿入
  3. テーブル確認
  4. ストアドファンクション作成
  5. EXPLAINでストアドファンクション実行
  6. テーブル確認

の手順で実行していく。

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use hobby
Database changed

mysql>create table t1(st char(50));
mysql> INSERT into t1 values("1");
Query OK, 1 row affected (0.12 sec)

mysql> show tables;
+-----------------+
| Tables_in_hobby |
+-----------------+
| max_chcek       |
| mt              |
| orde            |
| t1              |
+-----------------+
4 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| st   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE FUNCTION `cleanup`() RETURNS char(50) CHARSET latin1
    ->     DETERMINISTIC
    -> BEGIN
    -> delete from t1;
    -> RETURN 'OK';
    -> END $$
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;
mysql>  explain select * from (select cleanup()) as t1clean;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.09 sec)

mysql>  select * from t1;
Empty set (0.00 sec)

実際にデータがなくなっているのが確認できた。

そもそも、EXPLAINSELECT以外に使ったことないので、最後の方にINSERTDELETEEXPLAINで実行してみる。

一般ログ確認

一般ログで実際に実行されたSQLのログを確認してる。

EXPLAIN DML LOG

mysql> select * from t1;
+------+
| st   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> explain insert into t1 values("2");
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1;
+------+
| st   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)
2020-11-04T17:19:02.334894Z        15 Query     show tables
2020-11-04T17:19:06.238490Z        15 Query     select * from t1
2020-11-04T17:19:29.975423Z        15 Query     explain insert into t1 values("2")
2020-11-04T17:19:32.840570Z        15 Query     select * from t1

insertは実行されていないことが確認できた。

次はストアドファンクションのログ確認。

EXPLAIN FUNCTION LOG

mysql>  explain select * from (select cleanup()) as t1clean;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (1.07 sec)

mysql> select * from t1;
Empty set (0.00 sec)
2020-11-04T17:19:06.238490Z        15 Query     select * from t1
2020-11-04T17:19:29.975423Z        15 Query     explain insert into t1 values("2")
2020-11-04T17:19:32.840570Z        15 Query     select * from t1
2020-11-04T17:21:17.335484Z        15 Query     explain select * from (select cleanup()) as t1clean
2020-11-04T17:21:17.338968Z        15 Query     delete from t1
2020-11-04T17:21:26.108609Z        15 Query     select * from t1

delete文は実行され、データも消えている。

一般ログにもきちんと記録されていたことが確認できた。

EXPLAIN DML TEST

EXPLAIN概要

クエリー実行プランの理解

WHERE 句内のテーブル、カラム、インデックス、および条件の詳細に応じて、MySQL オプティマイザは SQL クエリーに含まれるルックアップを効率的に実行するための多くの技法を考慮します。巨大なテーブルに対するクエリーは、すべての行を読み取らなくても実行でき、複数のテーブルを含む結合は、行のすべての組み合わせを比較しなくても実行できます。オプティマイザがもっとも効率的なクエリーを実行するために選択する操作のセットは、「クエリー実行プラン」と呼ばれ、EXPLAIN プランとも呼ばれます。目的は、クエリーが適切に最適化されていることを示す EXPLAIN プランの側面を認識し、非効率的な操作が見られた場合に、プランを改善するための SQL 構文とインデックス設定技法を学ぶことです。

バグを紹介して記事では、「EXPLAINはデータを変更することはできません」と記述したが、ドキュメントを読んでみたがEXPLAIN で実行したDMLが適用されないといった記述は見つからなかった。

EXPLAINは、実行計画を見ることができる便利なものだ。

SELECT以外で使ったことなかった。

SELECT以外にもDELETEINSERTUPDATEREPLACEでも使用できる。

SELECT が唯一の説明可能

INSERT

mysql> select * from t1;
+------+
| st   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> explain insert into t1 values("1");
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1;
+------+
| st   |
+------+
| 1    |
+------+
1 row in set (0.01 sec)

DELETE

mysql> explain delete from t1 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | DELETE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1;
+------+
| st   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

UPDATE

mysql> explain update t1 set st = "2";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | UPDATE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1;
+------+
| st   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

DELETEINSERTUPDATEの3つをEXPLAINで実行してみたがテーブルは変化なし。

ストアドファンクションを通してではなく実行すると、テーブルには変化が起きないことが判明した。

なかなか、面白いバグだった。

EXPLAINをもっと詳しく見ればもっと他のことがわかりそう。

もっと複雑なストアドファンクションやストアドプロシージャでも、テーブルが破壊されるか試してみたかったが、

ストアドファンクションに不慣れすぎてやめました。

もっと、MySQLと仲良くならなくては。

SELECT以外でEXPLAINを使わないから自分は影響ないけど、

INSERTとかの実行計画見ることとかあるのかな。

そこが一番の疑問となった。