Description:
When creating multiple databases in a single workbench file, forward engineering a SQL script causes the INSERT commands to be placed after all databases and tables are defined. The problem is that the INSERT is executed after a different dtabase is used.
How to repeat:
Create a new document in Workbench
Add a table to the current Physical Schemata
Define the primary key column
Click on the inserts tab for the table an add a couple of rows.
Add a second database
Add a table
Now click on File->Export->Forward Engineer SQL Create Script
Open the resulting .sql file and you will find that the INSERT syntax is below the line for use mydb2 like this:
--- Begin snippet ---
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 DATABASE IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 ;
USE `mydb`;
-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
`idtable1` INT NOT NULL AUTO_INCREMENT ,
`table1Text` VARCHAR(45) NULL ,
PRIMARY KEY (`idtable1`) );
CREATE DATABASE IF NOT EXISTS `mydb2` ;
USE `mydb2`;
-- -----------------------------------------------------
-- Table `mydb2`.`table2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb2`.`table2` (
`idtable2` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`idtable2`) );
-- -----------------------------------------------------
-- Data for table `mydb`.`table1`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `table1` (idtable1, table1Text) VALUES (1, 'A');
INSERT INTO `table1` (idtable1, table1Text) VALUES (2, 'B');
INSERT INTO `table1` (idtable1, table1Text) VALUES (3, 'C');
COMMIT;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
--- End snippet ---
Suggested fix:
Add the database name to the table name (`mydb`.`table1`) as it is indicated in the comment just above the insert block.
