Database backup/restore - dependent VIEW ordering

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
spongepuppy
Posts: 3
Joined: Sun 10 Dec 2017 23:29

Database backup/restore - dependent VIEW ordering

Post by spongepuppy » 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):

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);
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):

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);
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 :)

alexa

Re: Database backup/restore - dependent VIEW ordering

Post by alexa » Tue 12 Dec 2017 18:06

We will investigate this issue and will answer you as soon as possible.

Snap
Posts: 2
Joined: Thu 14 Dec 2017 06:25

Re: Database backup/restore - dependent VIEW ordering

Post by Snap » Thu 14 Dec 2017 06:44

Hi, we have the same issue and now I must make the backup via console :-(
I'd like it more comfortable :-)
Please take a look at it. Before version 7.3 it worked fine.

alexa

Re: Database backup/restore - dependent VIEW ordering

Post by alexa » Thu 14 Dec 2017 10:30

We will fix this issue in the next product version.

spongepuppy
Posts: 3
Joined: Sun 10 Dec 2017 23:29

Re: Database backup/restore - dependent VIEW ordering

Post by spongepuppy » Thu 14 Dec 2017 23:29

It wasn't clear from my original post, but this was also an issue in the late 7.2.x versions of dbForge as well - it's just taken me this long to work out why only some databases would restore without problems!

It's not a new issue with 7.3, but rather an small ongoing annoyance with an otherwise excellent product :)

Snap
Posts: 2
Joined: Thu 14 Dec 2017 06:25

Re: Database backup/restore - dependent VIEW ordering

Post by Snap » Thu 18 Jan 2018 08:15

Now with Version 7.3.137 all worked fine. Great job :-)

alexa

Re: Database backup/restore - dependent VIEW ordering

Post by alexa » Thu 18 Jan 2018 13:21

Thank you for updating us on this issue.

Post Reply