MySQL Bugs: #11164: "Incorrect sub part key" - sub part length specified for non-char column in key

Bug #11164	"Incorrect sub part key" - sub part length specified for non-char column in key
Submitted:	8 Jun 2005 10:19	Modified:	12 Jul 2005 16:49
Reporter:	Mick Francis (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S1 (Critical)
Version:	1.0.7-beta-win32	OS:	Windows (W2K)
Assigned to:	Jorge del Conde	CPU Architecture:	Any

Description:
The CREATE TABLE SQL generated from an Oracle table whose keys include non-character columns is illegal, giving the error:

ERROR 1089 (HY000): Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys

How to repeat:
Oracle table definition:

------------------------------------------------------------------
create table T(
	cIdSelectionId VARCHAR2(26)  not null,
	cHierId        VARCHAR2(255) not null,
	cDimensionId   VARCHAR2(255) not null,
	nSize          NUMBER(4)     not null);

alter table T add constraint T_PK
primary key (cIdSelectionId, cHierId) using index;

create index IX_T_Size_HierId_SelId on T (nSize, cHierId, cIdSelectionId);

create index IX_T_cHierId on T (cHierId);
------------------------------------------------------------------
Resulting MySQL SQL:
------------------------------------------------------------------
CREATE TABLE `schema`.`t` (
  `cidselectionid` VARCHAR(26) NOT NULL,
  `chierid` VARCHAR(255) NOT NULL,
  `cdimensionid` VARCHAR(255) NOT NULL,
  `nsize` INTEGER(4) NOT NULL,
  PRIMARY KEY (`cidselectionid`, `chierid`),
  INDEX `ix_sfnidsel_chierid` (`chierid`(255)),
  INDEX `ix_sfnidsel_size_hierid_selid` (`nsize`(22), `chierid`(255), `cidselectionid`(26))
)
ENGINE = INNODB;

Thanks for your bug report.  Tested w/1.0.7

Jorge, please test the latest version 1.0.10. This should be fixed. Thanks.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/