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:Microsoft Windows (XP SP2)
Assigned to: CPU Architecture:Any
Tags: apostrophe, broken function, forward engineer sql alter
Triage: D2 (Serious)

[21 Jun 2008 1:17] Ken Zo
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.
[21 Jun 2008 1:19] Ken Zo
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.

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

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

[21 Jun 2008 1:35] Ken Zo
This is probably related to bug #37563.
[22 Jun 2008 11:19] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[23 Jun 2008 21:36] Johannes Taxacher
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
[10 Jul 2008 8:48] Tony Bedford
An entry was added to the 5.0.23 changelog (see also #37563).