Bug #7332 Cannot alter table if ENUM column is indexed.
Submitted: 15 Dec 2004 18:36 Modified: 28 May 2006 2:02
Reporter: Shuichi Tamagawa Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha (bk Dec 13) OS:Linux (SuSE Linux 9.0)
Assigned to: Ingo Strüwing CPU Architecture:Any

[15 Dec 2004 18:36] Shuichi Tamagawa
Description:
If ENUM type column is indexed, you cannot alter table with an error  -  "'ERROR 1167 (42000): The used storage engine can't index column 'c1'".

How to repeat:
mysql> create table t(c1 enum('a','b','c'), index(c1));
Query OK, 0 rows affected (0.12 sec)
mysql> alter table t add c2 int;
ERROR 1167 (42000): The used storage engine can't index column 'c1'
mysql> alter table t engine = innodb;
ERROR 1167 (42000): The used storage engine can't index column 'c1'
mysql> alter table t default charset = latin1;
ERROR 1167 (42000): The used storage engine can't index column 'c1'
[15 Dec 2004 20:03] MySQL Verification Team
Can't repeat with server (BK Dec 14):

miguel@hegel:~/dbs/5.0$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t(c1 enum('a','b','c'), index(c1));
Query OK, 0 rows affected (0.06 sec)

mysql> alter table t add c2 int;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t engine = innodb;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t default charset = latin1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
[15 Dec 2004 22:22] Shuichi Tamagawa
This happens only when the table's character set is multi byte.

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset = latin1;

Query OK, 0 rows affected (0.11 sec)

mysql> alter table t add c2 int;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset = utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> alter table t add c2 int;
ERROR 1167 (42000): The used storage engine can't index column 'c1'
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset = latin2;

Query OK, 0 rows affected (0.12 sec)

mysql> alter table t add c2 int;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset = gbk;
Query OK, 0 rows affected (0.11 sec)

mysql> alter table t add c2 int;
ERROR 1167 (42000): The used storage engine can't index column 'c1'
[16 Dec 2004 16:47] MySQL Verification Team
Verified with 5.0 BK tree.
Works fine with 4.1.
[28 May 2006 2:02] MySQL Verification Team
I was unable to repeat with current source server:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbx
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset =
    -> latin1;
Query OK, 0 rows affected (0.05 sec)

mysql>  alter table t add c2 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset = utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t add c2 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset =
    -> latin2;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t add c2 int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(c1 enum('a','b','c'), index(c1)) default charset = gbk;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t add c2 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>