MySQL Bugs: #68790: migrate from sql server to mysql failed for datetime2 type

Bug #68790	migrate from sql server to mysql failed for datetime2 type
Submitted:	27 Mar 2013 8:43	Modified:	31 Jul 2013 14:09
Reporter:	qiu yonggang 邱	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S3 (Non-critical)
Version:	5.2.47 CE	OS:	Windows (no test on other platform)
Assigned to:		CPU Architecture:	Any
Tags:	datetime2 migrate sql server

Description:
when i use the workbench to migrate the sql server 12 database to mysql5.5, when copy the data to table process, it reports error, datetime2 should convert to string type but  not the  msyql datetime type. But the table convert process success convert the datetime2 to mysql datetime type. 

How to repeat:
create a table contains datetime2 type column, then convert this table from sql server 2012 to mysql 5.5 

Suggested fix:
when copy the table data, identify the datetime2 type  and convert it to datetime type, thanks.

Thank you for the bug report. I couldn't repeat with my own SQL Server table and it's converted to datetime. Please provide the sql statement to create the SQL Server table which failed to you. Thanks.

Following is the table create sql, and the error workbench reported is:
`point_card`:Type mismatch fetching field 8 (should be string, was MYSQL_TYPE_DATETIME)

/****** Object:  Table [city].[point_card]    Script Date: 2013/3/30 17:34:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [city].[point_card](
	[id] [int] IDENTITY(48,1) NOT NULL,
	[name] [nchar](64) NULL,
	[description] [nchar](255) NULL,
	[idx] [int] NULL,
	[bucks] [int] NULL,
	[price] [nchar](32) NULL,
	[product_id] [nchar](64) NULL,
	[created_at] [datetime2](0) NULL,
	[onsale] [int] NULL,
	[platform] [smallint] NULL,
 CONSTRAINT [PK_point_card_id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [city].[point_card] ADD  DEFAULT (NULL) FOR [name]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT (NULL) FOR [description]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT ((0)) FOR [idx]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT ((0)) FOR [bucks]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT (NULL) FOR [price]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT (NULL) FOR [product_id]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT (NULL) FOR [created_at]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT ((0)) FOR [onsale]
GO

ALTER TABLE [city].[point_card] ADD  DEFAULT ((0)) FOR [platform]
GO

Thanks for you report.

Same problem here. Any news?

Fixed as of MySQL Workbench 6.0.2, and here's the changelog entry:

While migrating from a Microsoft SQL Server database, the "Datetype2"
MSSQL type is now converted to the "DATETIME" MySQL type.

Thank you for the bug report.