MySQL Bugs: #15807: MS Access autoincrement primaries a converted to non autoincrement ints

Bug #15807	MS Access autoincrement primaries a converted to non autoincrement ints
Submitted:	16 Dec 2005 9:49	Modified:	13 Jun 2006 12:48
Reporter:	Dom de Wild	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.21 rc	OS:	Windows (Windows 2000/XP)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
MS Access autoincrement primary keys are mapped to non-autoincrement int(10) fields.

Inside MS Access these fields are of datatype 4 (integer) and carry a property called "Attributes" with the value 17. For integer fields of non-autoincrement type the value of the "Attributes" property is 1. 

Following the result after object mapping. Inside MS Access the field named 'id' is defined with datatype 4 and "Attributes" property value 17:

DROP TABLE IF EXISTS `testen`.`testen_access_intern`;
CREATE TABLE `testen`.`testen_access_intern` (
  `id` INT(10) NOT NULL,
  `JaNein` TINYINT(1) NOT NULL,
  `testen` VARCHAR(50) NULL,
  `zeitstempel` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `id` (`id`)
)
ENGINE = INNODB;

How to repeat:
Create a table in MS Access with an autoincrement primary key field and migrate it with Migration Toolkit 1.0.21rc.

Suggested fix:
Map MS Access fields with datatype 4 and "Attributes" property value 17 to MySQL INT(10) autoincrement field.

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

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `db1`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `db1`.`mytb`;
CREATE TABLE `db1`.`mytb` (
  `col1` INT(10) NOT NULL,
  `col2` VARCHAR(50) NULL,
  PRIMARY KEY (`col1`)
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 1;

This seems to be a bug in MigrationGeneric, here's a diff with a possible fix (could be improved).  But basically the contains won't work because you are looking in an indexlist and not a referedcolumnlist.

(I can't attach files so here is the diff)

--- MigrationGeneric-orig.java	2006-01-10 19:39:49.000000000 -0600
+++ MigrationGeneric.java	2006-01-10 19:31:14.000000000 -0600
@@ -388,8 +388,7 @@
 					// if this table has a PK
 					if (targetTable.getPrimaryKey() != null) {
 						// and the current column is not part of the PK
-						if (!targetTable.getPrimaryKey().getColumns().contains(
-								col)) {
+						if (!targetTable.getPrimaryKey().getColumns().get(0).getReferedColumn().equals(col)) {
 							// reset AutoInc
 							col.setAutoIncrement(0);
 						} else {

The same behavior is evident in 1.0.22 rc (at least when migrating data from MS Access2003).  Any estimate as to when this will be fixed?

has anyone managed to fix this problem?

As of the 1.0.25 release this bug has not been fixed -- at least under WinXP.  It's been 4 months and it appears that the root cause has been identified and a provisional fix has been offered.  Could we please implement, test, and distribute it?

Thank you very much for all of your great efforts :-)!

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