I have the following table:
mysql> show create table keyword_links\G
*************************** 1. row ***************************
Table: keyword_links
Create Table: CREATE TABLE `keyword_links` (
`keyword_id` int(11) NOT NULL AUTO_INCREMENT,
`keyword` tinytext NOT NULL,
`link` tinytext,
`weight` smallint(6) NOT NULL DEFAULT '0',
`class_id` smallint(6) NOT NULL DEFAULT '0',
`category_id` smallint(6) NOT NULL DEFAULT '0',
`timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`keyword_id`),
KEY `class_id` (`class_id`),
KEY `category_id` (`category_id`),
KEY `idx_keyword` (`keyword`(333))
) ENGINE=MyISAM AUTO_INCREMENT=5082 DEFAULT CHARSET=utf8
to which I am trying to add a new column, which is failing:
mysql> ALTER TABLE keyword_links ADD COLUMN list_id INT UNSIGNED NOT NULL DEFAULT 0;
ERROR 1170 (42000): BLOB/TEXT column 'keyword' used in key specification without a key length
The index on keyword
column does have a key length of 333, so why is it failing and how to fix it?
UPDATE
I tried reducing the size of the index on the keyword
column from 333 to 255 and now I am able to add the new column successfully:
ALTER TABLE keyword_links DROP INDEX idx_keyword;
CREATE INDEX index_keyword ON keyword_links (keyword(255));
ALTER TABLE keyword_links ADD COLUMN list_id INT UNSIGNED NOT NULL DEFAULT 0;
But I would still like to know what's going on.
This is because of myisam's index's length limit of 1000 bytes (see this link)
Please note that this is all about bytes, and that a character might take 2, 3 or even 4 bytes depending on your encoding, and also that an INT will always be 4 bytes.