TL;DR
- DEFINERが存在しないSQL SECURITY DEFINERなビューと、そのビューを使ったビューがある時にMySQL ShellのloadDumpが転けることも転けないこともある
- mysqldumpからのリストアは100%転ける
- 転けるのが正しい気がするけど何故か転けずに完了してしまうのが気になったのでこのエントリ
参考: 日々の覚書: MySQLの論理バックアップにおける2段階のViewのリストア
下準備。
ビューのDEFINERになっているアカウントをDROPして、「DEFINERが存在しないビュー」「そのビューを使ったビュー」を作る。
（先にアカウントを作ってからDROPしないと、「DEFINERが存在しないビュー」までは無理矢理作れるけれども「そのビューを使ったビュー」が作れない）
CREATE USER dummy;
CREATE DATABASE d1;
CREATE definer=dummy VIEW d1.v1 AS SELECT 1;
CREATE VIEW d1.v2 AS SELECT * FROM d1.v1;
DROP USER dummy;
SHOW WARNINGS;
DROP USERした時にちゃんとワーニングは出る。
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------+
| Warning | 4005 | User 'dummy'@'%' is referenced as a definer account in a view. |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)
比較のためにmysqldumpも取っておく。ちなみに --single-transaction を省くと個別にLOCK TABLEしようとして転ける。
$ mysqldump -h172.17.0.2 -B d1 --single-transaction > /tmp/d1.sql
このダンプをリストアしようとすると必ず転ける。
$ mysql -h172.17.0.3 -vv < /tmp/d1.sql
..
--------------
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v2` AS select `v1`.`1` AS `1` from `v1` */
--------------
ERROR 1449 (HY000) at line 85: The user specified as a definer ('dummy'@'%') does not exist
Bye
MySQL ShellでdumpInstanceする。
$ mysqlsh -h172.17.0.2 -- util dumpInstance '/tmp/dump'
Please provide the password for 'root@172.17.0.2':
Save password for 'root@172.17.0.2'? [Y]es/[N]o/Ne[v]er (default No):
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 0 tables, 2 views.
3 out of 6 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
?% (0 rows / ?), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 0
Uncompressed data size: 0 bytes
Compressed data size: 0 bytes
Compression ratio: 0.0
Rows written: 0
Bytes written: 0 bytes
Average uncompressed throughput: 0.00 B/s
Average compressed throughput: 0.00 B/s
この時、 /tmp/dump/d1.json に入っている views の順番も多少関係する。v1が先に来ていれば転ける確率の方が高くなり、v2が先に来ていれば転けない可能性の方が高かった。
$ cat /tmp/dump/d1.json
{
"schema": "d1",
"includesDdl": true,
"includesViewsDdl": true,
"includesData": true,
"tables": [],
"views": [
"v2",
"v1"
],
"events": [],
"functions": [],
"procedures": [],
"basenames": {
"v2": "d1@v2",
"v1": "d1@v1"
}
}
--threads を大きめにすると転ける可能性が高くなる。
転けない時はこんな感じ。
$ mysqlsh -h172.17.0.4 -- util loadDump '/tmp/dump' --threads=10
Please provide the password for 'root@172.17.0.2':
Save password for 'root@172.17.0.2'? [Y]es/[N]o/Ne[v]er (default No):
Loading DDL and Data from '/tmp/dump' using 10 threads.
Opening dump...
Target is MySQL 8.0.35. Dump was produced from MySQL 8.0.35
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Executing common postamble SQL
Starting data load
?% (0 bytes / ?), 0.00 B/s, 0 / 0 tables done
Recreating indexes - done
No data loaded.
0 warnings were reported during the load.
転ける時はこんな感じ。
$ mysqlsh -h172.17.0.5 -- util loadDump '/tmp/dump' --threads=20
Please provide the password for 'root@172.17.0.2':
Save password for 'root@172.17.0.2'? [Y]es/[N]o/Ne[v]er (default No):
Loading DDL and Data from '/tmp/dump' using 20 threads.
Opening dump...
Target is MySQL 8.0.35. Dump was produced from MySQL 8.0.35
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
ERROR: Error executing DDL script for view `d1`.`v2`: MySQL Error 1449 (HY000): The user specified as a definer ('dummy'@'%') does not exist: /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`1` AS `1` from `v1` */
Executing view DDL - done
ERROR: The user specified as a definer ('dummy'@'%') does not exist
ビューを2段階リストアしているので、mysqldumpとかで直列にやった場合はこうなる。
CREATE VIEW v1 AS SELECT 1 AS 1;
CREATE VIEW v2 AS SELECT 1 AS 1;
DROP VIEW v1;
CREATE DEFINER=dummy VIEW v1 AS SELECT 1;
DROP VIEW v2;
CREATE VIEW v2 AS SELECT * FROM v1; -- ここでER_NO_SUCH_USER(=1449)で転ける
しかしパラレルにリストアすると、まれに v2 と v1 のタイミングがひっくり返る。MySQL Shellはd1.jsonのviewsに順番に読もうとする（んだと思う）ので、v2が先に来ているとよくこの順番になるんではないか。
CREATE VIEW v1 AS SELECT 1 AS 1;
CREATE VIEW v2 AS SELECT 1 AS 1;
DROP VIEW v2;
CREATE VIEW v2 AS SELECT * FROM v1; -- この時点ではv1は不正なビューではないので転けない
DROP VIEW v1;
CREATE DEFINER=dummy VIEW v1 AS SELECT 1; -- 不正なビューだがワーニング止まりなので転けはしない
なので、転けたり転けなかったりするという違いが生まれている模様。
たぶん、動作としては常に転けるのが正しいとは思う。
