MySQL Bugs: #6073: ALTER table minor glich

Bug #6073	ALTER table minor glich
Submitted:	13 Oct 2004 19:24	Modified:	16 Jan 2006 21:28
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.14-nt, 4.1.15-BK	OS:	Linux (Linux, Windows)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
ALTER TABLE complains  index without prefix is not allowed for text, while index is defined with prefix:

mysql> alter table b change t t text;
ERROR 1170: BLOB/TEXT column 't' used in key specification without a key length

The funny thing is I just convered the field from text to varchar(255) a moment ago
and it worked fine. 

How to repeat:
 CREATE TABLE `b` (
  `t` varchar(255) default NULL,
  KEY `t` (`t`(80))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> alter table b change t t text;
ERROR 1170: BLOB/TEXT column 't' used in key specification without a key length

I have the same problem with 4.1.11:

mysql> alter table reference change authors authors blob default NULL;
ERROR 1170: BLOB column 'authors' used in key specification without a key length
mysql>

| reference | CREATE TABLE `reference` (
  `reference_id` bigint(20) unsigned NOT NULL auto_increment,
  `PMID` int(10) unsigned default NULL,
  `authors` varchar(255) default NULL,
  `journal` varchar(30) default NULL,
  `url` varchar(255) default NULL,
  `title` varchar(255) default NULL,
  `issue` varchar(10) default NULL,
  `volume` int(4) unsigned default NULL,
  `pages` varchar(20) default NULL,
  `year` int(4) unsigned default NULL,
  `remark` blob,
  `version` int(1) unsigned default NULL,
  `remarks_id` bigint(20) unsigned default NULL,
  PRIMARY KEY  (`reference_id`),
  KEY `authors` (`authors`(10)),
  KEY `journal` (`journal`(10)),
  KEY `title` (`title`(10)),
  KEY `year` (`year`),
  KEY `PMID` (`PMID`)
) TYPE=InnoDB |

mysql> show index from reference;
+-----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| reference |          0 | PRIMARY  |            1 | reference_id | A         |          22 |     NULL | NULL   |      | BTREE      |         |
| reference |          1 | authors  |            1 | authors      | A         |          22 |       10 | NULL   | YES  | BTREE      |         |
| reference |          1 | journal  |            1 | journal      | A         |          22 |       10 | NULL   | YES  | BTREE      |         |
| reference |          1 | title    |            1 | title        | A         |          22 |       10 | NULL   | YES  | BTREE      |         |
| reference |          1 | year     |            1 | year         | A         |          22 |     NULL | NULL   | YES  | BTREE      |         |
| reference |          1 | PMID     |            1 | PMID         | A         |          22 |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

mysql> 

mysql> drop index authors on reference; 
Query OK, 22 rows affected (0.10 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql>

mysql> alter table reference change authors authors blob default NULL;
Query OK, 22 rows affected (0.09 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql>

Actually, just a continuation ... the conversion from blob to text works fine (just continuation from my previous comment):

mysql> create index authors on reference (authors(10));
Query OK, 22 rows affected (0.14 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> describe reference;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| reference_id | bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| PMID         | int(10) unsigned    | YES  | MUL | NULL    |                |
| authors      | blob                | YES  | MUL | NULL    |                |
| journal      | varchar(30)         | YES  | MUL | NULL    |                |
| url          | varchar(255)        | YES  |     | NULL    |                |
| title        | varchar(255)        | YES  | MUL | NULL    |                |
| issue        | varchar(10)         | YES  |     | NULL    |                |
| volume       | int(4) unsigned     | YES  |     | NULL    |                |
| pages        | varchar(20)         | YES  |     | NULL    |                |
| year         | int(4) unsigned     | YES  | MUL | NULL    |                |
| remark       | blob                | YES  |     | NULL    |                |
| version      | int(1) unsigned     | YES  |     | NULL    |                |
| remarks_id   | bigint(20) unsigned | YES  |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

mysql> alter table reference change authors authors text default NULL;
Query OK, 22 rows affected (0.12 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql>

Examine if bug still exists in current tree

Looks like the behaviour described is still the same:

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.32 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE `b` (
    ->   `t` varchar(255) default NULL,
    ->   KEY `t` (`t`(80))
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.19 sec)

mysql> alter table b change t t text;
ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key length
mysql> show index from `b`;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| b     |          1 | t        |            1 | t           | A         |
  NULL |       80 | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
1 row in set (0.02 sec)

mysql> drop index t on b;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table b change t t text;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc b;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| t     | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create index t on b(t(80));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

The same results on 4.1.15-debug BK build of September 6th on Linux.

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/commits/1167

Can't repeat it against 5.0.19, added a test case.