Bug #11003 Uncompatible autogenerated index name
Submitted: 31 May 2005 21:58 Modified: 9 Apr 2006 22:09
Reporter: Thierry Scalais Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S3 (Non-critical)
Version:1.0.25 OS:Microsoft Windows (XP)
Assigned to: CPU Architecture:Any

[31 May 2005 21:58] Thierry Scalais
Description:
When migrating from an MSAccess DB
Some index have a name automatically genrated (i presumed in the Access .mdb) that are not compatible with mysql

The migration tool create table with a command like

CREATE TABLE ...

  INDEX `{AC25F1F5-C47F-11D1-A96F-006097D4A641}` (`FMID`),
...

It generates an error that prevents the table creation.

How to repeat:
Sorry, I don't know how to generate these names.
It could be generated by a wizard or when creating relations (foreign keys)

Suggested fix:
Remove '{' and '}'
[31 May 2005 22:40] MySQL Verification Team
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Sorry I was unable to create an Access DB which creates
indexes as you mentioned. However I suggest you for
to test the new 1.0.7 version available in our download
web page, it has bugs fixes regarding the version 1.0.6.
[9 Apr 2006 21:40] Thierry Scalais
Exemple MS Access DB with uncompatible index name

Attachment: Db2.zip (application/zip, text), 8.38 KiB.

[9 Apr 2006 22:09] Thierry Scalais
I just link a example database that contains the uncompatible index names.

When I try to migrate this database to mysql I get the error "incorrect index name''"

Strangely when i execute the script generated by 'MySQL Migration Toolkit' from 'MySQL Query Browser' there is no error generated and index are created.

Note that foreign keys are not imported.
This is the entire message log from 'MySQL Migration Toolkit'
Initializing JDBC driver ... 
Driver class MySQL JDBC Driver 3.1
Opening connection ... 
Connection jdbc:mysql://127.0.0.1:3306/?user=root&password=xxxx&useServerPrepStmts=false

Execute script header commands.
-- ----------------------------------------------------------------------
-- MySQL GRT Application
-- SQL Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0
Creating schema db2 ...
CREATE DATABASE IF NOT EXISTS `db2`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci
Creating tables ...

Creating table Table1 ...
DROP TABLE IF EXISTS `db2`.`Table1`
Creating table Table1 ...

CREATE TABLE `db2`.`Table1` (
  `table1ID` INT(10) NOT NULL,
  `data` VARCHAR(50) NULL,
  PRIMARY KEY (`table1ID`)
)
ENGINE = INNODB

Creating table Table2 ...
DROP TABLE IF EXISTS `db2`.`Table2`
Creating table Table2 ...

CREATE TABLE `db2`.`Table2` (
  `table2ID` INT(10) NOT NULL,
  `data` VARCHAR(50) NULL,
  `table1ID` INT(10) NULL,
  PRIMARY KEY (`table2ID`),
  INDEX `{35A3E393-59B9-4E70-BD21-93419FEE8A8D}` (`table1ID`)
)
ENGINE = INNODB
An error occured while executing the SQL statement.
Incorrect index name ''

Creating table Table4 ...
DROP TABLE IF EXISTS `db2`.`Table4`
Creating table Table4 ...

CREATE TABLE `db2`.`Table4` (
  `table3ID` INT(10) NOT NULL AUTO_INCREMENT,
  `data1` VARCHAR(50) NULL,
  `Table5_Numéro` INT(10) NULL,
  PRIMARY KEY (`table3ID`),
  UNIQUE INDEX `CléPrimaire` (`table3ID`),
  INDEX `{4F96B4B1-0736-47FE-8DE4-336EE25F0174}` (`Table5_Numéro`),
  INDEX `Table5_Numéro` (`Table5_Numéro`)
)
ENGINE = INNODB
An error occured while executing the SQL statement.
Incorrect index name ''

Creating table Table5 ...
DROP TABLE IF EXISTS `db2`.`Table5`
Creating table Table5 ...

CREATE TABLE `db2`.`Table5` (
  `data2` VARCHAR(50) NULL,
  `Numéro` INT(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Numéro`),
  UNIQUE INDEX `CléPrimaire` (`Numéro`),
  INDEX `NORM_OrderByIndex` (`data2`)
)
ENGINE = INNODB
Creating views ...
Creating procedures ...

Execute script footer commands.
SET FOREIGN_KEY_CHECKS = 1
[14 May 2007 3:27] Thomas Carr
This "bug" should be raised in Severity

Complex Access databases, generates in table "MSysRelationships" in column "szRelationship" I found names with "{" "}" e.g. {136235CA-B548-4344-BEA2-C22E67F42458}.  What caused them, I do not know.  I speculation that happened when the author created "links" in the relationship table.

See the below, the "main" table is "contacts" with a primary key of "cont_id"

You note  when the tables "contacts" and "incid_equip" are "linked" it was given the name "contactsincid_equip".

However when the tables "contacts" and "cont_skill" are "linked" is was given the name "{136235CA-B548-4344-BEA2-C22E67F42458}"

ccolumn	grbit	icolumn	szColumn	szObject	szReferencedColumn	szReferencedObject	szRelationship
1	4352	0	cont_id	incid_equip	cont_id	contacts	contactsincid_equip
1	4352	0	cont_id	incid_material	cont_id	contacts	contactsincid_material
1	4352	0	cont_id	incid_fac	cont_id	contacts	contactsincid_fac
1	4352	0	cont_id	cont_skill	cont_id	contacts	{136235CA-B548-4344-BEA2-C22E67F42458}
1	4352	0	cont_id	cont_fac	cont_id	contacts	{61C865C4-3F9B-45AC-B94F-D2BB16BADED0}
1	4352	0	cont_id	incid_cont	cont_id	contacts	contactsincid_cont
1	4352	0	cont_id	cert_type_cont	cont_id	contacts	contactscert_type_cont
1	4352	0	cont_id	cont_course	cont_id	contacts	{F4491698-48A5-4F1B-9CE4-EF01CD68F146}
1	4352	0	cont_id	cont_equip	cont_id	contacts	{B32C1A15-AE98-4B41-A304-901A7E9363A5}
1	4352	0	cont_id	team_cont	cont_id	contacts	{BE16FF5D-29E3-4E98-AEB4-DCD6DFD25508}
1	4352	0	cont_id	cont_mat	cont_id	contacts	{F3EC4F2A-6D9E-4085-8202-5E8369FC0493}

I came across this when try to adapt a Access multiuser application. At first I was able to migrate with DBTools Manager Professional to MySQL 5.x, but some the relationships had these "{""}" names which are not very descriptive. However, the application seemed to work with the ODBC connection. 

When I compared the relationships generated by MySQL Workbench (with "{""}" names) with the Access report (no names).

After drop the database I tried the migration with MySQL Migration Toolkit, it failed because of the incompatible auto-generated index name and Date escape sequ. errors. After I edited Toolkit's SQL code for each index, removing the "{""}". The database migrated and MySQL Workbench generated a relationships diagram with captions like "B32C1A15-AE98-4B41-A304-901A7E9363A5" which again not very very descriptive. 

There are two solutions, when reengineering the access database;

 1. When "{" and "}" are detected in "szRelationship", remove them, and use the auto-generated index name or

 2. When "{" and "}" are detected in "szRelationship", replace the auto-generated index name with the concatenation of "szReferencedObject", "szReferencedColumn, "szObject" and "szColumn" creating the correct name of the index.

Ideally all the names in "szRelationship" should be rebuilt in that format since in "MSysRelationships", the current format in "szRelationship" is the concatenation of "szReferencedObject" and "szObject".  By using the "szReferencedObject",szReferencedColumn, "szObject" and "szColumn" you would be able to detect duplicate indexes.

An alternative is as a pre-migration step is to changed the names by "hand", if Access will permit it.