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.
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.