Bug #80500 #1067 - Invalid default value for ENUM mysql
Submitted: 24 Feb 2016 17:31 Modified: 24 Jan 2018 9:44
Reporter: Chamath Gunasekara Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:mysql Ver 14.14 Distrib 5.6.17, for Win OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: enum

[24 Feb 2016 17:31] Chamath Gunasekara
Description:
I have created a table with three fields. One is primary key and others are ENUM data type fields. Both fields have same ENUM types. The table has been created successfully. Collation is utf16_unicode_ci.

CREATE  TABLE IF NOT EXISTS `test_db`.`testsk1` (
  `idtable1` INT NOT NULL ,
  `c1` ENUM('a', 'b') NULL DEFAULT 'a' ,
  `c2` ENUM('a', 'b') NULL DEFAULT 'a' ,
  PRIMARY KEY (`idtable1`) )
ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;

But after created above table, I checked ENUM fields. Those fields' values(length/set) were empty. 

Then I changed collation to utf8_unicode_ci for ENUM type fields. Then it works fine. I think I don't want to add collation as utf16_unicode_ci for ENUM types. But I used it as utf16_unicode_ci because there can be Chinese characters.

I think this is a bug if not could you please explain this scenario.

How to repeat:
Please run following script to reproduce issue.

CREATE  TABLE IF NOT EXISTS `test_db`.`testsk1` (
  `idtable1` INT NOT NULL ,
  `c1` ENUM('a', 'b') NULL DEFAULT 'a' ,
  `c2` ENUM('a', 'b') NULL DEFAULT 'a' ,
  PRIMARY KEY (`idtable1`) )
ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;

Suggested fix:
No suggestions. Could you please explain if this is not a bug.
[25 Feb 2016 8:50] Umesh Shastry
Hello Chamath,

Thank you for the report.
Observed this with 5.6.29/5.7.11 builds.

Thanks,
Umesh