Bug #60566 No way to sort schemas on export

• Submitted: 21 Mar 2011 14:06
• Modified: 4 Sep 2012 21:06
• Reporter: Kevin Swift
• Status: Closed
• Category: MySQL Workbench: Modeling
• Severity: S3 (Non-critical)
• Version: 5.2.31CE
• OS: Any
• CPU Architecture: Any

[21 Mar 2011 14:06] Kevin Swift

Description:
There is no way to sort the order that schemas are exported.

For example if Schema A creates a view of a table on Schema B the export script fails because schema B needs to be created first so the view create command can succeed.

How to repeat:
Create two schemas A then B

Create a table 'table1' in schema B

Create a view of Table B.table in schema A

export a create script

Try to import the script

You will get an error such as

ERROR 1146 (42S02): Table 'B.X' doesn't exist

exported script.....

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `A` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
CREATE SCHEMA IF NOT EXISTS `B` ;
USE `A` ;

-- -----------------------------------------------------
-- Placeholder table for view `A`.`view1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A`.`view1` (`id` INT);

-- -----------------------------------------------------
-- View `A`.`view1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `A`.`view1`;
USE `A`;
CREATE  OR REPLACE VIEW `A`.`view1` AS  select * from B.table1;
;
USE `B` ;

-- -----------------------------------------------------
-- Table `B`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `B`.`table1` (
)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Suggested fix:
Ability to order the schema's in the GUI or set some form of priority.

Of course there is the possibility that there may be loops between schemas but the simple option of ordering should help a lot of people.

[21 Mar 2011 15:10] Valeriy Kravchuk

Sounds like a reasonable feature request.

[21 Mar 2011 15:25] Peter Laursen

The proposed implementation is not a solution for 'views built on views'.  WB will need to do as 'msyqldump' (and other decent client SQL-based backup softwares) do: create an 'intermediate* table for a view and later dropping it.  it is the only solution as long as 'view checks' cannot be turned off server side.

[4 Sep 2012 21:06] Philip Olson

Fixed as of the upcoming WB 5.2.45, and here's the changelog entry:

  It was not possible to sort schemas while exporting.

Thank you for the report.