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:
Thanks for the tips - converting datetime to smalldatetime resolved a bunch of issues for me!
float(53) - fixed in Workbench 6.1
How about NVARCHAR() ? I'm getting :Error during charset conversion of wstring: Illegal byte sequence
Try to choose FreeDTS as driver in the beginning.
Post a Comment