Description:
When synchronizing a model, and a table is renamed and then manually targeted, workbench is generating two alter statements. One to generate the table rename and the other to generate a foreign key constraint. The first alter command correctly renames the table. The second alter command to add the foreign key constraint ignores the table rename and uses the original table name to generate the foreign key addition.
How to repeat:
Using two script files below (init1.0.sql, init2.0.sql):
1. From mysql workben in an open model run synchronize with any source.
On Select sources screen:
2. Source: init2.0.sql
3. Destination: init1.0.sql
4. Updates to: update1.0-2.0.sql
On schema screen :
5. Make sure schema is checked
On differences screen:
6. Select updated_join table
7. Click Table Mapping
8. Select updated_join table
9. Change desired target table to original_join table
10. Click OK
11. Click Execute
12. See incorrect alter script generated, which has "ALTER TABLE `mydb`.`original_join`" instead of expected ALTER TABLE `mydb`.`updated_join` after table rename operation.
file init1.0.sql
------
-- MySQL Script generated by MySQL Workbench
-- Wed Sep 6 11:40:15 2017
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
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,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`data` (
`iddata` INT NOT NULL,
PRIMARY KEY (`iddata`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`original_join`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`original_join` (
`data_iddata` INT NOT NULL,
`joincol` VARCHAR(45) NULL,
INDEX `fk_original_join_data_idx` (`data_iddata` ASC),
PRIMARY KEY (`data_iddata`),
CONSTRAINT `fk_original_join_data`
FOREIGN KEY (`data_iddata`)
REFERENCES `mydb`.`data` (`iddata`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
------
file init2.0.sql
------
-- MySQL Script generated by MySQL Workbench
-- Wed Sep 6 11:44:42 2017
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
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,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`data`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`data` ;
CREATE TABLE IF NOT EXISTS `mydb`.`data` (
`iddata` INT NOT NULL,
PRIMARY KEY (`iddata`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`updated_join`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`updated_join` ;
CREATE TABLE IF NOT EXISTS `mydb`.`updated_join` (
`data_iddata` INT NOT NULL,
`joincol` VARCHAR(45) NULL,
`updated_joincol` VARCHAR(45) NULL,
INDEX `fk_original_join_data_idx` (`data_iddata` ASC),
PRIMARY KEY (`data_iddata`),
CONSTRAINT `fk_updated_join_data`
FOREIGN KEY (`data_iddata`)
REFERENCES `mydb`.`data` (`iddata`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
------
file update1.0-2.0.sql
------
-- MySQL Workbench Synchronization
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';
ALTER TABLE `mydb`.`original_join`
DROP FOREIGN KEY `fk_original_join_data`;
ALTER TABLE `mydb`.`original_join`
ADD COLUMN `updated_joincol` VARCHAR(45) NULL DEFAULT NULL AFTER `joincol`, RENAME TO `mydb`.`updated_join` ;
ALTER TABLE `mydb`.`original_join`
ADD CONSTRAINT `fk_updated_join_data`
FOREIGN KEY (`data_iddata`)
REFERENCES `mydb`.`data` (`iddata`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
------
Suggested fix:
Correctly account for table rename during script generation.
Description: When synchronizing a model, and a table is renamed and then manually targeted, workbench is generating two alter statements. One to generate the table rename and the other to generate a foreign key constraint. The first alter command correctly renames the table. The second alter command to add the foreign key constraint ignores the table rename and uses the original table name to generate the foreign key addition. How to repeat: Using two script files below (init1.0.sql, init2.0.sql): 1. From mysql workben in an open model run synchronize with any source. On Select sources screen: 2. Source: init2.0.sql 3. Destination: init1.0.sql 4. Updates to: update1.0-2.0.sql On schema screen : 5. Make sure schema is checked On differences screen: 6. Select updated_join table 7. Click Table Mapping 8. Select updated_join table 9. Change desired target table to original_join table 10. Click OK 11. Click Execute 12. See incorrect alter script generated, which has "ALTER TABLE `mydb`.`original_join`" instead of expected ALTER TABLE `mydb`.`updated_join` after table rename operation. file init1.0.sql ------ -- MySQL Script generated by MySQL Workbench -- Wed Sep 6 11:40:15 2017 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering 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,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`data` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`data` ( `iddata` INT NOT NULL, PRIMARY KEY (`iddata`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`original_join` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`original_join` ( `data_iddata` INT NOT NULL, `joincol` VARCHAR(45) NULL, INDEX `fk_original_join_data_idx` (`data_iddata` ASC), PRIMARY KEY (`data_iddata`), CONSTRAINT `fk_original_join_data` FOREIGN KEY (`data_iddata`) REFERENCES `mydb`.`data` (`iddata`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; ------ file init2.0.sql ------ -- MySQL Script generated by MySQL Workbench -- Wed Sep 6 11:44:42 2017 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering 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,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `mydb` ; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`data` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`data` ; CREATE TABLE IF NOT EXISTS `mydb`.`data` ( `iddata` INT NOT NULL, PRIMARY KEY (`iddata`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`updated_join` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`updated_join` ; CREATE TABLE IF NOT EXISTS `mydb`.`updated_join` ( `data_iddata` INT NOT NULL, `joincol` VARCHAR(45) NULL, `updated_joincol` VARCHAR(45) NULL, INDEX `fk_original_join_data_idx` (`data_iddata` ASC), PRIMARY KEY (`data_iddata`), CONSTRAINT `fk_updated_join_data` FOREIGN KEY (`data_iddata`) REFERENCES `mydb`.`data` (`iddata`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; ------ file update1.0-2.0.sql ------ -- MySQL Workbench Synchronization 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'; ALTER TABLE `mydb`.`original_join` DROP FOREIGN KEY `fk_original_join_data`; ALTER TABLE `mydb`.`original_join` ADD COLUMN `updated_joincol` VARCHAR(45) NULL DEFAULT NULL AFTER `joincol`, RENAME TO `mydb`.`updated_join` ; ALTER TABLE `mydb`.`original_join` ADD CONSTRAINT `fk_updated_join_data` FOREIGN KEY (`data_iddata`) REFERENCES `mydb`.`data` (`iddata`) ON DELETE NO ACTION ON UPDATE NO ACTION; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; ------ Suggested fix: Correctly account for table rename during script generation.