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 18:36]
Shuichi Tamagawa
[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>