MySQL Bugs: #46783: Workbench autoticks the "Skip Creation of FOREIGN KEYS" export checkbox

Bug #46783	Workbench autoticks the "Skip Creation of FOREIGN KEYS" export checkbox
Submitted:	18 Aug 2009 10:29	Modified:	4 Sep 2009 13:08
Reporter:	Leif Inge Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.17	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
MySQL Workbench 5.1.17 revision 4302.

My db model has collation "server default". On two tables I changed this to utf8_general_ci and added a varchar(45) column. I selected File/Export/Forward Engineer SQL CREATE script (the EER Diagram tab was open; I did not go to the MySQL Model first). The Export options were prefilled from an earlier export, Output SQL Script file pointing to an existing file and the following SQL Options ticked:

- Generate DROP Statements Before Each CREATE Statement
- Generate Separate CREATE INDEX Statements
- Add SHOW WARNINGS After Every DDL Statement
- Generate INSERT Statements for Tables

On the next screen, SQL Object Export Filter, the Export MySQL Table Objects checkbox was ticked, with 44 out of 44 tables selected. No other checkboxes where ticked.

The exported sql file contained no foreign keys, as if I had ticked the "Skip Creation of FOREIGN KEYS" checkbox. Trying a new export, I saw this checkbox still wasn't ticked, but a new export gave the same result. I clicked twice on that checkbox, ticking and unticking it. Then I got a correct export file.

It looks like some internal "Skip foreign keys at export" variable was ticked when I selected collation on the table level.

How to repeat:
Reproduced in another db model with collation utf8_general_ci as default. Same sequence:

1. Set collation utf8_general_ci on a table
2. Export SQL CREATE script file, DO NOT tick "Skip Creation of FOREIGN KEYS"
3. The foreign keys are gone from the script file, as if you DID tick that checkbox

Now I can't even get the foreign keys back in the script, no matter how much I tick and untick the checkbox. Had to roll back to the db model version before I changed collation. Then when I exported a script, the foreign keys were back in there.

I'd like to upgrade severity to level 2, serious. If the issue continues (seemingly outside of my control altogether), I must go back to an older version.

Another FK error popped up today and seems related to the issue about foreign key handling in this version. I deleted two tables, renamed another and then added a column with a foreign key. Then Workbench crashed. I restarted Workbench, deleted and re-added the column, but then in the Foreign key tab, in the Column/Referenced column pane, I couldn't click on the column - it was obviously disabled. I could click on other column, just not the one for which I actually wanted a foreign key.

I then started deleting and adding the column and foreign keys with different names, exporting SQL CREATE file, and restarting the Workbench. One of the exported SQL CREATE files didn't contain any foreign keys altogether, but the next export was ok, without me doing anything other than restarting Workbench and changing the export file name between the two exports. All of a sudden the Foreign key tab began to behave as normal and let me click on the wanted column. Now the exported file looks ok, as does the data model.

I believe the "disabled" foreign key pane happened to me in Wb 5.0 as well,  with an empty foreign key name in the exported file:

CONSTRAINT `fkTableAuthorID`
    FOREIGN KEY () -- No name here
    REFERENCES `mymodel`.`Author` () -- Empty here as well

The "skip foreign keys" checkbox issue I never experienced in 5.0.

Many thanks for writing a bug report.

Verified as described.

this has been fixed and will be included in 5.1.18

An entry was added to the 5.1.18 changelog:

MySQL Workbench did not export Foreign Keys when using the File, Export, Forward Engineer SQL CREATE Script wizard, even though the option Skip Creation of FOREIGN KEYS checkbox was cleared. Further, MySQL Workbench crashed when a column with a Foreign Key was added to a table.

I found this problem on 5.2.40. Below script was completed execute but my table does not has FK.

CREATE TABLE accountings (
    id INT NOT NULL AUTO_INCREMENT,
    accounting_type_id int  NOT NULL,
    amount decimal(18,0)  NOT NULL,
    status varchar(50)  NOT NULL,
    create_time DATETIME  NOT NULL,
    create_by varchar(50)  NOT NULL,
    modify_time DATETIME  NULL,
    modify_by varchar(50)  NULL,
  PRIMARY KEY (id) ,
  INDEX fk_accounting_type_id (accounting_type_id ASC) ,
  INDEX ix_status (status ASC) ,
  INDEX ix_create_time (create_time ASC) ,
  INDEX ix_modify_time (modify_time ASC) ,
  CONSTRAINT fk_accounting_type_id
    FOREIGN KEY (accounting_type_id )
    REFERENCES accounting_type (id )
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;