SQL Server to Mysql migration (using Mysql Workbench) data transfer error

Question

I am using Mysql Work bench(6.3) to migrate Database from MS Sql server(2008) to Mysql. It is erroring out during the "bulk Data Transfer" with below warnings. This is happening only with column types like (varchar, char). When I tried table with all 'int' columns there is no issue. Here is the log I got when I tried to migrate "Boy" table with columns (Name(char),age(int),Type(varchar))

> `[WRN][      copytable]: 20 characters could not be converted to UTF-8 from column Name during copy
   [WRN][      copytable]: 24 characters could not be converted to UTF-8 from column Type during copy
   [INF][      copytable]: Statement execution failed: Data too long for column 'Name' at row 1:

INSERT INTO `Test`.`Boy` (`Name`, `Age`, `Type`) VALUES ('John       \0 `Test`.`Boy` (`Name',10,'Type is Lien\0\0\0\0�\�7\0\0\0\0\0\0\0\0\0\0\0\0\0\0d\0\0defperformance_schemasession_variablessession_variables\rVariable_name\rVARIABLE_NAME\0@\0\0\0�\0\0\0]\0\0defperformance_schemasession_variablesse')`

Collation in MSsql is Latin1 and UTF8 in MySQL.
Also, exporting table data in to flat file and importing it in Mysql worked (but this is table to table not bulk transfer) ..

that insert query looks totally weird. why is there what appears to be table/field names embedded in little Johnny's first name? — Marc B, Dec 11 '15 at 20:44
That is Work bench generated insert statement. Not sure why it created it that way.. — Vinny, Dec 11 '15 at 20:45

score 2 · Answer 1 · answered Dec 14 '15 at 20:45

2

Changing the Source connection method from 'ODBC Data Source' to 'ODBC Data Source(Free TDS)' worked. As explained in this link http://www.martin-brennan.com/could-not-successfully-convert-ucs-2-string-to-utf-8/

answered Dec 14 '15 at 20:45

Vinny

51
1
6

I've been trying for days to figure out how to get FreeTDS. First the FreeTDS site has all downloads offline, so I looked to sourceforge, where nothing good was available. Then I tried compiling the FreeTDS.dll so that I can use the ODBC driver, but it compiled, but it doesn't work. Do you have the file FreeTDS.dll? – Exit Feb 19 '16 at 03:57
For me workbench had this compiled in already. MySQL Workbench 6.3. ODBC Data Source(FreeTDS) However... the data copy is still failing on 16 tables, though the structures all went and most of the data did. I'm dumping the tables to csv and building a script to export/import ... that always works. If there are no good delimiters to use (because the data contains all of them) you can dump to xls and use an xls library to import. Workbench needs just a little bit of tweaking ;-) Still saves a lot of work... – Neil Davis Sep 08 '16 at 14:04

score 0 · Answer 2 · answered Jun 22 '16 at 15:34

Connection method should be ODBC Data Source(Free TDS) and need to enable 'Driversends Unicode data as UTF-8' in advanced tab.

If you see any Data migration errors like 'Statement execution failed: Incorrect string value: '\xA310 pe...' for column'

Change storage engine INNODB to MYISAM and restart Mysql service

score 0 · Answer 3 · answered Jul 22 '16 at 13:03

0

I found that I had to do all of the above, yet it still wouldn't migrate just one table. I inspected this table and it contained the mu symbol μ

I removed this rogue character and the migration continued successfully.

answered Jul 22 '16 at 13:03

Rog

1

score 0 · Answer 4 · answered Jan 23 '17 at 06:27

0

Just download mysql workbench 6.3.8. It fixed the "Could not successfully convert UCS-2 string to UTF-8" bug when using ODBC(native) for connection method

answered Jan 23 '17 at 06:27

Tony Hsieh

11
1

SQL Server to Mysql migration (using Mysql Workbench) data transfer error

4 Answers4

Linked