MySQL Bugs: #45834: Fwd Eng SQL ALTER Script... changes columns that use user types with empty args

Bug #45834	Fwd Eng SQL ALTER Script... changes columns that use user types with empty args
Submitted:	29 Jun 2009 17:04	Modified:	17 Feb 2010 11:20
Reporter:	Stas Trefilov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.1.15 RC	OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any

Description:
forward engineering the alter script will generate CHANGE COLUMN statements on any columns declared as user defined type with empty argument. if your user type is declared as integer(11) it'll pass ok, but if you omit the (11) part, your columns will be changing types on each forward engineer attempt. don't know if the server will really execute the CHANGE command if it redeclares the same type, but this one really pollutes the output when you use user types a lot.

How to repeat:
1/ create a user type with empty Arguments field (i did this using INT with UNSIGNED flag and clearing the default value of 11 in the Arguments field)

2/ create new table with a single column using this user type (i also checked NOT NULL but this one does not really matter). interesting side note: you do not have the newly created user type in the Datatype dropdown. you can see it however in the User Types tab on the right. this one is new to 5.1.15.

3/ run Export / Forward Engineer SQL CREATE Script...:

[skip]
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
`id` INT UNSIGNED NOT NULL )
ENGINE = MyISAM;
[skip]

4/ run Export / Forward Engineer SQL ALTER Script... using the created file as an input. the output file will have this:

[skip]
ALTER TABLE `mydb`.`table1` CHANGE COLUMN `id` `id` INT UNSIGNED NOT NULL ;
[skip]

Suggested fix:
if you are using OSS version of WB and trying to synchronize your schema using Forward Engineer SQL ALTER Script feature then you have to manually review the generated alter statements and remove the lines changing columns with user defined type.

Thank you for the bug report. Verified just as described also on Windows XP.

not repeatable in current beta version

An entry has been added to the 5.2.15 changelog:

If a model contained a table which used a user defined type for a column, then when the model was forward engineered an erroneous COLUMN CHANGE statement was generated for the column. This only happened for user defined types without additional arguments. For example, a user defined type using INTEGER(11) would not create a COLUMN CHANGE statement, but using INTEGER would.