| 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: | |
| 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 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)
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