Bug #12732 "Incorrect sub part key" - sub part length specified for non-char column in key
Submitted: 22 Aug 2005 19:14 Modified: 23 Aug 2005 7:51
Reporter: Doug Leeper Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S2 (Serious)
Version:1.0.13rc OS:Microsoft Windows (WinXP)
Assigned to: CPU Architecture:Any

[22 Aug 2005 19:14] Doug Leeper
Description:
Appears to be duplicate of bugs: 11164 and 11120.  However, these bugs indicate it is fixed in 1.0.10.  If so, then it is broken in 1.0.13rc.

How to repeat:
Oracle 9.2 Win XP

CREATE TABLE `moguser`.`mog_auction` (
  `oid` VARCHAR(20) NOT NULL,
  `last_modified_date` DATETIME NOT NULL,
  `creation_date` DATETIME NOT NULL,
  `created_by_id` VARCHAR(50) NOT NULL,
  `last_modified_by_id` VARCHAR(50) NOT NULL,
  `auction_type` INT(2) NOT NULL,
  `status` INT(2) NOT NULL,
  `item_id` VARCHAR(20) NULL,
  `item_type` VARCHAR(30) NULL,
  `title` VARCHAR(100) NOT NULL,
  `description` MEDIUMTEXT NULL,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NOT NULL,
  `initial_price` DECIMAL(10, 2) NULL,
  `publish_reserve_price` INT(1) NULL,
  `reserve_price` DECIMAL(10, 2) NULL,
  `purchase_now_price` DECIMAL(10, 2) NULL,
  `currency` VARCHAR(5) NULL,
  `total_items` INT(6) NOT NULL,
  `items_bid` INT(6) NULL,
  `bid_count` INT(6) NULL,
  `current_price` DECIMAL(10, 2) NULL,
  `current_bid_id` VARCHAR(20) NULL,
  PRIMARY KEY (`oid`),
  INDEX `nuk_auction_created_by_id` (`created_by_id`(50)),
  INDEX `nuk_auction_item` (`item_id`(20), `item_type`(30)),
  INDEX `nuk_auction_start_time` (`start_time`(7)),
  INDEX `nuk_auction_status` (`status`)
)
ENGINE = INNODB;

I am assuming that status is the offending subpart key.
[22 Aug 2005 19:59] Doug Leeper
I downloaded 1.0.10 and the error was still there.

I then tried to decipher the error to see what was causing it and it was related to the date/datetime columns.  The migration toolkit for some reason decided to do a substring on a date/datetime column for the index part of the create table.

When I removed the '(#)' from the date/datetime column in the index script, everything worked fine.
[22 Aug 2005 20:00] Doug Leeper
Therefore the change would be to the following line:

INDEX `nuk_auction_start_time` (`start_time`(7)),

should be

INDEX `nuk_auction_start_time` (`start_time`),
[23 Aug 2005 7:51] Michael G. Zinner
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html