Bug #10543 Incorrect error when trying to convert varchar column having index to tinytext.
Submitted: 11 May 2005 11:40 Modified: 24 Jun 2005 4:26
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.5 Beta OS:Microsoft Windows (Windows 2003)
Assigned to: Jim Winstead

[11 May 2005 11:40] Disha
Description:
If a table has a column defined as varchar having an index defined and we try to modify the column and convert it to tiny text then incorrect error is displayed.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create a table with varchar column and create an index on it by specifying index length.
   create table t1(f1 varchar(65500))//
   create index index1 on t1(f1(10))//
   
4. Now convert the varchar column to tinytext using the alter table command as follows:
   alter table t1 modify f1 tinytext//
   
Expected Results: 
1. The column should get converted to tinytext without error and by retaining the index.

Actual Results: 
1. An incorrect error is displayed as:
ERROR 1170 (42000): BLOB/TEXT column 'f1' used in key specification without a key length
[17 May 2005 22:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24995
[26 May 2005 1:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25287
[2 Jun 2005 16:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25523
[10 Jun 2005 4:59] Jim Winstead
Fixed in 5.0.8.

Note that this introduces a slight change in behavior that may be worth documenting: if a key is defined to be the same length as a field (or is left to default to that), it will continue to be the same length as the field when that field changes size. The size of the key used to not change for some field types, like VARCHAR.
[24 Jun 2005 4:26] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.8 changelog; closed bug.