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.
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:
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
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?
F3B3B4AF is beyond the end of established UTF-8 characters. Perhaps MSSQL allowed, but MySQL did not.
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 '鶻姦'