Database backup/restore - dependent VIEW ordering
Posted: Mon 11 Dec 2017 22:17
Hi all,
We have a number of MySQL/MariaDB databases containing views that depend on other views.
This causes problems when we come to restore those databases, since dbForge exports views in lexical order. Here is a backup of a trivial example generated from dbForge 7.3.131 (with comments and extraneous elements stripped for clarity):
mysqldump generates blank tables as placeholders for views initially, subsequently replacing them with the views - again, in lexical order, but allowing the restore to proceed without modifying the dumped SQL (with comments and extraneous statements stripped for clarity):
Is there a setting in the database backup interface that enables this? If so, I've been unable to find it! If not, please consider this a feature request :)
We have a number of MySQL/MariaDB databases containing views that depend on other views.
This causes problems when we come to restore those databases, since dbForge exports views in lexical order. Here is a backup of a trivial example generated from dbForge 7.3.131 (with comments and extraneous elements stripped for clarity):
Code: Select all
USE view_example;
CREATE TABLE basetable (
id int(11) DEFAULT NULL,
name varchar(50) DEFAULT NULL,
description varchar(250) DEFAULT NULL
)
ENGINE = ARIA
CHARACTER SET utf8
COLLATE utf8_general_ci;
CREATE VIEW a_view AS
SELECT
`b_view`.`id` AS `id`,
`b_view`.`name` AS `name`,
`b_view`.`description` AS `description`
FROM `b_view`
WHERE (`b_view`.`id` > 20);
CREATE VIEW b_view AS
SELECT
`basetable`.`id` AS `id`,
`basetable`.`name` AS `name`,
`basetable`.`description` AS `description`
FROM `basetable`
WHERE (`basetable`.`id` > 10);
Code: Select all
DROP TABLE IF EXISTS `a_view`;
DROP VIEW IF EXISTS `a_view`;
CREATE TABLE `a_view` (
`id` tinyint NOT NULL,
`name` tinyint NOT NULL,
`description` tinyint NOT NULL
) ENGINE = MYISAM;
DROP TABLE IF EXISTS `b_view`;
DROP VIEW IF EXISTS `b_view`;
CREATE TABLE `b_view` (
`id` tinyint NOT NULL,
`name` tinyint NOT NULL,
`description` tinyint NOT NULL
) ENGINE = MYISAM;
DROP TABLE IF EXISTS `basetable`;
CREATE TABLE `basetable` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`description` varchar(250) DEFAULT NULL
) ENGINE = ARIA DEFAULT CHARSET = utf8 PAGE_CHECKSUM = 1;
DROP TABLE IF EXISTS `a_view`;
DROP VIEW IF EXISTS `a_view`;
CREATE ALGORITHM = UNDEFINED
VIEW `a_view`
AS
SELECT
`b_view`.`id` AS `id`,
`b_view`.`name` AS `name`,
`b_view`.`description` AS `description`
FROM `b_view`
WHERE (`b_view`.`id` > 20);
DROP TABLE IF EXISTS `b_view`;
DROP VIEW IF EXISTS `b_view`;
CREATE ALGORITHM = UNDEFINED VIEW `b_view`
AS
SELECT
`basetable`.`id` AS `id`,
`basetable`.`name` AS `name`,
`basetable`.`description` AS `description`
FROM `basetable`
WHERE (`basetable`.`id` > 10);