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

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:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

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.

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.

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`),

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