Bug #11597 Primary Keys in MS Access are generating two indices in MySQL
Submitted: 27 Jun 2005 21:47 Modified: 28 Jun 2005 1:12
Reporter: Matt Perry Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S3 (Non-critical)
Version:1.0.8 beta OS:Microsoft Windows (Windows XP)
Assigned to: Michael G. Zinner CPU Architecture:Any

[27 Jun 2005 21:47] Matt Perry
Description:
First, migration tool is great!  Thanks for making it.

If you migrate an Access table that has a primary key, it creates 2 indices in MySQL.  The first being the Primary, the second being just an index on the same field.  

This is not a critical bug, but in the Java code for the Migration Toolkit in function reverseEngineerTableIndices, it appears someone is trying to stop this from happening.... however, its still happening... 

Thanks again for this tool,
matt

How to repeat:
Create a table in Access 2000 that has a primary key.  Then import it, check and see how many indices are there.

Suggested fix:
Don't create the extra index.
[27 Jun 2005 23:56] Scott Harmon
Changing in ReverseEngineeringAccess.java method reverseEngineerTableIndices,

if ((newIndexName.compareToIgnoreCase("PrimaryKey") == 0)
	|| (newIndexName.compareToIgnoreCase("Primärschlüssel") == 0))
					continue;

to:

if ((newIndexName.compareToIgnoreCase("PrimaryKey") == 0)
	|| (newIndexName.compareToIgnoreCase("Primärschlüssel") == 0)
	|| (table.getPrimaryKey().getColumns().getItemByName(newIndexName)) != null)
					continue;

Seems to work.

Scott.
[28 Jun 2005 1:12] 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

Additional info:

Scott, thanks for the bugfix. Please notice that you had a ) placed wrong.

It should be

				//do not handle PrimaryKey indices
				if ((newIndexName.compareToIgnoreCase("PrimaryKey") == 0)
						|| (newIndexName.compareToIgnoreCase("Primary") == 0)
						|| (newIndexName.compareToIgnoreCase("Primärschlüssel") == 0)
						|| (table.getPrimaryKey().getColumns().getItemByName(
								newIndexName) != null))
					continue;

While investigating this issue i found a bug in the creation of the PK SQL code. This is now also fixed.
[28 Jun 2005 2:24] Scott Harmon
Mike,

Thanks for your prompt reply!  I was wondering what exactly is the url for the source reposity of the Migration Toolkit?

Thanks,

Scott.
[19 Sep 2007 18:04] Carlos Garces
Hi!

I have the same error using 1.1.2 under windows XP, two indexes are generated in the primary keys columns.