MySQL Bugs: #49041: Altering a VIEW creates script which includes DROP/CREATE table

Bug #49041	Altering a VIEW creates script which includes DROP/CREATE table
Submitted:	24 Nov 2009 17:00	Modified:	26 Nov 2009 15:36
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.2 r4753	OS:	Windows (XP)
Assigned to:	Alexander Musienko	CPU Architecture:	Any

Description:
When altering an existing VIEW, WB creates/executes a script which unnecessarily CREATEs and DROPs a TABLE with the same name:

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';

-- -----------------------------------------------------

-- Placeholder table for view `test`.`v1`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `test`.`v1` (`id` INT);

USE test;

-- -----------------------------------------------------

-- View `test`.`v1`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `test`.`v1`;

USE test;

CREATE  OR REPLACE VIEW `test`.`v1` AS SELECT * FROM t1;

;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This is unnecessary, and actually problematic, because if there is an existing TABLE whose name conflicts, this TABLE will be dropped (data loss) during execution of the script rather than an error resulting from the attempted creation of the VIEW with the same name

How to repeat:
Create TABLE test.v1
Create TABLE test.t1;
Try to create VIEW test.v1 using WB, watch TABLE test.v1 disappear.

Suggested fix:
Eliminate dangerous and unnecessary DROP TABLE commands.

You are taking about altering standalone database objects using query editor form, not about model synchronization, right?

Yes, that is correct.

fixed in repository. will be included in 5.2.9

An entry has been added to the 5.2.9 changelog:

In the Overview tab of the SQL Editor, representing a “live” view of the database currently connected to, if an attempt was made to edit a view, the resulting script generated unnecessary, and in fact dangerous, DROP TABLE statements. This had the potential side-effect that a table that coincidentally had the same name as the view, would be dropped with ensuing data loss.