Bug #37563 Forward Engineer SQL Create generates script it can't then Reverse Engineer
Submitted: 21 Jun 2008 1:32 Modified: 9 Jul 2008 17:12
Reporter: Ken Zo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.0.22 rev 3118 OS:Microsoft Windows (XP SP2)
Assigned to: CPU Architecture:Any
Tags: apostrophe, Broken, forward engineer sql create, reverse engineer
Triage: D2 (Serious)

[21 Jun 2008 1:32] Ken Zo
Description:
I create a model with InnoDB tables, use "Forward Engineer SQL CREATE Script", and save results to script.

Note that there are two related tables, and one column comment has an apostrophe in it.

I then close the current Workbench session (i.e. "New", or restart the application.)  I Import->Reverse Engineer MySQL Create Script.  I use the script Workbench just created.  I get an error (at the part of the script that has an apostrophe in a comment) and Workbench is unable to import the script.

The error message log is:

Started parsing MySQL SQL script.
Created MySQL Schema: dbname
Line 13: SQL syntax error near 's' ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB'. Statement skipped.
Created MySQL Table: dbname.table2
Table `dbname`.`table1` not found. Stub was created.
Table `dbname`.`table2` : Foreign key `fk_shortname` : Referred column `dbname`.`table1`.`id` not found. Stub was created.
Finished parsing MySQL SQL script. Totally processed statements: successful (2), errors (1), warnings (2).

How to repeat:
1) Open attached .mwb file, which contains two tables, related by a foreign key.  One table has a column with the comment "user's", which has an apostrophe.

2) Forward Engineer SQL CREATE Script.  It should generate a script like the attached created.sql file, which includes these statements:

CREATE SCHEMA IF NOT EXISTS `dbname` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci ;
USE `dbname`;
CREATE  TABLE IF NOT EXISTS `dbname`.`table1` (
  `id` CHAR(5) NOT NULL ,
  `shortname` TINYINT(1) NULL COMMENT 'user\'s' ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;
CREATE  TABLE IF NOT EXISTS `dbname`.`table2` (
  `id` CHAR(5) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_shortname (`id` ASC) ,
  CONSTRAINT `fk_shortname`
    FOREIGN KEY (`id` )
    REFERENCES `dbname`.`table1` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

3) Restart Workbench.
4) Reverse Engineer MySQL Create Script.  Point to the script created in the previous step.
You will get the error message displayed above, and the data model with its two tables will not fully import.

This might be related to bug #37562.
[21 Jun 2008 1:34] Ken Zo
Workbench file that will generate a script with Forward Engineer SQL Create that it can't then Reverse Engineered

Attachment: alter_bug_test.mwb (application/octet-stream, text), 4.56 KiB.

[21 Jun 2008 1:34] Ken Zo
Created from .mwb via Forward Engineer SQL Create

Attachment: created.sql (application/octet-stream, text), 1.12 KiB.

[22 Jun 2008 10:50] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[23 Jun 2008 10:56] Sergei Tkachenko
Fix will go in v5.0.23
[23 Jun 2008 20:18] Johannes Taxacher
works as expected now. WB correctly imports comments containing special characters.
fix will be in 5.0.23
[9 Jul 2008 17:12] Tony Bedford
An entry was added to the 5.0.23 Changelog:

The File, Export, Forward Engineer SQL CREATE Script menu item exports a script it is then unable to import using the File, Export, Reverse Engineer MySQL Create Script menu item, as it incorrectly imports comments containing special characters.