MySQL Bugs: #11003: Uncompatible autogenerated index name

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

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 '}'

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.

Exemple MS Access DB with uncompatible index name

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

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

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.