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:Microsoft Windows (W2K)
Assigned to: Bugs System CPU Architecture:Any

[8 Jun 2005 10:19] Mick Francis
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;
[12 Jul 2005 4:55] Jorge del Conde
Thanks for your bug report.  Tested w/1.0.7
[12 Jul 2005 8:14] Michael G. Zinner
Jorge, please test the latest version 1.0.10. This should be fixed. Thanks.
[12 Jul 2005 16:49] Jorge del Conde
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/