Bug #54363 Forward engineer ALTER sql script
Submitted: 9 Jun 2010 11:17 Modified: 28 Jul 2010 14:07
Reporter: Daniel Robinson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.2.24 CE RC rev.6246 OS:Any
Assigned to: Alexander Musienko CPU Architecture:Any
Tags: regression

[9 Jun 2010 11:17] Daniel Robinson
Description:

This feature has worked fine for me in version 5.1, with the same database. This seems to be a regression in the 5.2 branch.

When I modify a table, for instance by adding a field. I then export the sql ALTER script. The script generated contains far too many changes, it seems to want to recreate most (but not all) the foreign keys in all the tables.

To generate this script I export a full create script just before making the change. 

I get one group such as follows for each table in my database. 

ALTER TABLE `mydb`.`InvoiceLine` DROP FOREIGN KEY `fk_InvoiceLine_Invoice` ;

ALTER TABLE `mydb`.`InvoiceLine` 
  ADD CONSTRAINT `fk_InvoiceLine_Invoice`
  FOREIGN KEY (`invoiceName` , `companyId` )
  REFERENCES `mydb`.`Invoice` (`invoiceName` , `companyId` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

How to repeat:

1) Forward engineer a sql create script, no sql options selected, only export mysql table objects.

2) Make a change to a table in the database. Such as adding a field.

3) Forward engineer a sql alter script, use script created at step 1 as the input script.

4) The script produced contains too many instructions.
[14 Jun 2010 9:13] Johannes Taxacher
this is a duplicate of Bug #50938
[24 Jun 2010 11:54] Daniel Robinson
The fix for Bug #50938 has not resolved the issue I am having here. I have just installed the latest RC version. The steps I used to test the issue are : 

1) File -> Export -> Forward engineer SQL CREATE script
   The only option selected is "Generate DROP statement before each CREATE statement"
   Next
   Only select "Export Mysql Table Objects"
   Next
   Save to other file
   Finish

2) File -> Export -> Forward engineer SQL ALTER script
   Select file created in step 1 as the Input file.
   Next
   The proposed sql script contains statements. As the database has not changed at all this script should be empty.

The proposed script contains for each table : 

ALTER TABLE `amanda`.`UserProfileRole` DROP FOREIGN KEY `fk_UserProfileRole_UserProfile` ;

ALTER TABLE `amanda`.`UserProfileRole` 
  ADD CONSTRAINT `fk_UserProfileRole_UserProfile`
  FOREIGN KEY (`idUserProfile` )
  REFERENCES `amanda`.`UserProfile` (`idUserProfile` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Only one such set of statements per table, even if the table contains more than one foreign key.
[24 Jun 2010 11:55] Daniel Robinson
Wrong version number typed in. Sorry.
[25 Jun 2010 11:20] Daniel Robinson
reopening bug, forgot to change status
[26 Jun 2010 10:53] Valeriy Kravchuk
Please, upload some .mwb file to demonstrate the problem.
[29 Jun 2010 7:06] Susanne Ebrecht
Just for my understanding:

1. You draw a model
2. Forward engineer the model to mysql server
3. change model
4. What did you here? Did you say forward engineer again or did you sync the model?

Forward engineer is taking the whole model and sending it to MySQL server. It should only be used, when the schema isn't already existing on the server.

For all changes you will make in your model after you already used forward engineer and after the schema alraedy exist in the database you should use
"synchronise model".

Do you have this problems by using synchronise model?
[29 Jun 2010 7:21] Daniel Robinson
I need to generate SQL scripts to alter the structure of a database as I do not have access to all the machines the database is running on (multiple installations on different platforms).

To do this I use the "Forward Engineer ALTER sql script" functionality. For this to work I provide the "tool" with a sql file that describes the current state of the DB. This current state file is produced by the "Forward Engineer CREATE sql script".

Currently if I simply :

1) produce the create script
2) right after produce a alter script with the script from step 1
3) the script produced in step 2 contains many statements, it should be empty.

The steps are described more precisely in my previous comments.
[29 Jun 2010 12:54] Valeriy Kravchuk
Verified just as described in one of the previous comments using the .mwb file provided on Mac OS X. 

SQL generated consists of statements like (table names are substituted):

ALTER TABLE `db`.`t1` DROP FOREIGN KEY `fk_t1_t2` ;

...

ALTER TABLE `db`.`t1` 
  ADD CONSTRAINT `fk_t1_t2`
  FOREIGN KEY (`t2_id` )
  REFERENCES `db`.`t2` (`t2_id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

I think we have other reports like this (not needed SQL generated for FKs even when they had not changed at all)...
[12 Jul 2010 9:09] Susanne Ebrecht
Bug #55157 is set as duplicate of this bug here.
[27 Jul 2010 12:00] Johannes Taxacher
fix confirmed in repository
[28 Jul 2010 14:07] Tony Bedford
An entry has been added to the 5.2.26 changelog:

When forward engineering a SQL ALTER script, the generated code attempted to recreate all foreign keys.