Bug #67370 Workbench fails to synchronize views
Submitted: 24 Oct 2012 23:27 Modified: 6 Nov 2012 4:03
Reporter: Joe Harper Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.44 OS:Windows (7)
Assigned to: CPU Architecture:Any

[24 Oct 2012 23:27] Joe Harper
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;

;
[24 Oct 2012 23:28] Joe Harper
The test model I am using

Attachment: Test.mwb (application/octet-stream, text), 5.11 KiB.

[24 Oct 2012 23:43] Joe Harper
On further examination, it looks like the incorrect script is created only if you have uppercase letters in your view name.
[25 Oct 2012 17:27] Rafael Antonio Bedoy Torres
Verified using 5.2.44 CE Revision 9933
[6 Nov 2012 4:03] Alfredo Kojima
duplicate of bug #65108