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?