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

[25 Feb 2005 17:57] Paul DuBois
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.
[7 Mar 2005 13:46] Antony Curtis
I think this is the same as Bug#6073
[5 Feb 2008 18:07] Omer Barnir
workaround: alter index when you alter the column, and explicitly specify the prefix width
[1 Oct 2008 22:27] Konstantin Osipov
Antony suggests this is a dup of the bug I couldn't repeat.
Please re-verify.
[3 Oct 2008 9:35] Valeriy Kravchuk
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.
[16 Oct 2008 19:23] Paul DuBois
Fix typo in synopsis.
[14 Nov 2017 12:50] Federico Razzoli
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)
[23 Aug 2023 12:49] Samantha Jones
table

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