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 11:51]
Victoria Alaya
[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)