Description:
I have a schema that creates different SQL in the CREATE script vs. the ALTER script. I should be able to generate the CREATE script, generate an ALTER script using that CREATE script as the input, and the result should be essentially nothing. I have proven that I can do that with other schemas.
Here is the ALTER script:
#######################################
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 `ted_test`.`DUT_Session_Build` DROP FOREIGN KEY `fk_DUT_Session_Build_DI1` ;
ALTER TABLE `ted_test`.`Test_Results` DROP FOREIGN KEY `fk_Test_Results_Test_Cases1` ;
ALTER TABLE `ted_test`.`DUT_Session_Build` DROP COLUMN `DI_ID` , ADD COLUMN `DI_ID` INT(11) NULL DEFAULT NULL AFTER `BLD_ID` ,
ADD CONSTRAINT `fk_DUT_Session_Build_DI1`
FOREIGN KEY (`DI_ID` )
REFERENCES `ted_test`.`DUT_Identifier` (`DI_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
, DROP PRIMARY KEY
, ADD PRIMARY KEY (`BLD_ID`, `DUT_ID`, `SES_ID`, `DI_ID`)
, ADD INDEX `fk_DUT_Session_Build_DI1` (`DI_ID` ASC)
, DROP INDEX `fk_DUT_Session_Build_DI1` ;
ALTER TABLE `ted_test`.`Test_Results` DROP COLUMN `TC_ID` , ADD COLUMN `TC_ID` INT(11) NULL DEFAULT NULL AFTER `RES_ID` ,
ADD CONSTRAINT `fk_Test_Results_Test_Cases1`
FOREIGN KEY (`TC_ID` )
REFERENCES `ted_test`.`Test_Cases` (`TC_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
, DROP INDEX `fk_Test_Results_Test_Cases1`
, ADD INDEX `fk_Test_Results_Test_Cases1` (`TC_ID` ASC) ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
#######################################
Here is the relevant piece of the CREATE script:
#######################################
CREATE TABLE IF NOT EXISTS `ted_test`.`DUT_Session_Build` (
`DUT_ID` INT NOT NULL ,
`SES_ID` INT NOT NULL ,
`BLD_ID` INT NOT NULL ,
`DI_ID` INT NULL ,
PRIMARY KEY (`BLD_ID`, `DUT_ID`, `SES_ID`, `DI_ID`) ,
INDEX `fk_Result_Status_has_Build_Build1` (`BLD_ID` ASC) ,
INDEX `fk_DUT_Result_Build_DUTs1` (`DUT_ID` ASC) ,
INDEX `fk_DUT_Result_Build_Sessions1` (`SES_ID` ASC) ,
INDEX `fk_DUT_Session_Build_DI1` (`DI_ID` ASC) ,
CONSTRAINT `fk_Result_Status_has_Build_Build1`
FOREIGN KEY (`BLD_ID` )
REFERENCES `ted_test`.`Build` (`BLD_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_DUT_Result_Build_DUTs1`
FOREIGN KEY (`DUT_ID` )
REFERENCES `ted_test`.`DUTs` (`DUT_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_DUT_Result_Build_Sessions1`
FOREIGN KEY (`SES_ID` )
REFERENCES `ted_test`.`Sessions` (`SES_ID` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `fk_DUT_Session_Build_DI1`
FOREIGN KEY (`DI_ID` )
REFERENCES `ted_test`.`DUT_Identifier` (`DI_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `ted_test`.`Test_Results` (
`TR_ID` INT NOT NULL AUTO_INCREMENT ,
`RES_ID` INT NULL ,
`TC_ID` INT NULL ,
`TR_State` VARCHAR(45) NULL ,
`TR_Status` VARCHAR(256) NULL ,
`Warnings` INT NULL DEFAULT 0 ,
`Errors` INT NULL DEFAULT 0 ,
`TR_Start` DATETIME NULL ,
`TR_Updated` DATETIME NULL ,
`TR_Percent_Complete` INT NULL DEFAULT 0 ,
`TR_Result` VARCHAR(45) NULL ,
`TR_Blessed` TINYINT NULL DEFAULT 0 ,
PRIMARY KEY (`TR_ID`) ,
INDEX `fk_Test_Result_Values_Result_Status1` (`RES_ID` ASC) ,
INDEX `fk_Test_Results_Test_Cases1` (`TC_ID` ASC) ,
CONSTRAINT `fk_Test_Result_Values_Result_Status1`
FOREIGN KEY (`RES_ID` )
REFERENCES `ted_test`.`Result_Status` (`RES_ID` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `fk_Test_Results_Test_Cases1`
FOREIGN KEY (`TC_ID` )
REFERENCES `ted_test`.`Test_Cases` (`TC_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
#######################################
There appear to be a few differences, mostly like the ALTER script is being more specific with field sizes and default values than the CREATE script.
I would retest this in 5.2.34, but generating an ALTER script crashes. I've already written up that bug.
How to repeat:
I'm not sure exactly since these tables appear similar to my other tables.
I do not want to publicly post my .mwb file, but I will work with whatever developer who works on this.
Suggested fix:
Align the SQL so the ALTER script generates no real SQL statements when compared to the identical CREATE script.
Description: I have a schema that creates different SQL in the CREATE script vs. the ALTER script. I should be able to generate the CREATE script, generate an ALTER script using that CREATE script as the input, and the result should be essentially nothing. I have proven that I can do that with other schemas. Here is the ALTER script: ####################################### 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 `ted_test`.`DUT_Session_Build` DROP FOREIGN KEY `fk_DUT_Session_Build_DI1` ; ALTER TABLE `ted_test`.`Test_Results` DROP FOREIGN KEY `fk_Test_Results_Test_Cases1` ; ALTER TABLE `ted_test`.`DUT_Session_Build` DROP COLUMN `DI_ID` , ADD COLUMN `DI_ID` INT(11) NULL DEFAULT NULL AFTER `BLD_ID` , ADD CONSTRAINT `fk_DUT_Session_Build_DI1` FOREIGN KEY (`DI_ID` ) REFERENCES `ted_test`.`DUT_Identifier` (`DI_ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION , DROP PRIMARY KEY , ADD PRIMARY KEY (`BLD_ID`, `DUT_ID`, `SES_ID`, `DI_ID`) , ADD INDEX `fk_DUT_Session_Build_DI1` (`DI_ID` ASC) , DROP INDEX `fk_DUT_Session_Build_DI1` ; ALTER TABLE `ted_test`.`Test_Results` DROP COLUMN `TC_ID` , ADD COLUMN `TC_ID` INT(11) NULL DEFAULT NULL AFTER `RES_ID` , ADD CONSTRAINT `fk_Test_Results_Test_Cases1` FOREIGN KEY (`TC_ID` ) REFERENCES `ted_test`.`Test_Cases` (`TC_ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION , DROP INDEX `fk_Test_Results_Test_Cases1` , ADD INDEX `fk_Test_Results_Test_Cases1` (`TC_ID` ASC) ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; ####################################### Here is the relevant piece of the CREATE script: ####################################### CREATE TABLE IF NOT EXISTS `ted_test`.`DUT_Session_Build` ( `DUT_ID` INT NOT NULL , `SES_ID` INT NOT NULL , `BLD_ID` INT NOT NULL , `DI_ID` INT NULL , PRIMARY KEY (`BLD_ID`, `DUT_ID`, `SES_ID`, `DI_ID`) , INDEX `fk_Result_Status_has_Build_Build1` (`BLD_ID` ASC) , INDEX `fk_DUT_Result_Build_DUTs1` (`DUT_ID` ASC) , INDEX `fk_DUT_Result_Build_Sessions1` (`SES_ID` ASC) , INDEX `fk_DUT_Session_Build_DI1` (`DI_ID` ASC) , CONSTRAINT `fk_Result_Status_has_Build_Build1` FOREIGN KEY (`BLD_ID` ) REFERENCES `ted_test`.`Build` (`BLD_ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_DUT_Result_Build_DUTs1` FOREIGN KEY (`DUT_ID` ) REFERENCES `ted_test`.`DUTs` (`DUT_ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_DUT_Result_Build_Sessions1` FOREIGN KEY (`SES_ID` ) REFERENCES `ted_test`.`Sessions` (`SES_ID` ) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_DUT_Session_Build_DI1` FOREIGN KEY (`DI_ID` ) REFERENCES `ted_test`.`DUT_Identifier` (`DI_ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `ted_test`.`Test_Results` ( `TR_ID` INT NOT NULL AUTO_INCREMENT , `RES_ID` INT NULL , `TC_ID` INT NULL , `TR_State` VARCHAR(45) NULL , `TR_Status` VARCHAR(256) NULL , `Warnings` INT NULL DEFAULT 0 , `Errors` INT NULL DEFAULT 0 , `TR_Start` DATETIME NULL , `TR_Updated` DATETIME NULL , `TR_Percent_Complete` INT NULL DEFAULT 0 , `TR_Result` VARCHAR(45) NULL , `TR_Blessed` TINYINT NULL DEFAULT 0 , PRIMARY KEY (`TR_ID`) , INDEX `fk_Test_Result_Values_Result_Status1` (`RES_ID` ASC) , INDEX `fk_Test_Results_Test_Cases1` (`TC_ID` ASC) , CONSTRAINT `fk_Test_Result_Values_Result_Status1` FOREIGN KEY (`RES_ID` ) REFERENCES `ted_test`.`Result_Status` (`RES_ID` ) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Test_Results_Test_Cases1` FOREIGN KEY (`TC_ID` ) REFERENCES `ted_test`.`Test_Cases` (`TC_ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; ####################################### There appear to be a few differences, mostly like the ALTER script is being more specific with field sizes and default values than the CREATE script. I would retest this in 5.2.34, but generating an ALTER script crashes. I've already written up that bug. How to repeat: I'm not sure exactly since these tables appear similar to my other tables. I do not want to publicly post my .mwb file, but I will work with whatever developer who works on this. Suggested fix: Align the SQL so the ALTER script generates no real SQL statements when compared to the identical CREATE script.