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: | |
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
[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.