MySQL Bugs: #8814: ALTER error: BLOB/TEXT column 't' used in key specification without a key

Bug #8814	ALTER error: BLOB/TEXT column 't' used in key specification without a key
Submitted:	25 Feb 2005 17:57	Modified:	3 Oct 2008 9:35
Reporter:	Paul DuBois	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	3.23, 4.0, 4.1, 5.0, 5.0.66a	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When specifying a prefix value for a BLOB or TEXT column index,
the prefix value is always remembers. For VARCHAR (or more generally,
non-BLOB/TEXT string columns), the prefix value is remembered only
if the prefix is less than the column length. If the prefix is the same as
the column length, it's forgotten. One consequence of this is that if
you later widen the column, you also implicitly widen the index, which
isn't necessarily what you want. Another consequence is that it can
lead users to believe that a foreign key column actually can be indexed
with a prefix, contrary to the documentation. (What actually happens
is that the prefix value is forgotten, so as far as InnoDB is concerned,
the column has no prefix. But what the user remembers is that he
*did* specify an index and InnoDB allowed it to be in the FK anyway.
See Bug #8429 for an instance of where this fooled a user.)

How to repeat:
Here's a test script.  For the BLOB and TEXT columns, the prefix
information is retained, even though the prefix length is the column
length.  VARCHAR is treated differently, which is inconsistent. The
ALTER TABLE and subsequence SHOW CREATE demonstrate that
the index on c1 gets widened implicitly when c1 is widened, even
though that was not requested. (I discussed this with Brian on IRC,
who agreed that widening the index is an unexpected result.)

DROP TABLE IF EXISTS t;
CREATE TABLE t (
    c1 VARCHAR(128), INDEX (c1(128)),
    c2 VARCHAR(255), INDEX (c2(255)),
    b TINYBLOB, INDEX (b(255)),
    t TINYTEXT, INDEX (t(255))
);
SHOW CREATE TABLE t\G
ALTER TABLE t MODIFY c1 VARCHAR(255);
SHOW CREATE TABLE t\G

The output of the script looks like this:

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE t (
    ->     c1 VARCHAR(128), INDEX (c1(128)),
    ->     c2 VARCHAR(255), INDEX (c2(255)),
    ->     b TINYBLOB, INDEX (b(255)),
    ->     t TINYTEXT, INDEX (t(255))
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` varchar(128) default NULL,
  `c2` varchar(255) default NULL,
  `b` tinyblob,
  `t` tinytext,
  KEY `c1` (`c1`),
  KEY `c2` (`c2`),
  KEY `b` (`b`(255)),
  KEY `t` (`t`(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE t MODIFY c1 VARCHAR(255);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` varchar(255) default NULL,
  `c2` varchar(255) default NULL,
  `b` tinyblob,
  `t` tinytext,
  KEY `c1` (`c1`),
  KEY `c2` (`c2`),
  KEY `b` (`b`(255)),
  KEY `t` (`t`(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Don't forget the prefix length.

I think this is the same as Bug#6073

workaround: alter index when you alter the column, and explicitly specify the prefix width

Antony suggests this is a dup of the bug I couldn't repeat.
Please re-verify.

I can't repeat bug #6073 with 5.0.66a, for example, but this test case:

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

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

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t (
    ->     c1 VARCHAR(128), INDEX (c1(128)),
    ->     c2 VARCHAR(255), INDEX (c2(255)),
    ->     b TINYBLOB, INDEX (b(255)),
    ->     t TINYTEXT, INDEX (t(255))
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` varchar(128) default NULL,
  `c2` varchar(255) default NULL,
  `b` tinyblob,
  `t` tinytext,
  KEY `c1` (`c1`),
  KEY `c2` (`c2`),
  KEY `b` (`b`(255)),
  KEY `t` (`t`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

mysql> ALTER TABLE t MODIFY c1 VARCHAR(255);
ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key
 length

still proves a bug for me.

Fix typo in synopsis.

Still in 8.0.3.

mysql> CREATE TABLE t (
    ->     c1 VARCHAR(10), INDEX (c1(10)),
    ->     c2 VARCHAR(50), INDEX (c2(50)),
    ->     b TINYBLOB, INDEX (b(50)),
    ->     t TINYTEXT, INDEX (t(50))
    -> );
Query OK, 0 rows affected (0.50 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` varchar(10) DEFAULT NULL,
  `c2` varchar(50) DEFAULT NULL,
  `b` tinyblob,
  `t` tinytext,
  KEY `c1` (`c1`),
  KEY `c2` (`c2`),
  KEY `b` (`b`(50)),
  KEY `t` (`t`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> ALTER TABLE t MODIFY c1 VARCHAR(50);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` varchar(50) DEFAULT NULL,
  `c2` varchar(50) DEFAULT NULL,
  `b` tinyblob,
  `t` tinytext,
  KEY `c1` (`c1`),
  KEY `c2` (`c2`),
  KEY `b` (`b`(50)),
  KEY `t` (`t`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

table

Attachment: table.pdf (application/pdf, text), 60.50 KiB.