Description:
The workbench fails to synchronize views if the view already exists. If the view exists, then it is dropped but not replaced.
How to repeat:
First synchronize of the model always works. Then if you synchronize a second time the script generated is different, because the view already exists in the database. This script is included in the suggested fix, the will generate the view if it doesn't exist, but then drops the view directly after creating it.
Suggested fix:
The below script is generated by the WorkBench, and it calls to drop the view directly after it has been created, my suggested fix is remove whatever is created this line from the script generator:
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';
-- -----------------------------------------------------
-- Placeholder table for view `MyTestDB`.`TestView`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `MyTestDB`.`TestView` (`TestTableID` INT, `Value1` INT, `Value2` INT, `Value3` INT, `Value4` INT);
-- -----------------------------------------------------
-- Placeholder table for view `MyTestDB`.`testview`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `MyTestDB`.`testview` (`TestTableID` INT, `Value1` INT, `Value2` INT, `Value3` INT, `Value4` INT);
USE `MyTestDB`;
-- -----------------------------------------------------
-- View `MyTestDB`.`TestView`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `MyTestDB`.`TestView`;
USE `MyTestDB`;
CREATE OR REPLACE VIEW `MyTestDB`.`TestView` AS
SELECT *
FROM TestTable;
;
USE `MyTestDB`;
-- -----------------------------------------------------
-- View `MyTestDB`.`testview`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `MyTestDB`.`testview`;
DROP VIEW IF EXISTS `MyTestDB`.`testview` ;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
If the View does not exist the following code is created
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';
-- -----------------------------------------------------
-- Placeholder table for view `MyTestDB`.`TestView`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `MyTestDB`.`TestView` (`TestTableID` INT, `Value1` INT, `Value2` INT, `Value3` INT, `Value4` INT);
USE `MyTestDB`;
-- -----------------------------------------------------
-- View `MyTestDB`.`TestView`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `MyTestDB`.`TestView`;
USE `MyTestDB`;
CREATE OR REPLACE VIEW `MyTestDB`.`TestView` AS
SELECT *
FROM TestTable;
;
