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.
