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: | |
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 |
[13 Oct 2004 19:24]
Peter Zaitsev
[17 May 2005 13:43]
Martin Mokrejs
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>
[17 May 2005 13:45]
Martin Mokrejs
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>
[19 Aug 2005 8:25]
Antony Curtis
Examine if bug still exists in current tree
[11 Sep 2005 10:05]
Valeriy Kravchuk
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.
[16 Jan 2006 21:27]
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/commits/1167
[16 Jan 2006 21:28]
Konstantin Osipov
Can't repeat it against 5.0.19, added a test case.