MySQL Bugs: #35457: DROP TABLE comes after CREATE TABLE IF NOT EXISTS

Bug #35457	DROP TABLE comes after CREATE TABLE IF NOT EXISTS
Submitted:	20 Mar 2008 12:06	Modified:	26 Apr 2008 20:58
Reporter:	Vladimir Dusa	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	drop table

Description:
In the alter script comes the "DROP TABLE" statement after the "CREATE TABLE" statement. See example (e.g. `atelierwwapps`.`tbl_SIAPhase`):

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  TABLE IF NOT EXISTS `atelierwwapps`.`tbl_SIAPhase` (
  `siap_Nr` INT(11) NOT NULL AUTO_INCREMENT ,
  `siap_Parent` INT(11) NULL DEFAULT NULL ,
  `siap_Name` CHAR(128) NOT NULL ,
  `siap_Order` INT(11) NOT NULL ,
  `siap_Teilphase` TINYINT(1) NOT NULL DEFAULT 0 ,
  `siap_OhneNummerierung` TINYINT(1) NOT NULL DEFAULT 0 ,
  `tmstmp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`siap_Nr`) ,
  INDEX fk_tbl_SIAPhase_tbl_SIAPhase (`siap_Parent` ASC) ,
  CONSTRAINT `fk_tbl_SIAPhase_tbl_SIAPhase`
    FOREIGN KEY (`siap_Parent` )
    REFERENCES `atelierwwapps`.`tbl_SIAPhase` (`siap_Nr` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;

CREATE  TABLE IF NOT EXISTS `atelierwwapps`.`tbl_gu_LPBKP` (
  `lpbkp_Leistungspaket` INT(11) NOT NULL ,
  `lpbkp_BKP` INT(11) NOT NULL ,
  `tmstmp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`lpbkp_Leistungspaket`, `lpbkp_BKP`) ,
  INDEX fk_tbl_gu_LPBKP_tbl_gu_leistungspakete (`lpbkp_Leistungspaket` ASC) ,
  CONSTRAINT `fk_tbl_gu_LPBKP_tbl_gu_leistungspakete`
    FOREIGN KEY (`lpbkp_Leistungspaket` )
    REFERENCES `atelierwwapps`.`tbl_gu_leistungspakete` (`lp_Nr` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci
COMMENT = 'N..N Zwischentabelle';

CREATE  TABLE IF NOT EXISTS `atelierwwapps`.`tbl_gu_LPSIAPhase` (
  `lpsiap_Leistungspaket` INT(11) NOT NULL ,
  `lpsiap_SIAPhase` INT(11) NOT NULL ,
  `lpsiap_Teilleistung` DOUBLE(15,3) NOT NULL DEFAULT 0 ,
  `tmstmp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`lpsiap_Leistungspaket`, `lpsiap_SIAPhase`) ,
  INDEX fk_tbl_gu_LPSIAPhase_tbl_gu_leistungspakete (`lpsiap_Leistungspaket` ASC) ,
  INDEX fk_tbl_gu_LPSIAPhase_tbl_SIAPhase (`lpsiap_SIAPhase` ASC) ,
  CONSTRAINT `fk_tbl_gu_LPSIAPhase_tbl_gu_leistungspakete`
    FOREIGN KEY (`lpsiap_Leistungspaket` )
    REFERENCES `atelierwwapps`.`tbl_gu_leistungspakete` (`lp_Nr` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_tbl_gu_LPSIAPhase_tbl_SIAPhase`
    FOREIGN KEY (`lpsiap_SIAPhase` )
    REFERENCES `atelierwwapps`.`tbl_SIAPhase` (`siap_Nr` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;

CREATE  TABLE IF NOT EXISTS `atelierwwapps`.`tbl_gu_auftragleistungspakete` (
  `alp_Nr` INT(11) NOT NULL AUTO_INCREMENT ,
  `alp_Leistungspaket` INT(11) NULL DEFAULT NULL ,
  `alp_Auftrag` INT(11) NULL DEFAULT NULL ,
  `tmstmp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`alp_Nr`) ,
  INDEX fk_tbl_gu_auftragleistungspakete_tbl_gu_leistungspakete (`alp_Leistungspaket` ASC) ,
  INDEX fk_tbl_gu_auftragleistungspakete_tbl_gu_auftrag (`alp_Auftrag` ASC) ,
  CONSTRAINT `fk_tbl_gu_auftragleistungspakete_tbl_gu_leistungspakete`
    FOREIGN KEY (`alp_Leistungspaket` )
    REFERENCES `atelierwwapps`.`tbl_gu_leistungspakete` (`lp_Nr` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_gu_auftragleistungspakete_tbl_gu_auftrag`
    FOREIGN KEY (`alp_Auftrag` )
    REFERENCES `atelierwwapps`.`tbl_gu_auftrag` (`a_Nr` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;

CREATE  TABLE IF NOT EXISTS `atelierwwapps`.`tbl_gu_auftragleistungspakettotal` (
  `alpt_AuftragLeistungspaket` INT(11) NOT NULL ,
  `alpt_SIAPhase` INT(11) NOT NULL ,
  `alpt_Total` DOUBLE(15,3) NULL ,
  `tmstmp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`alpt_AuftragLeistungspaket`, `alpt_SIAPhase`) ,
  INDEX fk_tbl_gu_alpt_tbl_gu_auftragleistungspakete (`alpt_AuftragLeistungspaket` ASC) ,
  INDEX fk_tbl_gu_auftragleistungspakettotal_tbl_SIAPhase (`alpt_SIAPhase` ASC) ,
  CONSTRAINT `fk_tbl_gu_alpt_tbl_gu_auftragleistungspakete`
    FOREIGN KEY (`alpt_AuftragLeistungspaket` )
    REFERENCES `atelierwwapps`.`tbl_gu_auftragleistungspakete` (`alp_Nr` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_gu_auftragleistungspakettotal_tbl_SIAPhase`
    FOREIGN KEY (`alpt_SIAPhase` )
    REFERENCES `atelierwwapps`.`tbl_SIAPhase` (`siap_Nr` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;

DROP TABLE IF EXISTS `atelierwwapps`.`tbl_gu_lpbkp` ;

DROP TABLE IF EXISTS `atelierwwapps`.`tbl_gu_lpsiaphase` ;

DROP TABLE IF EXISTS `atelierwwapps`.`tbl_gu_settings` ;

DROP TABLE IF EXISTS `atelierwwapps`.`tbl_siaphase` ;

ALTER TABLE `atelierwwapps`.`tbl_gu_offerten` DROP COLUMN `o_Auftrag` , DROP COLUMN `o_Leistung` , ADD COLUMN `o_AuftragLeistungspaket` INT(11) NULL DEFAULT NULL  AFTER `o_MutiertDurch` , ADD CONSTRAINT `fk_tbl_gu_offerten_tbl_gu_auftragleistungspakete`
  FOREIGN KEY (`o_AuftragLeistungspaket` )
  REFERENCES `atelierwwapps`.`tbl_gu_auftragleistungspakete` (`alp_Nr` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, ADD INDEX fk_tbl_gu_offerten_tbl_gu_auftragleistungspakete (`o_AuftragLeistungspaket` ASC) , DROP INDEX `tbl_gu_auftragtbl_gu_offerten` , DROP INDEX `tbl_gu_leistungtbl_gu_offerten` ;

ALTER TABLE `atelierwwapps`.`tbl_gu_offertesumme` CHANGE COLUMN `op_Offerte` `op_Offerte` INT(11) NULL DEFAULT NULL  , ADD CONSTRAINT `fk_tbl_gu_offertesumme_tbl_gu_offerten`
  FOREIGN KEY (`op_Offerte` )
  REFERENCES `atelierwwapps`.`tbl_gu_offerten` (`o_Nr` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, ADD INDEX fk_tbl_gu_offertesumme_tbl_gu_offerten (`op_Offerte` ASC) , DROP INDEX `tbl_gu_offertentbl_gu_offertesumme` ;

-- -----------------------------------------------------
-- Placeholder table for view `atelierwwapps`.`view_siaphasen`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `atelierwwapps`.`view_siaphasen` (`id` INT);

-- -----------------------------------------------------
-- View `atelierwwapps`.`view_siaphasen`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `atelierwwapps`.`view_siaphasen`;
CREATE OR REPLACE  OR REPLACE VIEW `view_siaphasen` AS select `phase`.`siap_Nr` AS `Nr`,concat(`phase`.`siap_Order`,_utf8'. ',`phase`.`siap_Name`) AS `Name` from `tbl_siaphase` `Phase` where (`phase`.`siap_Teilphase` = 0) union select `teilphase`.`siap_Nr` AS `Nr`,concat(`phase`.`siap_Order`,_utf8'.',`teilphase`.`siap_Order`,_utf8'. ',`teilphase`.`siap_Name`) AS `Name` from (`tbl_siaphase` `Teilphase` join `tbl_siaphase` `Phase` on((`teilphase`.`siap_Parent` = `phase`.`siap_Nr`))) where (`teilphase`.`siap_Teilphase` = -(1)) order by `Name`;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

How to repeat:
1) take some backup and import it as a new model (File/Import/Reverse Engineer MySQL
Script)
2) Modify some table in the model.
3) generate ALTER Script (File/Export/Forward Engineer SQL ALTER Script)
4) go through the wizard till the script will be shown.
5) check the the ALTER Script.

I'm sorry for wrong description in "How to repeat". In the point 2) should stay "create new table" instead of "modify some table".

Best regards

Vladimír Duša

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.16-rc, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".