MySQL Bugs: #36085: Auto increment option was not cleared off internally on table editor

Bug #36085	Auto increment option was not cleared off internally on table editor
Submitted:	15 Apr 2008 9:03	Modified:	14 Oct 2008 12:33
Reporter:	Choong Seng Chen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.17, 5.0.18	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	auto increment, table editor

Description:
Auto increment option was not cleared off internally when editing a column which had the option ticked (with data type eg.int) and subsequently changing the datatype to non-applicable type (eg. char).

The problem will only manifest when forward engineer the model to script.

How to repeat:
Create a table with a column "whatever" with datatype "int" and make it auto increment.
Save.
Change the table by changing its datatype to "char(10)".
Export the model to script
Inspect the generated script and you'll find that the auto-increment is still applied.

This is expected behaviour in case user changes datatype accidentally, so this setting is not lost. if user changes the datatype on purpose he should be aware of those details and has to uncheck this manually.
Workbench Standard Edition includes a validation check that informs the user about such incompatibilities.

What observed is that when the user changed the datatype (either accidentally or intentionally), the tick on that auto increment option was automatically unticked from the UI.

It would be agreeable as an expected feature if the tick remains and grayed out, however, in this case, the UI logically tells the user that it had detected that the AI is not applicable to the new datatype and thus the tick was automatically off.

A similar observation:
1) Create column with datatype INT and set the Zerofill Flag.
2) Save it.
3) Change the column to datatype VARCHAR, notice that zerofill flag is not applicable any more.
4) Change the column to datatype INT and the Zerofill flag appeared back but not set.

You might not regard it as a bug, but it is not an expected behavior from the user point of view.

Thanks!

I'm sorry for my wrong info, we have introduced this validation in SE-version after 5.0.18. 
With 5.0.19 the MySQL Validation reports an error like this:
(x) Column 'table1'.'idtable1' is set to AUTO INCREMENT which is only valid for numeric types.
But this feature is only available in SE, for OSS you just have to do that manually.
 
Whats been changed since 5.0.17 is, that the AI-Flag in the column-row is not unticked anymore. Therefore its visible to the user that this setting is still applied to that col. and will be written to the export script. The user can uncheck it manually, but is not able to set it again for types that don't support AI.

I have to agree that this behaviour is not consistent for all column properties like the mentioned ZEROFILL and i.e. UNSIGNED flags which are not set directly in the grid. For those properties (listed in the "Flags" box) the setting is lost as soon as the datatype is changed and the property is not available for the new type.
i.e. when changing a INT-column, with ZEROFILL checked, to CHAR the property is removed from the "Flags"-Listbox and when changing the datatype back to INT it shows up again cleared.

We'll discuss how to improve this.

when changing a the columntype from INT which has Auto-increment turned on the checbox is not cleared, but exported SQL-code won't have AUTO_INCREMENT in the code. also the validation feature reports the problem with an appropriate message.
fixed in 5.0.24

An entry was added to the 5.0.24 changelog:

The auto-increment flag was not cleared internally for a column, when the type of that column was changed to one for which auto-increment is invalid e.g. char. When the model was exported using Export, Forward Engineer SQL Create Script, the resulting script incorrectly retained the auto-increment flag for the changed column.