Bug #72958 ENUM with default value set generates Errno=1067
Submitted: 11 Jun 2014 11:51 Modified: 11 Jun 2014 16:55
Reporter: Victoria Alaya Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.5.32-cll-lve OS:Linux (Shared hoster custom build)
Assigned to: CPU Architecture:Any
Tags: enum default values

[11 Jun 2014 11:51] Victoria Alaya
Description:
Unable to create a table with a specific default enum value, such as:

CREATE TABLE evo_groups (
	      grp_perm_blogs                   enum('user','viewall','editall') COLLATE ascii_bin NOT NULL default 'user',
	      grp_perm_stats                   enum('none','user','view','edit') COLLATE ascii_bin NOT NULL default 'none',
	    ) ENGINE = innodb DEFAULT CHARSET = utf8

This is somebody else's code (blog engine) but I cannot create any similar lines manually or with PHPMyAdmin either. The error is always the same:

Error #1067: Invalid default value for 'grp_perm_blogs'

If I try and create a row with default empty value, I cannot change it later to any of the specified items either. The charset and COLLATE do not really matter. DATABASE default COLLATE is utf8_general_ci but ascii_bin specified in the line above or not, the result is always the same!

How to repeat:
The above did not produce the same results for the folks I reported it to (engine authors & volunteer supporters).

If it makes any difference, my hoster's default database creation tool creates tables in cp2151 and collate cp1251_general_ci

The fellow also testing the above engine said:
"the only way that I found to achieve something like that was setting the database collation to cp1251_bin (or cp1251_general) before the first install, but the upgrade ran smooth" and everything works unlike me.

I do not know any ways to work around it and I really need that engine version.

Suggested fix:
There were similar problems reported before, long ago, all reported as fixed:

http://bugs.mysql.com/bug.php?id=14280
http://bugs.mysql.com/bug.php?id=20108
http://bugs.mysql.com/bug.php?id=44368
[11 Jun 2014 13:27] Victoria Alaya
I actually managed to get the initial script to work by removing COLLATE ascii_bin so collate encoding WAS the problem indeed.

Sorry, I abriged it above without testing and it presents errors. Here's the minimal original non-working rows list

CREATE TABLE evo_groups (
	 
	      grp_ID                           int(11) NOT NULL auto_increment,
	 
	      grp_name                         varchar(50) NOT NULL default '',
	 
	      grp_perm_blogs                   enum('user','viewall','editall') COLLATE ascii_bin NOT NULL default 'user',
	 
	      grp_perm_stats                   enum('none','user','view','edit') COLLATE ascii_bin NOT NULL default 'none',
	 
	      PRIMARY KEY grp_ID (grp_ID)
	 
	    ) ENGINE = innodb DEFAULT CHARSET = utf8

And it works without "COLLATE ascii_bin" in enum properties. So this different charset seems to be causing problems.
[11 Jun 2014 14:26] Peter Laursen
I can without problems create the table

CREATE TABLE `evo_groups` (
  `grp_ID` int(11) NOT NULL AUTO_INCREMENT,
  `grp_name` varchar(50) NOT NULL DEFAULT '',
  `grp_perm_blogs` enum('user','viewall','editall') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'user',
  `grp_perm_stats` enum('none','user','view','edit') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'none',
  PRIMARY KEY (`grp_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

.  on official MySQL 5.5.38  as well as on MariaDB same version (both having utf8 default charset).

-- Peter
-- not a MySQL person
[11 Jun 2014 16:55] MySQL Verification Team
Thank you for the bug report.

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > USE test
Database changed
mysql 5.5 > CREATE TABLE evo_groups (
    ->
    ->        grp_ID                           int(11) NOT NULL auto_increment,
    ->
    ->        grp_name                         varchar(50) NOT NULL default '',
    ->
    ->        grp_perm_blogs                   enum('user','viewall','editall') COLLATE ascii_bin NOT NULL default 'user',
    ->
    ->        grp_perm_stats                   enum('none','user','view','edit') COLLATE ascii_bin NOT NULL default 'none',
    ->
    ->        PRIMARY KEY grp_ID (grp_ID)
    ->
    ->      ) ENGINE = innodb DEFAULT CHARSET = utf8;
Query OK, 0 rows affected (0.28 sec)

mysql 5.5 > SHOW COLLATION LIKE "ASCII%";
+------------------+---------+----+---------+----------+---------+
| Collation        | Charset | Id | Default | Compiled | Sortlen |
+------------------+---------+----+---------+----------+---------+
| ascii_general_ci | ascii   | 11 | Yes     | Yes      |       1 |
| ascii_bin        | ascii   | 65 |         | Yes      |       1 |
+------------------+---------+----+---------+----------+---------+
2 rows in set (0.01 sec)