Bug #38434 unique key index length
Submitted: 29 Jul 2008 19:15 Modified: 6 Aug 2008 15:50
Reporter: Sanjeev Kumar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S1 (Critical)
Version: 5.0.51b-community-nt MySQL Community Ed, 5.0, 5.1 BZR OS:Any (Linux, MS Windows xp)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[29 Jul 2008 19:15] Sanjeev Kumar
Description:
I am trying to create unique key index for the table: 

CREATE TABLE  `testdb`.`testtable` (
  `GID` int(10) unsigned NOT NULL auto_increment,
  `BATCH_GID` int(10) unsigned NOT NULL,
  `MEDIA_GID` int(10) unsigned NOT NULL,
  `METADATA_GID` int(10) unsigned NOT NULL,
  `METADATA_VALUE` varchar(384) default NULL,
  `ELIM_REASON` varchar(384) default NULL,
  `ELIM_COUNT` int(10) unsigned NOT NULL,
  `ELIM_SIZE` bigint(20) unsigned NOT NULL,
  `LAST_MODIFIED_AT` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `LAST_MODIFIED_BY` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`GID`),
  UNIQUE KEY `FK_MCB_DOC_ELIM_VOL_UNIQUE_KEY` USING BTREE (`BATCH_GID`,`MEDIA_GID`,`METADATA_GID`,`METADATA_VALUE`(255),`ELIM_REASON`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Since my unique key length is exceeding max key length (I think 767 Bytes for a key) so i am unable to create it. But if i first create the index key for the four columns (which are used to create unique key) and then if i update the index to the unique key, i am able to create it. Key is also working fine :

INSERT INTO mcb_doc_elim_vol (BATCH_GID, MEDIA_GID, METADATA_GID, METADATA_VALUE, ELIM_REASON, ELIM_COUNT, ELIM_SIZE)
VALUES(1, 1, 1, 'this is test-metadat-value', 'this test-elim-reason-value', 1, 1)
ON DUPLICATE KEY UPDATE
ELIM_COUNT = VALUES(ELIM_COUNT),
ELIM_SIZE = VALUES(ELIM_SIZE);

I tried to search on MySQL Manual but couldn't get why it is happening. 

How to repeat:
First create a index key in the above mentioned table for the four columns, used to create the unique.
[29 Jul 2008 19:18] Sanjeev Kumar
Insert query should be :

INSERT INTO testtable(BATCH_GID, MEDIA_GID, METADATA_GID, METADATA_VALUE,
ELIM_REASON, ELIM_COUNT, ELIM_SIZE)
VALUES(1, 1, 1, 'this is test-metadat-value', 'this test-elim-reason-value', 1, 1)
ON DUPLICATE KEY UPDATE
ELIM_COUNT = VALUES(ELIM_COUNT),
ELIM_SIZE = VALUES(ELIM_SIZE);
[29 Jul 2008 19:59] Sveta Smirnova
Thank you for the report.

Please provide query you use to "update the index to the unique key"
[29 Jul 2008 20:20] Sanjeev Kumar
It is actually dropping the index key first then creating unique key.

ALTER TABLE `testtable` DROP INDEX `UNIQUE_KEY`,
 ADD INDEX UIQUE_KEY(`BATCH_GID`, `MEDIA_GID`, `METADATA_GID`, `METADATA_VALUE`, `ELIM_REASON`);

Warning: Specified key was too long; max key length is 765 bytes
Warning: Specified key was too long; max key length is 765 bytes

ALTER TABLE `testtable` DROP INDEX `UNIQUE_KEY`,
 ADD UNIQUE `UNIQUE_KEY`(`BATCH_GID`, `MEDIA_GID`, `METADATA_GID`, `METADATA_VALUE`(255), `ELIM_REASON`(255));
[29 Jul 2008 20:30] Sveta Smirnova
Thanl you for the feedback.

> ALTER TABLE `testtable` DROP INDEX `UNIQUE_KEY`,
> ADD UNIQUE `UNIQUE_KEY`(`BATCH_GID`, `MEDIA_GID`, `METADATA_GID`,
> `METADATA_VALUE`(255), `ELIM_REASON`(255));

You specified length of METADATA_VALUE and ELIM_REASON, so actual length of unique key is 255 + 255 + 4 + 4 + 4 = 522 < 767

So I close this report as not a bug.
[29 Jul 2008 20:34] Sanjeev Kumar
But these two columns are having utf-8 encoding so so shouldn't be it is 255*3 + 255*3 + 4 + 4 + 4
[30 Jul 2008 16:03] Sveta Smirnova
Thank you for the feedback.

Set to "Verified".
[6 Aug 2008 15:50] Sanjeev Kumar
Any updates on this? Does status verified means this is not bug or this has been verified as a bug? 

Thanks,
Sanjeev
[6 Aug 2008 17:55] Sveta Smirnova
"Verified" means the report is considered as valid bug and will be processed.