MySQL Bugs: #46067: Cannot ALTER TABLE when key prefix too long

Bug #46067	Cannot ALTER TABLE when key prefix too long
Submitted:	9 Jul 2009 0:40	Modified:	21 Feb 2011 12:01
Reporter:	Sean Hunt	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.75, 5.0, 5.1 bzr	OS:	Linux (Ubuntu )
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	regression

Description:
When there exists a key on a TEXT/BLOB field with a prefix longer than the minimum number of characters (as per the charset), attempting to ALTER TABLE will cause error 1170 - the error you normally get when creating an index without a prefix length when it needs one.

Workarounds:

Shorten the key to the minimum number of characters for your charset (e.g. 85 in a TINYTEXT for utf8).

DROP the key before doing any ALTER TABLE, then ADD it again afterwards.

Use a fixed-width field.

Notes:

Applies in both InnoDB and MyISAM, haven't tested for other engines but I assume it's there too.

How to repeat:
CREATE TABLE foo (bar TINYTEXT, KEY (bar(100)));
ALTER TABLE foo /* Anything but DROP KEY bar */;

Suggested fix:
Either make it an error to create keys that are too long, or ignore them when processing irrelevant queries.

Thank you for the report.

I can not repeat described behavior with current development sources. Please indicate accurate minor version of MySQL you are using.

All right.

Thank you for the feedback.

I still can not repeat described behavior in my environment. Please try with current version 5.0.83 and if problem still exists provide your configuration file and output of SHOW CREATE TABLE foo - I want to check table create options.

Ah, I forgot the most important part of the CREATE TABLE statement - the collation that produces the bug!

Here's my queries (with server and client default charset of utf8 and server default collation of utf8_general_ci):

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.10 sec)
    
mysql> use test
Database changed
mysql> CREATE TABLE foo (bar TINYTEXT, KEY (bar(100)));
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE foo ADD baz INT;
ERROR 1170 (42000): BLOB/TEXT column 'bar' used in key specification without a key length
mysql> SHOW CREATE TABLE foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `bar` tinytext,
  KEY `bar` (`bar`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Thank you for the report.

Verified as described.

Only versions 5.0 and 5.1 affected.

Really bug fixed in version 5.4.4, but set to "Verified", because can not find report this can be marked as duplicate.

Problem is repeatable with MyISAM storage engine as well.

Verified as described by using actual bzr tree from MySQL 5.1.

Test:

CREATE DATABASE blubb CHARACTER SET UTF8;

USE blubb;

CREATE TABLE foo (bar TINYTEXT, KEY (bar(100)));

ALTER TABLE foo ADD baz INT;

Workaround should be add key after column

Issue has been fixed in 5.4

Please do not close bug reports as "Won't fix" on the basis that it is not repeatable in the current development version.

It requires addition of a test case, as the very least.

Sveta, on what basis is this a regression?
Did you check that it is not repeatable in 4.1?

Kostja,

yes, I checked it with 4.1

Tried to reproduce with the following trees:
mysql-5.1-bugteam => test failed
mysql-next-mr-bugfixing => test failed
mysql-6.0-codebase-bugfixing => test succeeded

Tried to reproduce again:
mysql-5.1-bugteam => test failed
mysql-next-mr-bugfixing => test succeeded
mysql-next-4284 => test succeeded

This bug is only present in 5.1, not in 5.5+
A patch has been pushed to 5.5+ with a regression test.
Closing the bug as "Won't fix" as it won't be fixed in 5.1.

Test suite changes only. No changelog entry needed.