Problems during migration from SQL Server to MySQL via MySQL Workbench

Question

We've found most of the options we needed to include the necessary CHARSET. Here is what we've included in the MySQL my.cnf:

[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4 
[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci

But we still get an error which points to a wrong charset and/or collation:

10:17:38 [INF][ copytable]: Statement execution failed: Incorrect string value: '\xF3\xB3\xB4\xAF)<...' for column 'LangText' at row 57

Complete Log file: https://ufile.io/52s13

Can anybody point to a solution for his issue? Thanks!

---

---

---

We need to migrate a SQL Server database to MySQL for the first time (for a customer project). To do so, we've quickly set up a local testing machine to try one of the many tutorials to migrate via MySQL Workbench.

Installed/ prerequisites:

• Local Windows 10 (64-bit)
• MySQL Workbench 6.3 (Community Server)
• MySQL Server 8.0
• Microsoft SQL Server Management Studio (SSMS) 17.0
• Microsoft SQL Server 2017 (Developer Version)
• Driver: SQL Server Native Client 11.0

Unfortunately, we run into some problems during migration and re-importing the DB on another machine. To quickly move forward, we edited the resulting SQL dump with the following queries:

Regarding Export:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' mssql_to_mysql_20180730.local.sql
sed -i 's/ENGINE=InnoDB DEFAULT/ENGINE=InnoDB ROW_FORMAT=DYNAMIC/g' mssql_to_mysql_20180730.local.sql
sed -i 's/DEFAULT CHARSET=utf8mb4/ROW_FORMAT=DYNAMIC CHARSET=utf8mb4/g' mssql_to_mysql_20180730.local.sql

Performance:

sed -i '1s/^/SET foreign_key_checks=0;\nSET unique_checks=0;\nSET autocommit=0;\n/' mssql_to_mysql_20180730.local.sql
sed -i -e "\$aCOMMIT;\nSET unique_checks=1;\nSET foreign_key_checks=1;\n" mssql_to_mysql_20180730.local.sql

Additionally, we've adjusted the target db:

SET GLOBAL innodb_default_row_format=DYNAMIC;

How can we set these options during the migration in workbench? Is there an overview on which options can be altered during the migration configuration?

• eroomydna: You're more likely to attract some help if you post the errors you're getting.
• Miłosz Bodzek: That short message is not very helpful to find out what's goes wrong. Please try run once again with 'Enable debug output for table copy' checked and paste link to full log file.
• kai: Thanks! I've added the log file here: Log. In the file, I removed some rows of inserted content in line 144 of the log because they contained some personal data. I hope that's still useful.
• kai: Closed for more specific question: dba.stackexchange.com/questions/213979/…
• Federico Razzoli: The link is broken.

Answer 1

I think F3B3B4AF is beyond the end of established UTF-8 characters. Perhaps MSSQL allowed, but MySQL did not.

Please provide SHOW CREATE TABLE for the target table. It had better be using utf8mb4 for the target column.

Do any of these look correct?

 gb2312, gbk 6 2 '蟪疮'
 euckr 6 2 '車눕'
 big5 6 2 '馧敞'
 eucjpms, ujis 6 2 '鶻姦'

• Mike Lischke: That looks more like a comment/question for clarification than an answer.