MySQL Bugs: #37563: Forward Engineer SQL Create generates script it can't then Reverse Engineer

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:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	apostrophe, Broken, forward engineer sql create, reverse engineer

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.

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.

Created from .mwb via Forward Engineer SQL Create

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

Thank you for a bug report. Verified just as described.

Fix will go in v5.0.23

works as expected now. WB correctly imports comments containing special characters.
fix will be in 5.0.23

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.