Sql-server – UTF8 Trouble while migrating from MSSQL to MySQL with MySQL Workbench

character-setmigrationMySQLmysql-workbenchsql server

I am tasked with migrating part of our database from an on-site MSSQL server to an Amazon RDS MySQL. This will be my first attempt with a database migration, so I am doing a dry run from our MSSQL server to a locally installed instance of MySQL using MySQL Workbench. Local install MySQL Workbench version 6.3. MySQL Server version 5.7

Error generated: Inserting Data: Incorrect string value: '\xA0\xA0 I-D...' for column 'Address1' at row 77 while performing bulk data transfer.
- This happens twice in the same table (two different rows). All other tables are successful.

Symptoms:

On the Object Migration -> Manual Editing -> Column Mappings screen, I see a list of migration messages/warnings.
- On columns that go from source VARCHAR/NVARCHAR to VARCHAR, I get "Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci. Same for Char to Char.
- On columns that go from source BIT to TINYINT(1), I get "Source column type BIT was migrated to TINYINT(1)

Settings:

Source Selection is MSSQL Server, ODBC (Free TDS)
- Advanced tab has "Driver sends Unicode data as UTF-8" checked. Required for Free TDS
Target Selection is local MySQL. Connection method is Standard(TCP/IP)

I have read THIS article which seems quite promising, but I am not sure how to implement step 3 as it is a migration and when I get to the "Create Schemas" step, it says it will drop the schema and recreate anew if it already exists.

All the indexes on this database are on small columns, so the 3-bit to 4-bit utf8 will not be an issue for indexes.
As far as "Modify connection, client, and server character sets" to be utf8mb4, I cannot even find the file to modify. I went to MySQL Server 5.7/my-default.ini and changed it to include
```
[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
```
as requested, but my variables still look like the below picture, so that is obviously not the right file. Plus, will I actually have access to this on the Amazon RDS instance?

Any thoughts?

Best Answer

Hex A0 probably came from a Word document, where it is a "hard space".

It appears that you did not specify that the source was 'latin1' and the destination is 'utf8'. Otherwise, A0 should have turned into C2A0. That seems correct. However, this seems incorrect: "Driver sends Unicode data as UTF-8". Or perhaps just inconsistent with "Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci". Did you do anything in ODBC to tell it to convert latin1 to utf8?

You have an inconsistency -- some places you mention utf8, some mention utf8mb4. Outside MySQL, there is only "utf8" (or "UTF-8"), but inside MySQL there is a difference between utf8 and utf8mb4. If you intend to handle Chinese or Emoji, go with utf8mb4.

Related Solutions

Sql-server – SQL Server to MySQL migration – how can I remove UCS-2 surrogate pairs in SQL Server

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;

Mysql – How large space will be occupied by thesql for a varchar utf8 column

CHAR(...) CHARACTER SET utf8 always takes 3 bytes per character -- CHAR(100) occupies 300 bytes (no length needed).

VARCHAR occupies 1-2 bytes for a length, plus only as many bytes as needed. So VARCHAR(100) with hello will occupy 7 (2+5) bytes in any character set.

Señor, in CHARACTER SET latin1, take 5 bytes (plus length). In utf8, it takes 6 bytes (plus length). This is because ñ is the 1-byte hex F1 in latin1 or the 2-byte C3B1 for utf8.

Some Chinese characters and some Emoji, need 4 bytes, so utf8mb4 is a better choice for them.

There is very little use for ever using CHAR instead of VARCHAR. They are usually for consistent-length fields such as postal code, country code, md5, uuid, etc. And all of those may as well be CHARACTER SET ascii or possibly latin1, but not utf8.

Back to your question... With CHARACTER SET utf8 (or utf8mb4):

English letters take 1 byte.
Western European accented letters take 2 bytes.
Greek, Hebrew, and other languages in that general area, take 2 bytes.
Japanese and Korean take 3 bytes.
Most Chinese characters are 3 bytes; some are 4 and need utf8mb4.
A longer list

"mysql will physically store characters using 1 byte for latin and 3 bytes for CJK" is not phrased correctly. Change 'latin' to 'English' to make it correct. ('Latin' tends to include various accented characters.)

Best Answer

Related Solutions

Sql-server – SQL Server to MySQL migration – how can I remove UCS-2 surrogate pairs in SQL Server

Mysql – How large space will be occupied by thesql for a varchar utf8 column

Related Question