Description:
Looks similar to this report http://bugs.mysql.com/bug.php?id=67304
Have read http://dev.mysql.com/doc/workbench/en/wb-database-synchronization.html
Synchronize database model to a locally hosted database for a simple model table column name change caused all foreign key constraints to be dropped in my database. No changes were made to default options in the synchronize process.
Started by forward engineering model with several foreign key constraints as per attached file
Checked the resulting database correctly reflected the FK constraints - yes.
Changed a table column name in the model and synchronized. Executed the following generated code to rename a column:-
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';
ALTER TABLE `Microarray`.`GENE` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`CHIP` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`GENOME` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`PROBESET` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`PROBE` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`PROTEIN` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`TRANSCRIPT` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`CHROMOSOME` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
ALTER TABLE `Microarray`.`QTL` CHARACTER SET = utf8 , COLLATE = utf8_general_ci , DROP COLUMN `RightFM` , ADD COLUMN `RightF` INT(11) NULL DEFAULT NULL AFTER `QTL_trait` ;
ALTER TABLE `Microarray`.`ASSOCIATION` CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Checked column renamed - yes
Changed same table column name again in the model and synchronized again. This time the following code was generated dropping all FK constraints and setting them all up again!
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';
ALTER TABLE `Microarray`.`GENE` DROP FOREIGN KEY `fk_GENE_CHROMOSOME1` ;
ALTER TABLE `Microarray`.`PROBESET` DROP FOREIGN KEY `fk_PROBESET_CHIP1` ;
ALTER TABLE `Microarray`.`PROBE` DROP FOREIGN KEY `fk_PROBE_PROBESET1` ;
ALTER TABLE `Microarray`.`CHROMOSOME` DROP FOREIGN KEY `fk_CHROMOSOME_GENOME1` ;
ALTER TABLE `Microarray`.`QTL` DROP FOREIGN KEY `fk_QTL_CHROMOSOME1` ;
ALTER TABLE `Microarray`.`ASSOCIATION` DROP FOREIGN KEY `fk_ASSOCIATION_GENE1` ;
ALTER TABLE `Microarray`.`GENE` CHANGE COLUMN `Ensembl_ID` `Ensembl_ID` VARCHAR(45) NOT NULL , CHANGE COLUMN `CHROMOSOME_GENOME_Genome_Name` `CHROMOSOME_GENOME_Genome_Name` VARCHAR(45) NOT NULL , CHANGE COLUMN `PROBESET_Identifier` `PROBESET_Identifier` VARCHAR(45) NOT NULL , CHANGE COLUMN `PROBESET_CHIP_Chip_ID` `PROBESET_CHIP_Chip_ID` VARCHAR(45) NOT NULL , CHANGE COLUMN `Ensembl_Name` `Ensembl_Name` VARCHAR(45) NOT NULL , CHANGE COLUMN `Description` `Description` VARCHAR(150) NOT NULL , DROP FOREIGN KEY `fk_GENE_PROBESET1` ;
ALTER TABLE `Microarray`.`GENE`
ADD CONSTRAINT `fk_GENE_PROBESET1`
FOREIGN KEY (`PROBESET_Identifier` , `PROBESET_CHIP_Chip_ID` )
REFERENCES `Microarray`.`PROBESET` (`Identifier` , `CHIP_Chip_ID` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
ADD CONSTRAINT `fk_GENE_CHROMOSOME1`
FOREIGN KEY (`CHROMOSOME_Number` , `CHROMOSOME_GENOME_Genome_Name` )
REFERENCES `Microarray`.`CHROMOSOME` (`Number` , `GENOME_Genome_Name` )
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE `Microarray`.`CHIP` CHANGE COLUMN `Chip_ID` `Chip_ID` VARCHAR(45) NOT NULL , CHANGE COLUMN `Type` `Type` VARCHAR(45) NOT NULL ;
ALTER TABLE `Microarray`.`GENOME` CHANGE COLUMN `Genome_Name` `Genome_Name` VARCHAR(45) NOT NULL ;
ALTER TABLE `Microarray`.`PROBESET` CHANGE COLUMN `Identifier` `Identifier` VARCHAR(45) NOT NULL , CHANGE COLUMN `CHIP_Chip_ID` `CHIP_Chip_ID` VARCHAR(45) NOT NULL ,
ADD CONSTRAINT `fk_PROBESET_CHIP1`
FOREIGN KEY (`CHIP_Chip_ID` )
REFERENCES `Microarray`.`CHIP` (`Chip_ID` )
ON DELETE NO ACTION
ON UPDATE CASCADE;
ALTER TABLE `Microarray`.`PROBE` CHANGE COLUMN `PROBESET_Identifier` `PROBESET_Identifier` VARCHAR(45) NOT NULL , CHANGE COLUMN `PROBESET_CHIP_Chip_ID` `PROBESET_CHIP_Chip_ID` VARCHAR(45) NOT NULL ,
ADD CONSTRAINT `fk_PROBE_PROBESET1`
FOREIGN KEY (`PROBESET_Identifier` , `PROBESET_CHIP_Chip_ID` )
REFERENCES `Microarray`.`PROBESET` (`Identifier` , `CHIP_Chip_ID` )
ON DELETE NO ACTION
ON UPDATE CASCADE;
ALTER TABLE `Microarray`.`PROTEIN` CHANGE COLUMN `Protein_ID` `Protein_ID` VARCHAR(45) NOT NULL ;
ALTER TABLE `Microarray`.`TRANSCRIPT` CHANGE COLUMN `Transcript_ID` `Transcript_ID` VARCHAR(45) NOT NULL ;
ALTER TABLE `Microarray`.`CHROMOSOME` CHANGE COLUMN `GENOME_Genome_Name` `GENOME_Genome_Name` VARCHAR(45) NOT NULL ,
ADD CONSTRAINT `fk_CHROMOSOME_GENOME1`
FOREIGN KEY (`GENOME_Genome_Name` )
REFERENCES `Microarray`.`GENOME` (`Genome_Name` )
ON DELETE NO ACTION
ON UPDATE CASCADE;
ALTER TABLE `Microarray`.`QTL` CHANGE COLUMN `QTL_Name` `QTL_Name` VARCHAR(45) NOT NULL , CHANGE COLUMN `CHROMOSOME_GENOME_Genome_Name` `CHROMOSOME_GENOME_Genome_Name` VARCHAR(45) NOT NULL , CHANGE COLUMN `Left_flank_marker` `Left_flank_marker` VARCHAR(45) NOT NULL , CHANGE COLUMN `right_flank_marker` `right_flank_marker` VARCHAR(45) NOT NULL , CHANGE COLUMN `peak_marker` `peak_marker` VARCHAR(45) NULL DEFAULT NULL , CHANGE COLUMN `QTL_trait` `QTL_trait` VARCHAR(150) NOT NULL , CHANGE COLUMN `RightF` `RightFM` INT(11) NULL DEFAULT NULL ,
ADD CONSTRAINT `fk_QTL_CHROMOSOME1`
FOREIGN KEY (`CHROMOSOME_Number` , `CHROMOSOME_GENOME_Genome_Name` )
REFERENCES `Microarray`.`CHROMOSOME` (`Number` , `GENOME_Genome_Name` )
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE `Microarray`.`ASSOCIATION` CHANGE COLUMN `TRANSCRIPT_Transcript_ID` `TRANSCRIPT_Transcript_ID` VARCHAR(45) NOT NULL , CHANGE COLUMN `PROTEIN_Protein_ID` `PROTEIN_Protein_ID` VARCHAR(45) NOT NULL , CHANGE COLUMN `GENE_Ensembl_ID` `GENE_Ensembl_ID` VARCHAR(45) NOT NULL , DROP FOREIGN KEY `fk_ASSOCIATION_TRANSCRIPT` , DROP FOREIGN KEY `fk_ASSOCIATION_PROTEIN1` ;
ALTER TABLE `Microarray`.`ASSOCIATION`
ADD CONSTRAINT `fk_ASSOCIATION_TRANSCRIPT`
FOREIGN KEY (`TRANSCRIPT_Transcript_ID` )
REFERENCES `Microarray`.`TRANSCRIPT` (`Transcript_ID` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
ADD CONSTRAINT `fk_ASSOCIATION_PROTEIN1`
FOREIGN KEY (`PROTEIN_Protein_ID` )
REFERENCES `Microarray`.`PROTEIN` (`Protein_ID` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
ADD CONSTRAINT `fk_ASSOCIATION_GENE1`
FOREIGN KEY (`GENE_Ensembl_ID` )
REFERENCES `Microarray`.`GENE` (`Ensembl_ID` )
ON DELETE NO ACTION
ON UPDATE CASCADE;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Upon execution, the following error response was received:-
Executing SQL script in server
ERROR: Error 1005: Can't create table 'Microarray.#sql-9547_16d' (errno: 150)
ALTER TABLE `Microarray`.`GENE`
ADD CONSTRAINT `fk_GENE_PROBESET1`
FOREIGN KEY (`PROBESET_Identifier` , `PROBESET_CHIP_Chip_ID` )
REFERENCES `Microarray`.`PROBESET` (`Identifier` , `CHIP_Chip_ID` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
ADD CONSTRAINT `fk_GENE_CHROMOSOME1`
FOREIGN KEY (`CHROMOSOME_Number` , `CHROMOSOME_GENOME_Genome_Name` )
REFERENCES `Microarray`.`CHROMOSOME` (`Number` , `GENOME_Genome_Name` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
SQL script execution finished: statements: 10 succeeded, 1 failed
How to repeat:
Suggest copy my code exactly as above to repeat
Suggested fix:
Near identical operation ignores FK's first time and then drops them all second time for near identical operation - bug???