Bug #12313 MT generates wrong sql when migrating multicolumn indexes
Submitted: 2 Aug 2005 7:38 Modified: 2 Aug 2005 15:13
Reporter: Thorbj?rn Weidemann Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S2 (Serious)
Version:1.0.11 beta OS:Microsoft Windows (Win XP)
Assigned to: CPU Architecture:Any

[2 Aug 2005 7:38] Thorbj?rn Weidemann
Description:
Trying to migrate from Progress 9.1D05 to Mysql 5.0.10a-beta-nt, using generic jdbc.

If a table in Progress contains indexes spanning multiple columns, MT generates wrong sql. Sounds to me like bug http://bugs.mysql.com/bug.php?id=11002 which you weren't able to reproduce. Hope I can provide you with enough information to do so.

Please read my thread on forum: http://forums.mysql.com/read.php?104,37224 where I describe some changes I had to make to MT xml to make it this far in the migration process.

This is the sql generated by MT for a single table. As you can see the sql for multi-column index bestilling_egenskab is wrong, and mysql complains:

Initializing JDBC driver ... 
Driver class MySQL JDBC Driver 3.1
Opening connection ... 
Connection jdbc:mysql://localhost:3306/?user=<user>&password=<password>&useServerPrepStmts=false
Deactivate foreign key constraints.
SET FOREIGN_KEY_CHECKS = 0;
Drop schema pub if it already exists.
DROP DATABASE IF EXISTS `pub`;
Creating schema pub ...
CREATE DATABASE `pub`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Creating tables ...
Creating table bestilling_egenskab ...
CREATE TABLE `pub`.`bestilling_egenskab` (
  `book_refnr` BIGINT(10) NOT NULL,
  `egenskabsnr` BIGINT(10) NOT NULL,
  `book_sekvensnr` BIGINT(10) NOT NULL,
  INDEX `bestilling_egenskab` (`book_refnr`),
  INDEX `bestilling_egenskab` (`book_sekvensnr`),
  INDEX `bestilling_egenskab` (`egenskabsnr`),
  INDEX `book_refnr` (`book_refnr`),
  INDEX `egenskabsnr` (`egenskabsnr`)
)
ENGINE = INNODB;
An error occured while executing the SQL statement.
Duplicate key name 'bestilling_egenskab'
Creating views ...
Creating procedures ...
Reactivate foreign key constraints.
SET FOREIGN_KEY_CHECKS = 1;

How to repeat:
Create this table in Progress 9.1D05 and try to migrate it with MTm using generic jdbc. This sql was extracted from progress using the mpro tool.

DROP TABLE bestilling_egenskab.
CREATE TABLE bestilling_egenskab (
  book_refnr integer FORMAT '->,>>>,>>9' DEFAULT 0 NOT NULL,
  book_sekvensnr integer FORMAT '>>>>>>9' DEFAULT 1 NOT NULL,
  egenskabsnr integer FORMAT '->,>>>,>>9' NOT NULL
).

CREATE INDEX bestilling_egenskab ON bestilling_egenskab (book_refnr, book_sekvensnr, egenskabsnr).
CREATE INDEX book_refnr ON bestilling_egenskab (book_refnr).
CREATE INDEX egenskabsnr ON bestilling_egenskab (egenskabsnr).

Suggested fix:
Generate correct sql for multicolumn indexes.
[2 Aug 2005 15:13] 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