MySQL Bugs: #41675: Missing index name collision detection at Object Mapping step

Bug #41675	Missing index name collision detection at Object Mapping step
Submitted:	22 Dec 2008 15:04	Modified:	19 Oct 2009 13:51
Reporter:	stonebrad unlojitop	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.1.15	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	CHECKED, indexes naming collision duplicate

Description:
When migrating from SQL Server (my case) to MySQL index names get truncated to 31 characters so there is a great chance to have duplicate index names at MySQL.
This is not detected when object mapping checks are done and if you're not aware of this issue you wont notice it until you finish the whole migration.
Tables with this issue are not generated.

The final log shows a message like:
Duplicate key name 'myreallyhugeindexnamethatwillra', but that's a little too late.

How to repeat:
Create 2 indexes in a specific table at source db with the names:
myreallyhugeindexnamethatwillraisemigrationproblems01
myreallyhugeindexnamethatwillraisemigrationproblems02

Both indexes will be renamed to 'myreallyhugeindexnamethatwillra' (31 chars).

Suggested fix:
Extend object mapping checks to include this verification and show appropriate warnings.
Maybe enable a convenient button like 'auto solve' and rename indexes in target database like 'index1','index2','index3', etc to avoid this issue.

Thank you for a bug report. It is very easy to repeat even migrating the following MySQL table:

mysql> create table tli(c1 int, c2 int,
    -> key myreallyhugeindexnamethatwillraisemigrationproblems01(c1),
    -> key myreallyhugeindexnamethatwillraisemigrationproblems02(c2));
Query OK, 0 rows affected (0.20 sec)

mysql> show create table tli\G
*************************** 1. row ***************************
       Table: tli
Create Table: CREATE TABLE `tli` (
  `c1` int(11) default NULL,
  `c2` int(11) default NULL,
  KEY `myreallyhugeindexnamethatwillraisemigrationproblems01` (`c1`),
  KEY `myreallyhugeindexnamethatwillraisemigrationproblems02` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

to another instance of MySQL. The following script is generated:

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

SET FOREIGN_KEY_CHECKS = 0;

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

DROP TABLE IF EXISTS `test`.`tli`;
CREATE TABLE `test`.`tli` (
  `c1` INT(11) NULL,
  `c2` INT(11) NULL,
  INDEX `myreallyhugeindexnamethatwillra` (`c1`),
  INDEX `myreallyhugeindexnamethatwillra` (`c2`)
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Migration Tool into MySQL Workbench. We won't fix this anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/