Bug #14640 unique indexes aren't handles correctly
Submitted: 4 Nov 2005 13:28 Modified: 24 Nov 2005 13:31
Reporter: Antony Mandilas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S1 (Critical)
Version:1.0.20-rc OS:Microsoft Windows (winXP)
Assigned to: CPU Architecture:Any

[4 Nov 2005 13:28] Antony Mandilas
Description:
i am trying the migration from 4.0.21 to 5.0.1, in both systems the data is stored  using the innodb engine.

In order to have foreign keys an index should be created in the target table, which is not a "unique" index type, just an index for the values. 

When the same value exists in the foreign key column, more than once then the migration fails with the following message:

          Duplicate entry '1' for key XXXX
          YYY row(s) transfered.

but nothing is transfered and the key that is supposed to have duplicated entries should not be a unique type key. In the root system it is a simple index, so there could be stored duplicated values. But checking the target system schema, the indexthat is created is Unique type instead of Index type.

How to repeat:
try a migration between two databases, mysql and innodb used, that has two tables which are connected using foreign keys.

in the first table that is connected to the second table using a FK, try to enter more that one times the same value as the foreign key value.

so if the second table has 3 values with indexes (1,2,3). In the first table, the column that holds the foreing keys should have as values like the following

1,1,1,2,3,2,1

Suggested fix:
check the creation of the target schema so that it meets the root shcema.
[10 Nov 2005 18:43] Bjorn Persson
I have been able to duplicate problem migrating from MySQL 4.0.26 under WinXP to 4.0.24 under NetWare 6. I.e. all my indexes become unique and I can't migrate data.
[22 Nov 2005 20:09] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this problem with the latest MT version
[22 Nov 2005 20:10] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this problem with the latest MT version
[24 Nov 2005 13:31] 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