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