Bug #69153 Synchronize database for column name change drops all foreign key constraints
Submitted: 6 May 2013 10:40 Modified: 28 Jun 2013 9:25
Reporter: Bruce Steedman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:5.2.47 OS:MacOS (10.8.3)
Assigned to: CPU Architecture:Any
Tags: dropped, foreign key, synchronize

[6 May 2013 10:40] Bruce Steedman
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???
[6 May 2013 10:41] Bruce Steedman
Weird code generated by synch

Attachment: SQL.txt (text/plain), 7.05 KiB.

[6 May 2013 10:42] Bruce Steedman
Weird code generated by sync

Attachment: SQL.txt (text/plain), 7.05 KiB.

[6 May 2013 11:23] MySQL Verification Team
Thank you for the bug report. Could you please provide your model project file?. Thanks.
[6 May 2013 11:32] Bruce Steedman
Model

Attachment: Microarray.mwb (application/octet-stream, text), 18.81 KiB.

[6 May 2013 12:34] MySQL Verification Team
"Changed a table column name in the model and synchronized....". Please elaborate which change you did (table/column). Thanks.
[6 May 2013 12:38] Bruce Steedman
This bit of code shows the table/column name modified. This worked. Changing the name back to original name threw up the extra FK code  

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` ;
[8 May 2013 13:44] MySQL Verification Team
Thank you for the feedback. I did: Synchronize Model with a live server 5.6.11 and after completed without problems back to model changed the column name RightFM to RightF and saved the model. Synchronized again without problems. So please check that is the same steps you did. Thanks.
[8 May 2013 15:52] Bruce Steedman
Sync I was performing was with a locally hosted database (through MAMP) with the following settings:-

Hostname = localhost
Port = 8889

Please advise if you can replicate the issue with these settings.

Thanks
[8 May 2013 20:42] MySQL Verification Team
Alter Table

Attachment: alter_table.png (image/png, text), 331.79 KiB.

[8 May 2013 20:45] MySQL Verification Team
It continues without problems however I noticed the alter table statement a bit different than yours (I had not modified your model) please see the prior attached screen-shot. Thanks.
[9 May 2013 15:45] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=69181 marked as duplicate of this one.
[9 May 2013 16:10] Bruce Steedman
Thanks - #69181 is better write up than mine! Will try and compare the alter table statements, to see if this is the cause.
[14 May 2013 11:12] Jan de Vries
The same thing is happening to me when there is a difference in a column's default value (i.e.: the model specifies none/empty, but the server specifies "0"): upon synchronizing, Workbench does a "CHANGE" on that specific column ánd drops & readds all of the table's foreign keys.
[22 Jun 2013 3:36] Alfredo Kojima
Posted by developer:
 
The bug was fixed in the 6.0 branch
[27 Jun 2013 23:09] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.0.3 release, and here's the changelog entry:

Executing "Synchronize Model" against two tables that were identical
except for the order that the indexes were created, would cause
MySQL Workbench to drop and immediately re-add the foreign keys.

Thank you for the bug report.
[28 Jun 2013 9:25] Bruce Steedman
Great to hear Philip - look forward to seeing 6.0.3

Thanks

Bruce