Bug #24453 Cannot use VARCHAR field longer than 255 characters as primary key
Submitted: 21 Nov 2006 8:23 Modified: 21 Nov 2006 8:43
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.12 OS:Linux (RHEL 4.0 Upd2 X86(EM64T/AMD64))
Assigned to: CPU Architecture:Any
Tags: key, primary, varchar

[21 Nov 2006 8:23] [ name withheld ]
Description:
In MySQL 4.1.12, through the MySQL Administrator tool (but it would probably happen even without it), using a VARCHAR(65535) field as primary key, leading to the following schema manipulation script,

mysql> CREATE TABLE `docmap`.`documents` (
    ->   `title` VARCHAR (65535),
    ->   `author` VARCHAR (65535),
    ->   `edition` VARCHAR (65535),
    ->   `docdate` VARCHAR (65535),
    ->   `milestone` VARCHAR (65535),
    ->   `DTI` VARCHAR (65535),
    ->   `product` VARCHAR (65535),
    ->   `prvers` VARCHAR (65535),
    ->   `documentcode` VARCHAR (65535),
    ->   `url` VARCHAR (65535),
    ->   `fullfilepath` VARCHAR (65535),
    ->   `upload_date` VARCHAR (65535),
    ->   `uploader` VARCHAR (65535),
    ->   `pdmid` VARCHAR (65535),
    ->   PRIMARY KEY(`upload_date`)
    -> )
    -> ENGINE = MYISAM
    -> COMMENT = 'Holds the description of the documents archived in DocMap';

is NOT accepted (first problem) and produces the following incorrect (second problem) error message:

ERROR 1170 (42000): BLOB/TEXT column 'upload_date' used in key specification without a key length

Note that the message is wrong, because a key length WAS specified. The same message appears if the key field has TEXT type. 
Besides to that, this is a restriction: VARCHAR fields are accepted as primary keys only if they are up to 255 characters long. Is this restriction intentional?

How to repeat:
alter a table schema, or create a new one, so that its primary key field has VARCHAR(65535) type. When the schema modification script is executed, you get the described error

Suggested fix:
Remove the restriction on the maximum length of VARCHAR fields compatible with the status of primary key. 
Allow TEXT fields to be primary keys.
[21 Nov 2006 8:43] Hartmut Holzgraefe
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.