MySQL Bugs: #43851: Migration Toolkit gives various issues when migrating from MSSQL Server 2005

Bug #43851	Migration Toolkit gives various issues when migrating from MSSQL Server 2005
Submitted:	25 Mar 2009 4:52	Modified:	16 Oct 2009 11:39
Reporter:	Roel Van de Paar	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.1.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CHECKED

Description:
Attempting to migrate from a MSSQL server 2005 to MySQL 5.0.51 (though produced schema does not load on 5.1.31 either) gives various issues. In this particular case about 16 out of 56 tables did not convert properly.

Source tables:

==================== tbl_060_PeopleDetails ====================

CREATE TABLE [dbo].[tbl_060_PeopleDetails](
[PeopDetID] [int] IDENTITY(1,1) NOT NULL,
[PeopDetBornDate] [datetime] NULL,
[PeopDetActDate] [datetime] NULL,
[PeopDetExpDate] [datetime] NULL,
[PersonID] [int] NOT NULL,
[AuthorID] [int] NOT NULL,
[PeopDetTypID] [int] NOT NULL,
[PeopDetRate] [decimal](18, 2) NULL,
[PeopDetName] [nvarchar](255) NULL,
[PeopDetDesc] [nvarchar](255) NULL,
[Deactivate] [bit] NULL,
CONSTRAINT [aaaaatbl_060_PeopleDetails_PK] PRIMARY KEY NONCLUSTERED
(
[PeopDetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

==================== tbl_620_MaterialsDet ====================

CREATE TABLE [dbo].[tbl_620_MaterialsDet](
[MaterialDetID] [int] IDENTITY(1,1) NOT NULL,
[MaterialID] [int] NOT NULL,
[VendorID] [int] NOT NULL,
[CopyrightStatus] [nvarchar](255) NOT NULL,
[PersonID] [int] NOT NULL,
[PersonAction] [nvarchar](255) NULL,
[Movement] [int] NULL,
[Inventory] [int] NULL,
[DetailDate] [datetime] NULL,
[Note] [ntext] NULL,
CONSTRAINT [aaaaatbl_620_MaterialsDet_PK] PRIMARY KEY NONCLUSTERED
(
[MaterialDetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

==================== tbl_200_Courses ====================

CREATE TABLE [dbo].[tbl_200_Courses](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CourseCode] [nvarchar](12) NOT NULL,
[CourseDescription] [nvarchar](50) NOT NULL,
[CourseDescriptionNew] [nvarchar](50) NULL,
[CoursePrereqs] [bit] NOT NULL,
[CourseCredits] [float] NOT NULL,
[CourseTuition] [money] NOT NULL,
[CourseFee] [money] NOT NULL,
[ActivateOn] [datetime] NULL,
[DeactivateOn] [datetime] NULL,
[CourseActive] [bit] NOT NULL,
[CourseNotes] [ntext] NULL,
[CourseNotes2] [ntext] NULL,
[ReleaseID] [int] NOT NULL,
[DeanOwner] [int] NULL,
[ProgramOwner] [nvarchar](50) NULL,
[CourseArea] [nvarchar](255) NOT NULL,
[CourseLevel] [nvarchar](255) NOT NULL,
[HoursPerWeek] [int] NULL,
[CourseSequence] [int] NULL,
[DoNotRun] [bit] NULL,
CONSTRAINT [aaaaatbl_200_Courses_PK] PRIMARY KEY NONCLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

==================== tbl_201_CourseNotes ====================

CREATE TABLE [dbo].[tbl_201_CourseNotes](
[CourseNotesID] [int] IDENTITY(1,1) NOT NULL,
[CourseNotesDate] [datetime] NOT NULL,
[PersonID] [int] NOT NULL,
[CourseID] [int] NOT NULL,
[ReleaseID] [int] NOT NULL,
[CourseNotes] [ntext] NULL,
[HideNote] [bit] NOT NULL,
[NoteType] [nvarchar](255) NOT NULL,
CONSTRAINT [aaaaatbl_201_CourseNotes_PK] PRIMARY KEY NONCLUSTERED
(
[CourseNotesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How to repeat:
Some examples of the resulting errors after conversion of the above listed source tables (notice error 2 and 4 are similar):

==================== tbl_060_PeopleDetails ====================
An error occured while executing the SQL statement.
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '(0),
PRIMARY KEY (`PeopDetID`),
INDEX `AuthorID` (`AuthorID`),
INDEX `Peop' at line 12

==================== tbl_620_MaterialsDet ====================
An error occured while executing the SQL statement.
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near
'CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1)),
`Note` LONGTEXT NULL' at line 10

==================== tbl_200_Courses ====================
An error occured while executing the SQL statement.
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '(0),
`CourseCredits` FLOAT(53) NOT NULL DEFAULT 3,
`CourseTuition` DECIMAL(1' at line 6

==================== tbl_201_CourseNotes ====================
An error occured while executing the SQL statement.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1)),
`PersonID` INT(10) N' at line 3

This last error, when run manually: 

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1)),
`PersonID` INT(10) N' at line 3
(0 ms taken)

Suggested fix:
Fix table conversions.

2 additional notes in regards errors 2 and 4:

o http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html

'The DEFAULT clause specifies a default value for a column. With one
exception, the default value must be a constant; it cannot be a function
or an expression. This means, for example, that you cannot set the
default for a date column to be the value of a function.'

o Notice that for error 2 and 4 the issue is that the field in question has a millisecond format: 2006-03-02 00:00:00.000

This person is having a similar issue:
http://forums.mysql.com/read.php?60,162599,162599

This comment addresses only one table tbl_201_CourseNotes.

The problem can be repeated by setting the constraint to the DateTime column in MSSQL table tbl_201_CourseNotes as follows:

ALTER TABLE [dbo].[tbl_201_CourseNotes] ADD DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))) FOR [CourseNotesDate]

The default value is first generated as datetime, then converted to varchar and then converted back to datetime.

It results in generating the DEFAULT value that makes no sense to MySQL server:

`CourseNotesDate` DATETIME NOT NULL DEFAULT CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1)),

Obviously, there is no need to perform conversions like datetime->varchar->datetime, so the problem can be easily fixed by dropping the old constraint and adding a new one as follows:

ALTER TABLE [dbo].[tbl_201_CourseNotes] ADD DEFAULT getdate() FOR [CourseNotesDate]

MigrationToolkit generates the resulting column as:

`CourseNotesDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

So, the problem is resolved for this particular case.
However, DateTime columns in MSSQL can contain more complex logic for default values, so they are not easily simplified such as in the current case. Thus, if the complex expression goes beyond the reach of DEFAULT CURRENT_TIMESTAMP it must be disregarded with the warning informing user that the default value was not convertible to MySQL syntax.

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 in foreseeable future.

Timestamp, Date, Datetime, Time is always a problem on migrations. Every RDBMS implemented their own logic here and you can't catch all of this.

On migration it always needs manual work to transfer Timestamp, Time, Date, Datetime values.

More informations about MySQL Workbench you will find here:

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