MySQL Bugs: #45165: can't use auto_increment attribute with user defined type, crash on workaround

Bug #45165	can't use auto_increment attribute with user defined type, crash on workaround
Submitted:	28 May 2009 18:15	Modified:	9 Jun 2009 9:25
Reporter:	Stas Trefilov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.1.12	OS:	MacOS (10.5.7)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	CHECKED

Description:
when defining a primary key with a user defined type workbench does not allow to set the auto_increment attribute. clicking on AI checkbox does not alter the option.

after using the workaround described later the model may be saved on disk and may be reopened later, but now trying to Forward Engineer SQL CREATE Script leads to a crash if a set of exported tables has a column with user type and auto_increment attribute set to on.

How to repeat:
- define a new user type as follows:
name: id
type: INT
flags: UNSIGNED

- create a table with a single column:
column: a
datatype: id

- note that after selecting user type 'id' as a datatype you cannot modify the AI checkbox

- now change the column datatype to 'INT'

- check the AI checkbox (note that as we use a predefined type the checkbox may be altered)

- change the column datatype back to 'id' leaving the AI attribute checked

- now you have column 'a' of user type 'id' with auto_increment attribute set

- SAVE MODEL

- go to File / Export / Forward Engineer SQL CREATE Script... and click twice on Continue

- program unexpectedly quits

Suggested fix:
use a predefined type (int) for the primary key, check the auto_increment attribute, then change the type to the needed user defined type.

Thank you for the bug report. Verified just as described. Pretty serious bug because of the crash.

These were actually two bugs in one, affecting all supported platforms. Fixed in 5.1 and 5.2.

fix confirmed. will be included in 5.1.13

An entry was added to the 5.1.13 changelog:

When defining a primary key with a user-defined data type, it was not possible to set the auto-increment (AI) attribute. Clicking the AI checkbox had no effect.

To circumvent this problem it was possible to temporarily change the data type of the column to a built-in data type such as INT, set the AI attribute and then change the data type back to the desired user-defined data type. The model could then be saved. However, if this model was reopened and File, Export, Forward Engineer SQL CREATE Script used to generate a script, MySQL Workbench would crash.