MySQL Bugs: #43098: Migration toolkit should transform SqlServer's Unique identifier into Unique.

Bug #43098	Migration toolkit should transform SqlServer's Unique identifier into Unique.
Submitted:	23 Feb 2009 6:38	Modified:	22 Jul 2012 18:24
Reporter:	srinath gnath	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	1.1.17	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The Migration toolkit should transform a MS SqlServer's Unique Identifier field into a varchar(64),whereas it should be a unique.

This can lead to various problem, when we migrate the whole database from sql server to mysql.

PS: I'm using MySQL 5.0.41 with MySql GUI Tools V5.1.10 for windows.

How to repeat:
Create a table in SqlServer with unique identifier datatype and convert it with Migration Toolkit... you get varchar(64) by default.

Thank you for the problem report. Please, provide original CREATE TABLE statement from SQL Server and complete script generated by Migration Toolkit for it in MySQL.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,

Please find the create table queries for both mysql and ms sql.

// MS-SQL CREATE TABLE QUERY

USE [Test-SQL]
GO
/****** Object:  Table [dbo].[tbl_Sample]    Script Date: 03/24/2009 10:18:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Sample](
	[Sample_Id] [int] NULL,
	[Sample_type_Id] [int] NULL,
	[Enabled] [bit] NULL,
	[Region_ID] [int] NULL,
	[Loc_ID] [int] NULL,
	[EMailID] [varchar](100)
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

// MYSQL CREATE TABLE QUERY

CREATE TABLE `Test-SQL`.`tbl_Sample` (
  `Sample_Id` INT(10) NULL,
  `Sample_type_Id` INT(10) NULL,
  `Enabled` TINYINT NULL,
  `Region_ID` INT(10) NULL,
  `Loc_ID` INT(10) NULL,
  `EMailID` VARCHAR(100) NULL
)

Hi,

Please find the creATE TABLE QUERY. Here ApplicationId field is an unique identifier. Its convert into varchar(64).

------------------- MS-SQL CREATE TABLE QUERY ----------------

GO
/****** Object:  Table [dbo].[aspnet_Users]    Script Date: 03/24/2009 10:27:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aspnet_Users](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL CONSTRAINT [DF__aspnet_Us__UserI__27C3E46E]  DEFAULT (newid()),
	[UserName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LoweredUserName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MobileAlias] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__aspnet_Us__Mobil__28B808A7]  DEFAULT (NULL),
	[IsAnonymous] [bit] NOT NULL CONSTRAINT [DF__aspnet_Us__IsAno__29AC2CE0]  DEFAULT ((0)),
	[LastActivityDate] [datetime] NOT NULL,
	[Active] [bit] NULL CONSTRAINT [DF_aspnet_Users_Active]  DEFAULT ((1)),
	[Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	 CONSTRAINT [PK__aspnet_Users__25DB9BFC] PRIMARY KEY NONCLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Us__Appli__26CFC035] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Users] CHECK CONSTRAINT [FK__aspnet_Us__Appli__26CFC035]SET ANSI_PADDING OFF

------------------ MYSQL CREATE TABLE QUERY ----------------------------------------

DROP TABLE IF EXISTS `Sample_dbo`.`aspnet_Users`;
CREATE TABLE `Sample_dbo`.`aspnet_Users` (
  `ApplicationId` VARCHAR(64) NOT NULL,
  `UserId` VARCHAR(64) NOT NULL DEFAULT newid(),
  `UserName` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `LoweredUserName` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `MobileAlias` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `IsAnonymous` TINYINT NOT NULL DEFAULT (0),
  `LastActivityDate` DATETIME NOT NULL,
  `Active` TINYINT NULL DEFAULT (1),
  `Name` VARCHAR(255) NULL,
  PRIMARY KEY (`UserId`),
  UNIQUE INDEX `aspnet_Users_Index` (`ApplicationId`, `LoweredUserName`),
  INDEX `aspnet_Users_Index2` (`ApplicationId`, `LastActivityDate`),
  CONSTRAINT `FK__aspnet_Us__Appli__26CFC035` FOREIGN KEY `FK__aspnet_Us__Appli__26CFC035` (`ApplicationId`)
    REFERENCES `Sample_dbo`.`aspnet_Applications` (`ApplicationId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = INNODB;

Many thanks for writing a feature request.

Workaround here is to edit the columns manually.

We are on the way to implement full functionality of MySQL Migration Toolkit into MySQL Workbench. We won't implement this addition anymore into Migration Toolkit, but we will look what we are able to do in Workbench here.

The upcoming Workbench Migration Wizard handles this by translating "UNIQUE 
IDENTIFIER" from SQL Server into a VARCHAR(64) UNIQUE field.

A changelog is not required as this is a new Workbench wizard, and is already documented within the "Microsoft SQL Server Type Mapping" table.