Bug #35561 Indexes don't work correctly with TEXT fields

• Submitted: 25 Mar 2008 21:12
• Modified: 26 Mar 2008 0:10
• Reporter: Eugene Kapun
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Windows
• CPU Architecture: Any

[25 Mar 2008 21:12] Eugene Kapun

Description:
When I try to ALTER TABLE which has index on TEXT column, sometimes I get an error:
ERROR 1170 (42000): BLOB/TEXT column 'xxx' used in key specification without a
key length
Example:

mysql> create table test (test tinytext not null, primary key (test(100)), x int
 not null);
Query OK, 0 rows affected (0.02 sec)

mysql> alter table test drop column x;
ERROR 1170 (42000): BLOB/TEXT column 'test' used in key specification without a
key length

How to repeat:
Type this into MySQL client:

create table test (test tinytext not null, primary key (test(100)), x int not null);
alter table test drop column x;

[25 Mar 2008 21:21] Valeriy Kravchuk

What exact version, 5.0.x, you had used? I can not repeat the behaviour described with 5.0.56, for example:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.56-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> drop table test;
Query OK, 0 rows affected (0.31 sec)

mysql> create table test (test tinytext not null, primary key (test(100)), x int
 not null);
Query OK, 0 rows affected (0.16 sec)

mysql> alter table test drop column x;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

[25 Mar 2008 21:42] Eugene Kapun

I used 5.0.27-community-nt and 5.0.51a-community-nt.

[26 Mar 2008 0:10] MySQL Verification Team

Thank you for the feedback. I am not able to repeat with today Windows
source server, so it was fixed somehow. Please wait for the next release.

c:\dbs>5.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.60-nt Source distribution

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

mysql> create table test (test tinytext not null, primary key
    -> (test(100)), x int
    ->  not null) engine InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> alter table test drop column x;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>