home | non-tech | cs | ee | about
Abstract New

Migrating from SQL Server to MySQL using the MySQL Workbench - Data conversion pains

The release of MySQL Workbench 5.2 introduced the database migration wizard which enables schema and data migration from a MSSQL Server database onto MySQL. The migration of the schema is fairly straightforward and will have you thrilled to bits. The data migration then comes as a kick to your thingamajig


Some of the columns to watch out for (these conversion issues may be fixed in later versions of SQL Workbench):
  • Datetime2 - MySQL does not have a corresponding data type. The precision involved will cause data migration errors.
    Error: “Type mismatch fetching field should be string, was MYSQL_TYPE_DATETIME”
    Solution: Convert all datetime2 types into datetime
  • Datetime - In MYSQL datetime does not contain milliseconds. The SQL Server datetime datatype contains milliseconds.
    Error: “Invalid timestamp literal detected” error.
    Solution: Convert the datetime type to a smalldatetime in SQL Server if you do not mind losing the milliseconds
  • float(53) - A float(53) in SQL server specifies double precision. MYSQL supports float, real and double precision so you can also alter the generated script during the creation of the database. By default the schema converts a float(53) into MYSQL float and hence during data import it throws the following error
    Error: Type mismatch fetching field (should be float, was MYSQL_TYPE_DOUBLE)
  • Uniqueidentifier - MySQL does not support the guid - The schema should be altered to CHAR(36) if you are using entity framework to ensure proper mapping.

4 comments:

Anonymous said...

Thanks for the tips - converting datetime to smalldatetime resolved a bunch of issues for me!

Anonymous said...

float(53) - fixed in Workbench 6.1

Pavel Gusak said...

How about NVARCHAR() ? I'm getting :Error during charset conversion of wstring: Illegal byte sequence

Barbaros said...

Try to choose FreeDTS as driver in the beginning.

Post a Comment

© 2014 - 2015 abstract new. All rights reserved.