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?

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 '鶻姦'