Bug #37835 Primary Keys migrated from Oracle in the wrong order
Submitted: 3 Jul 2008 9:58 Modified: 23 Oct 2008 10:02
Reporter: Jérôme Louis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S1 (Critical)
Version:1.1.12 OS:Windows
Assigned to: Mike Lischke CPU Architecture:Any
Tags: foreign keys, order, Primary Keys

[3 Jul 2008 9:58] Jérôme Louis
Description:
When migrating my tables from Oracle to MySQL, some tables are not created for a strange reason. Here is the reason :

it seems that, when a table as more than 2 primary keys, the generated MySQL script doesn't keep the order of these primary keys, which is a problem because the order is kept in the foreign keys using these primary keys. The order not being the same, an error is generated.

How to repeat:
Create 2 simple Oracle tables :

CREATE TABLE FOO (
  COL1 INT NOT NULL,
  COL2 INT NOT NULL,
  COL3 INT NOT NULL,
  PRIMARY KEY (COL1, COL2, COL3)
);

CREATE TABLE BAR (
  COL1 INT NOT NULL,
  COL2 INT NOT NULL,
  COL3 INT NOT NULL,
  CONSTRAINT BAR_FOO_FK FOREIGN KEY (COL1, COL2, COL3)
    REFERENCES FOO(COL1, COL2, COL3)
);

Then, use the MTK to generate the creation script. Here is the one I generated :

-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `fcs`
  CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `fcs`;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `fcs`.`bar`;
CREATE TABLE `fcs`.`bar` (
  `col1` DECIMAL(22, 0) NOT NULL,
  `col2` DECIMAL(22, 0) NOT NULL,
  `col3` DECIMAL(22, 0) NOT NULL,
  CONSTRAINT `bar_foo_fk` FOREIGN KEY `bar_foo_fk` (`col1`, `col2`, `col3`)
    REFERENCES `fcs`.`foo` (`col1`, `col2`, `col3`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS `fcs`.`foo`;
CREATE TABLE `fcs`.`foo` (
  `col1` DECIMAL(22, 0) NOT NULL,
  `col2` DECIMAL(22, 0) NOT NULL,
  `col3` DECIMAL(22, 0) NOT NULL,
  PRIMARY KEY (`col3`, `col2`, `col1`)
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF

You can see the difference :
- on table "foo", we have "PRIMARY KEY (`col3`, `col2`, `col1`)". The order is not the same as in Oracle
- on table "bar", we have "REFERENCES `fcs`.`foo` (`col1`, `col2`, `col3`)". The order is the same as in Oracle

The execution of the script generates an error : 
"Can't create table '.\fcs\foo.frm' (errno: 150)"

If you change the "PRIMARY KEY (`col3`, `col2`, `col1`)" to "PRIMARY KEY (`col1`, `col2`, `col3`)" in the script, the creation is successful.
[3 Jul 2008 18:01] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with Oracle XE (10.2.x).
[8 Aug 2008 16:20] Jérôme Louis
It seems that the MTK is using the COLUMN ID to sort the columns in the PK.
[11 Aug 2008 7:51] Jérôme Louis
Here's an update. It seems there's also a problem while migrating tables with two keys or more when the Column Id is not in the right order :

CREATE TABLE BAZ (
  COL2 INT NOT NULL,
  COL1 INT NOT NULL,
  PRIMARY KEY (COL1, COL2)
);

CREATE TABLE FOO (
  COL1 INT NOT NULL,
  COL2 INT NOT NULL,
  PRIMARY KEY (COL1, COL2)
);

migrates into :

DROP TABLE IF EXISTS `fcs`.`baz`;
CREATE TABLE `fcs`.`baz` (
  `col2` DECIMAL(22, 0) NOT NULL,
  `col1` DECIMAL(22, 0) NOT NULL,
  PRIMARY KEY (`col1`, `col2`)
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS `fcs`.`foo`;
CREATE TABLE `fcs`.`foo` (
  `col1` DECIMAL(22, 0) NOT NULL,
  `col2` DECIMAL(22, 0) NOT NULL,
  PRIMARY KEY (`col2`, `col1`)
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

You can see that, for table baz, that the migrated order is not right. It seems that MTK is using the column id instead of the primary key position to order the primary key.

It doesn't seem that it's the same problem as the original bug of this submission, because the order was right in that one.
[12 Sep 2008 14:28] Team DBA MySQL Infocamere
We have a similar problem with Mysql Migration toolkit.
Source: Oracle Enterprise Edition 10.2.0.3 64bit on HP-UX O.S.
Target: Mysql 5.1.24 on Red Hat Linux 5

The Primary key's columns Order on mysql is wrong. 
We receive en error during Foreign key creation.
In Oracle the order of table's columns is the same on the PK definition and on the FK edefinition.
Now we mast analyze the Mysql DDL to find the migration error, because this is a performance problem on mysql, the index on primary key have not the right order so many query may have a performance degradetion. 
Is important to warn all user about this problem, is a dangerous problem for query performance.
[14 Oct 2008 15:48] Jérôme Louis
I tried looking at the code and I found a way to resolve this problem (while waiting fort a official fix)

You just have to modify ReverseEngineeringOracle.java in the java/com/mysql/grt/modules directory, and then compile it.

The modifications are :
on lines 428 and 435 :
replace : + "ORDER BY c.TABLE_NAME";
whith : + "ORDER BY c.TABLE_NAME, i.COLUMN_POSITION";

I will try to post the modified .java and .class files.
[14 Oct 2008 15:50] Jérôme Louis
Corrections (see my comment from Oct 14th 2008)

Attachment: ReverseEngineeringOracle.zip (unknown/unknown, text), 21.48 KiB.

[23 Oct 2008 10:02] Mike Lischke
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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Jérôme, thank you providing the patch.