Bug #38391 Synchronize after Forward enginieer creates ALTER statements every time
Submitted: 26 Jul 2008 13:57 Modified: 27 Jul 2008 16:19
Reporter: Martin Bachmann Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.23 SE OS:Windows
Assigned to: CPU Architecture:Any

[26 Jul 2008 13:57] Martin Bachmann
Description:
After I created a database from a model using forward engineer, I immediately sync'ed without altering anything. However it generates a huge ALTER script  
This is confusing as nothing was altered at all. 

MySQL server: 5.0.51a

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 `simulatron`.`sim_fleets` CHANGE COLUMN `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  , CHANGE COLUMN `updateLast` `updateLast` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `updateNext` `updateNext` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `updatePeriod` `updatePeriod` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `posX` `posX` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `posY` `posY` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `posZ` `posZ` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `targetX` `targetX` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `targetY` `targetY` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `targetZ` `targetZ` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `targetFleet_id` `targetFleet_id` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `targetPlanet_id` `targetPlanet_id` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `sim_races_id` `sim_races_id` INT(11) UNSIGNED NULL DEFAULT NULL  , 
  ADD CONSTRAINT `fk_sim_fleets_sim_races`
  FOREIGN KEY (`sim_races_id` )
  REFERENCES `simulatron`.`sim_races` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `simulatron`.`sim_galaxies` CHANGE COLUMN `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  , CHANGE COLUMN `stars` `stars` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `racesStart` `racesStart` INT(11) NULL DEFAULT 4  , CHANGE COLUMN `racesMax` `racesMax` INT(11) NULL DEFAULT 4  , CHANGE COLUMN `racesComputer` `racesComputer` INT(11) NULL DEFAULT 2  , CHANGE COLUMN `time` `time` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'ticks'  ;

ALTER TABLE `simulatron`.`sim_planets` CHANGE COLUMN `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  , CHANGE COLUMN `updateNext` `updateNext` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `updatePeriod` `updatePeriod` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `inventoryMetal` `inventoryMetal` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `inventoryFuel` `inventoryFuel` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `inventoryFood` `inventoryFood` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `ressourcesMetal` `ressourcesMetal` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `ressourcesFuel` `ressourcesFuel` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `ressourcesFood` `ressourcesFood` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `sim_planetTypes_id` `sim_planetTypes_id` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `sim_races_id` `sim_races_id` INT(11) UNSIGNED NOT NULL  , CHANGE COLUMN `sim_stars_id` `sim_stars_id` INT(11) UNSIGNED NOT NULL  , 
  ADD CONSTRAINT `fk_sim_planets_sim_planetTypes`
  FOREIGN KEY (`sim_planetTypes_id` )
  REFERENCES `simulatron`.`sim_planetTypes` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, 
  ADD CONSTRAINT `fk_sim_planets_sim_races`
  FOREIGN KEY (`sim_races_id` )
  REFERENCES `simulatron`.`sim_races` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, 
  ADD CONSTRAINT `fk_sim_planets_sim_stars`
  FOREIGN KEY (`sim_stars_id` )
  REFERENCES `simulatron`.`sim_stars` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `simulatron`.`sim_races` CHANGE COLUMN `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  , CHANGE COLUMN `starttime` `starttime` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `techlevel` `techlevel` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `factorAttack` `factorAttack` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorDefense` `factorDefense` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorScience` `factorScience` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorGrowth` `factorGrowth` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorFarming` `factorFarming` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorIndustry` `factorIndustry` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorNavigation` `factorNavigation` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorDiplomacy` `factorDiplomacy` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `factorAdministration` `factorAdministration` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `computerAggresivity` `computerAggresivity` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `computerDefensiveness` `computerDefensiveness` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `computerScience` `computerScience` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `computerExpansionistic` `computerExpansionistic` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `computerDiplomatic` `computerDiplomatic` FLOAT(11) UNSIGNED NULL DEFAULT 1  , CHANGE COLUMN `sim_raceTypes_id` `sim_raceTypes_id` INT(11) UNSIGNED NULL DEFAULT NULL  , 
  ADD CONSTRAINT `fk_sim_races_sim_raceTypes`
  FOREIGN KEY (`sim_raceTypes_id` )
  REFERENCES `simulatron`.`sim_raceTypes` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `simulatron`.`sim_ships` CHANGE COLUMN `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  , CHANGE COLUMN `updateNext` `updateNext` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `updatePeriod` `updatePeriod` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `sim_shipTypes_id` `sim_shipTypes_id` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `sim_races_id` `sim_races_id` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `sim_fleets_id` `sim_fleets_id` INT(11) UNSIGNED NULL DEFAULT NULL  , 
  ADD CONSTRAINT `fk_sim_ships_sim_fleets`
  FOREIGN KEY (`sim_fleets_id` )
  REFERENCES `simulatron`.`sim_fleets` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, 
  ADD CONSTRAINT `fk_sim_ships_sim_races`
  FOREIGN KEY (`sim_races_id` )
  REFERENCES `simulatron`.`sim_races` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, 
  ADD CONSTRAINT `fk_sim_ships_sim_shipTypes`
  FOREIGN KEY (`sim_shipTypes_id` )
  REFERENCES `simulatron`.`sim_shipTypes` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `simulatron`.`sim_stars` CHANGE COLUMN `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  , CHANGE COLUMN `updateNext` `updateNext` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `updatePeriod` `updatePeriod` INT(11) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `sim_starTypes_id` `sim_starTypes_id` INT(11) UNSIGNED NULL DEFAULT NULL  , 
  ADD CONSTRAINT `fk_sim_stars_sim_starTypes`
  FOREIGN KEY (`sim_starTypes_id` )
  REFERENCES `simulatron`.`sim_starTypes` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

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

How to repeat:
just forward engineer then sync

Suggested fix:
don't create alter statements if they can't be applied to the db anyway (I suppose that's the reason why they're always generated again)
[27 Jul 2008 16:19] Valeriy Kravchuk
Please do not submit the same bug more than once. An existing bug report, http://bugs.mysql.com/bug.php?id=37709, already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.