Bug #68285 Getting Errors while Migrating data from MS SQL to MY SQL
Submitted: 6 Feb 2013 5:20 Modified: 25 Mar 2013 18:22
Reporter: Hakoo Desai Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S1 (Critical)
Version:MySQL 5.5 OS:Windows
Assigned to: CPU Architecture:Any
Tags: database-migration, MySQL, sql-server

[6 Feb 2013 5:20] Hakoo Desai
Description:
I am trying to migrate my database from MS SQL 2000 to MySQL using Migration tool in MySQL. When I am doing so, I am getting following warnings:

1) Collateral cheanges SQL_Latin1..... to  UTF8_General_CI. I am worried about this as my tables contains encrypted data, does this collateral changes affect that?

2)BIT field changes to TINYINT(1). MySQL has BIT field, than why it is changing to TINYINT?

All these were warnings, apart from this, many tables and data are not created. They are missing.
So, I picked one table among them, say tblProducts.

When I am trying to migrate this table, I am getting following error :

    Migrating data... wbcopytables.exe --odbc-source=DSN=SwiplODBC;DATABASE=;UID= --target=root@127.0.0.1:3306 --progress --passwords-from-stdin --thread-count=1 --table-file=c:\users\xxxx\appdata\local\temp\tmp5ffenq \users\xxxx\appdata\local\temp\tmp5ffenq `SWIPL11009`.`tblProducts`:

Copying 83 columns of 102854 rows from table [SWIPL11009].[dbo].[tblProducts] ERROR: `SWIPL11009`.`tblProducts`:Type mismatch fetching field 40 (should be float, was MYSQL_TYPE_DOUBLE) `SWIPL11009`.`tblProducts`:Finished copying 0 rows in 0m01s  
 08:43 [INF][      copytable]: Opening ODBC connection to DSN=SwiplODBC;DATABASE=;UID=;PWD=XXX' 
 08:43 [INF][      copytable]: ODBC connection to 'DSN=SwiplODBC;DATABASE=;UID=;PWD=' opened 
 08:43 [INF][      copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root 
 08:43 [INF][      copytable]: Connection to MySQL opened 
 ***08:43 [WRN][      copytable]: Invalid timestamp literal detected: ''*** 

Actually I don't have any timestamp field, all are DateTime, and set null By default.
Looking forward for some suggestion and solutions.

Thanks.

How to repeat:
I am trying to migrate my database from MS SQL 2000 to MySQL using Migration tool in MySQL. When I am doing so, I am getting following warnings:

1) Collateral cheanges SQL_Latin1..... to  UTF8_General_CI. I am worried about this as my tables contains encrypted data, does this collateral changes affect that?

2)BIT field changes to TINYINT(1). MySQL has BIT field, than why it is changing to TINYINT?

All these were warnings, apart from this, many tables and data are not created. They are missing.
So, I picked one table among them, say tblProducts.

When I am trying to migrate this table, I am getting following error :

    Migrating data... wbcopytables.exe --odbc-source=DSN=SwiplODBC;DATABASE=;UID= --target=root@127.0.0.1:3306 --progress --passwords-from-stdin --thread-count=1 --table-file=c:\users\xxxx\appdata\local\temp\tmp5ffenq \users\xxxx\appdata\local\temp\tmp5ffenq `SWIPL11009`.`tblProducts`:

Copying 83 columns of 102854 rows from table [SWIPL11009].[dbo].[tblProducts] ERROR: `SWIPL11009`.`tblProducts`:Type mismatch fetching field 40 (should be float, was MYSQL_TYPE_DOUBLE) `SWIPL11009`.`tblProducts`:Finished copying 0 rows in 0m01s  
 08:43 [INF][      copytable]: Opening ODBC connection to DSN=SwiplODBC;DATABASE=;UID=;PWD=XXX' 
 08:43 [INF][      copytable]: ODBC connection to 'DSN=SwiplODBC;DATABASE=;UID=;PWD=' opened 
 08:43 [INF][      copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root 
 08:43 [INF][      copytable]: Connection to MySQL opened 
 ***08:43 [WRN][      copytable]: Invalid timestamp literal detected: ''*** 

Actually I don't have any timestamp field, all are DateTime, and set null By default.
Looking forward for some suggestion and solutions.

Thanks.

Suggested fix:
Other Migration tools  can convert and import data into mysql.
[25 Mar 2013 18:21] Sergio Andres De La Cruz Rodriguez
1. The changes in the encoding should not affect the encoded strings. Usually your application logic is the one that handles the decoding of the data. There's an issue, however, when putting international chars in non national fields using some encoding different than UTF-8 (see bug 66516). The fix for that should be out in the next Workbench release.

2. BIT vs TINYINT(1). There's no agreement on what should the preferred target type be (see bug 60701 for an explanation).

3. "should be float, was MYSQL_TYPE_DOUBLE": already reported (bug 67234)

This bug is marked as duplicate of bug 67234.
[25 Mar 2013 18:25] Sergio Andres De La Cruz Rodriguez
Thank you very much for your bug report
[7 Jun 2013 3:55] Kevin Scott
The original bug description describes an issue with migration of DATETIME fields 
Specifically:

***08:43 [WRN][      copytable]: Invalid timestamp literal detected: ''*** 
Actually I don't have any timestamp field, all are DateTime, and set null By default.

I am having this issue with v5.2.47.  This bug is marked as a duplicate but the referenced issue does not address this DATETIME problem.
[7 Jun 2013 12:49] Sergio Andres De La Cruz Rodriguez
Hakoo, Kevin:

Can you provide a sample SQL Server CREATE TABLE and INSERT INTO statements that can be used to reproduce this DATETIME issue?