MySQL Bugs: #37562: Forward Engineer SQL Alter script generates erroneous statements when no changes

Bug #37562	Forward Engineer SQL Alter script generates erroneous statements when no changes
Submitted:	21 Jun 2008 1:17	Modified:	10 Jul 2008 8:48
Reporter:	Ken Zo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.22 3118	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	apostrophe, broken function, forward engineer sql alter

Description:
I create a model with InnoDB tables, use "Forward Engineer SQL CREATE Script", and save results to script.  I IMMEDIATELY use "Forward Engineer SQL ALTER Script" and point to the script I just created.  Although I made no changes, Workbench generates an erroneous change script (which attempts to add a column that already exists) that will throw an error when executed in MySQL, and halt the script, thus blocking successful use of the Forward Engineer SQL Alter feature.

Note that there are two related tables, and one column comment has an apostrophe in it.  Through experimentation, I determined that the problem went away when there was only one table, or when I removed the apostrophe from the column comment.

How to repeat:
1) Open attached .mwb file, which contains two tables, related by a foreign key.  One table has a row 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) Immediately Forward Engineer SQL ALTER Script, and retrieve the script you just created.  You will receive this script, even though no changes have been made:
ALTER TABLE `dbname`.`table1` ADD COLUMN `shortname` TINYINT(1) NULL DEFAULT NULL COMMENT 'user\'s'  AFTER `id` , CHANGE COLUMN `id` `id` CHAR(5) NOT NULL  
, ADD PRIMARY KEY (`id`) ;
4) Attempting to use this script will fail, since column "shortname" already exists in the original CREATE script.

Workbench file that will fail to Forward Engineer SQL Alter after Forward Engineer SQL Create

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.

This is probably related to bug #37563.

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

in fact, it was related to the bug mentioned and was fixed together with http://bugs.mysql.com/bug.php?id=37562. fix will be in 5.0.23

An entry was added to the 5.0.23 changelog (see also #37563).