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:
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.1.17 OS:Windows
Assigned to: CPU Architecture:Any

[18 Aug 2009 10:29] Leif Inge Sandberg
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.
[20 Aug 2009 9:47] Leif Inge Sandberg
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.
[20 Aug 2009 12:21] Susanne Ebrecht
Many thanks for writing a bug report.

Verified as described.
[2 Sep 2009 12:20] Johannes Taxacher
this has been fixed and will be included in 5.1.18
[4 Sep 2009 13:08] Tony Bedford
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.
[8 Jul 2012 7:54] Jirawat Seesawang
I found this problem on 5.2.40. Below script was completed execute but my table does not has FK.

CREATE TABLE accountings (
    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 )