MySQLの論理バックアップにおける2段階のViewのリストア

TL;DR

• mysqldump とかが CREATE VIEW v1 AS SELECT 1 AS num, 1 AS val とかいう一見VIEW定義となんの関係もない CREATE VIEW 文を吐く理由

---

mysqldumpは単にアルファベット順にテーブルやビューの定義を取得するので、シンプルに直接 CREATE VIEW を書いてしまうと順番によってはリストアに転ける。

mysql80 38> CREATE TABLE t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.04 sec)

mysql80 38> INSERT INTO t1 VALUES (1, 'one'), (2, 'two');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql80 38> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql80 38> CREATE VIEW a_v1 AS SELECT val FROM v1 WHERE num = 1;
Query OK, 0 rows affected (0.02 sec)

mysql80 38> SHOW TABLES;
+--------------+
| Tables_in_d2 |
+--------------+
| a_v1         |              <-- ビューv1を参照しているのでアルファベット順にリストアすると転ける
| t1           |
| v1           |
+--------------+
3 rows in set (0.00 sec)

mysql80 38> use d3
Database changed

mysql80 38> CREATE VIEW a_v1 AS SELECT val FROM v1 WHERE num = 1;
ERROR 1146 (42S02): Table 'd3.v1' doesn't exist

なのでこれを避けるために、

• Base Tableの CREATE TABLE と ダミーの CREATE VIEW を流す
• ↑が流れ終わった後に本物の CREATE VIEW を流す
  という2ステップで実行している。

$ mysqldump80 d2
..
--
-- Temporary view structure for view `a_v1`
--

DROP TABLE IF EXISTS `a_v1`;
/*!50001 DROP VIEW IF EXISTS `a_v1`*/;
/*!50001 CREATE VIEW `a_v1` AS SELECT
 1 AS `val`*/;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Temporary view structure for view `v1`
--

DROP TABLE IF EXISTS `v1`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50001 CREATE VIEW `v1` AS SELECT
 1 AS `num`,
 1 AS `val`*/;

--
-- Final view structure for view `a_v1`
--

/*!50001 DROP VIEW IF EXISTS `a_v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `a_v1` AS select `v1`.`val` AS `val` from `v1` where (`v1`.`num` = 1) */;

--
-- Final view structure for view `v1`
--

/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v1` AS select `t1`.`num` AS `num`,`t1`.`val` AS `val` from `t1` */;

..

1. 最初のTemporary view structureの時点では CREATE VIEW では固定値を列挙するだけなのでビューのビュー ( a_v1 )でも依存関係は問題はなくリストアできて
2. Final view structureの時点でダミーを DROP VIEW して本来の定義を CREATE VIEW することで復元できる、たとえ順番が入れ違って a_v1 が先にリストアされても、カラムの定義まではダミーの方で作成済みなのでビューのビューでもリストアできる

という仕組み。

意外とよくできてる。