MySQL Bugs: #11120: Incorrect syntax for creating unique index when migrating from Oracle

Bug #11120	Incorrect syntax for creating unique index when migrating from Oracle
Submitted:	6 Jun 2005 18:59	Modified:	12 Jul 2005 16:53
Reporter:	Al Willingham	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.7 Beta	OS:	Windows (Windows XP)
Assigned to:	Jorge del Conde	CPU Architecture:	Any

Description:
The migration toolkit does not create the correct mysql sql syntax for creating a unique index when migrating from Oracle 9.2 to MySQL 4.1.12. The migration toolkit does not generate an error when running the resulting script with the unique indexes and fails to create the tables with the indexes.

Toolkit sql......

CREATE TABLE `wrlscarr`.`arpu` (
  `operatorid` BIGINT(10) NOT NULL,
  `year` BIGINT(10) NOT NULL,
  `qtr1` BIGINT(10) NOT NULL,
  `qtr2` BIGINT(10) NOT NULL,
  `qtr3` BIGINT(10) NOT NULL,
  `qtr4` BIGINT(10) NOT NULL,
  `source` VARCHAR(100) NOT NULL,
  `lastchanged` DATETIME NOT NULL,
  UNIQUE INDEX `unique_arpu` (`operatorid`(22), `year`(22))
)
ENGINE = INNODB;

This is the error received when running the same script from command line:

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

This is the sql created when creating a table with a unique index using MySQL Admin 

CREATE TABLE `wrlscarr`.`test` (
  `one` BIGINT(10) NOT NULL,
  `two` BIGINT(10) NOT NULL,
  `three` BIGINT(10) NOT NULL,
  `four` VARCHAR(100) NOT NULL,
  UNIQUE `Index_1`(`one`, `two`, `three`)
)
TYPE = InnoDB;

A workaround is to manually edit the script removing the (22) created by the toolkit and running the modified script from the command line.

How to repeat:
Use the Toolkit to migrate a Oracle 9.2 table with a unique index.

Same problem occurs on non-unique index as well

Thanks for your bug report.  Tested w/latest version of MT.

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

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