MySQL Bugs: #11597: Primary Keys in MS Access are generating two indices in MySQL

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:	Windows (Windows XP)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

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.

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.

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.

Mike,

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

Thanks,

Scott.

Hi!

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