MySQL Bugs: #38434: unique key index length

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

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.

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);

Thank you for the report.

Please provide query you use to "update the index to the unique key"

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));

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.

But these two columns are having utf-8 encoding so so shouldn't be it is 255*3 + 255*3 + 4 + 4 + 4

Thank you for the feedback.

Set to "Verified".

Any updates on this? Does status verified means this is not bug or this has been verified as a bug? 

Thanks,
Sanjeev

"Verified" means the report is considered as valid bug and will be processed.