MySQL Bugs: #44368: Invalid default value for ENUM fields

Bug #44368	Invalid default value for ENUM fields
Submitted:	20 Apr 2009 17:38	Modified:	6 Aug 2009 9:24
Reporter:	Richard Piatkowski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.30 SE Revision 3384	OS:	Windows (SP 2)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	CHECKED, enum default values

Description:
If you declare a column of a table as ENUM with possible values ‘y’ and ‘n’ and you set the default value for that column to one of those values like in the screenshot named Table.png, you are not able to validate this database in forward engineering. 

The second screenshot “Forward Engineer Schema.png” shows the upcoming message, indicating an “Invalid default value ‘n’ for column `table1` . `table1_enum`”.

There is no way for me to declare an ENUM field with a valid default value. Even a try to define the numerical keys for the chosen values as defaults, ends up in the failing validation.

How to repeat:
The bug can be repeated by following the instructions under 'description'.

Image of the example table

Attachment: Table.png (image/x-png, text), 3.40 KiB.

Forward engineering schema

Attachment: Forward Engineer Schema.png (image/x-png, text), 20.22 KiB.

*.mwb file in order to easily reproduce the error.

Attachment: example.mwb (application/x-zip-compressed, text), 3.17 KiB.

Thank you for the bug report. Verified just as described. As a workaround you may use OSS version that does not perform validation and create correct script like:

...
-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `c1` ENUM('a', 'b') NULL DEFAULT 'a' ,
  PRIMARY KEY (`idtable1`) )
ENGINE = InnoDB;

Fixed in 5.1 and 5.2 branches.

Screenshot of the missing "column"-information.

Attachment: 30.07.png (image/png, text), 4.82 KiB.

The errror persists in different templates. I am missing the information, which column an index refers to. The "column"-column is simply blank. In the example screenshot attached, the index column should list the field it refers to.

the issue on default values for enum columns has been fixed. fix will be in 5.1.17.

the other issue reported in the last 2 comments is a seperate issue which has already been reported in http://bugs.mysql.com/bug.php?id=41241.

An entry was added to the 5.2.3 and 5.1.17 changelogs:

In the Columns tab of the Table Editor, if the column was of type ENUM and was given a default value, then when an attempt was made to forward engineer the schema it would not validate.