Mysql server does not support 4-byte encoded utf8 characters

Question

I've received a server error running a Data transfer component from Sql Server to MySql db. The error message reads as follows:

[MySql][ODBC 5.1 Driver][mysqld-5.0.67-community-nt-log]Server does not support 4-byte encoded UTF8 characters.

The source Sql Server table contains nvarchar columns, the target MySql table contains varchar columns.

Can anybody shed some light on this problem?

Solution 1

If you need MySQL to support 4-byte UTF-8 characters (which is normally considered part of UTF-8), you need to use the character set utf8mb4, not utf8. utf8mb4 was first supported in MySQL 5.5.3.

Solution 2

I had the same problem which I could reproduce by simply updating a char(1) column for a single row over a linked server on SQL 2008 to a MySQL 5.1 DB:

update linked_server_name...table_name set status = 'c' where id = 1;

This error was occurring on a newly built server. I had a similar setup on another machine, where the same code worked just fine. The only difference was the version of the MySQL ODBC driver: on the new server, it was 5.2.2; on the old (working) server, it's 5.1.8 (now unsupported).

I downloaded and installed the previous version of the ODBC driver (v5.1.11), and the problem went away.

Solution 3

1. Update your MySQL to 5.5.3 and use utf8mb4 for column encoding.

2. Force copy

3. Create a new table in SQLServer with the same structure as the table you need to copy

4. Change the new table's column nvarchar(size) -> varchar(size x 2)

5. Copy the data into the new table

6. Copy the SQLServer's data from the new table to MySQL

Solution 4

From the documentation:

Currently, MySQL support for UTF-8 does not include four-byte sequences. (An older standard for UTF-8 encoding is given by RFC 2279, which describes UTF-8 sequences that take from one to six bytes. RFC 3629 renders RFC 2279 obsolete; for this reason, sequences with five and six bytes are no longer used.)

Solution 5

"4-byte encoded UTF-8 characters" refers to characters with code point > 0xFFFF, i.e., ones whose code points don't fit within 16 bits (are outside the basic multilingual plane (BMP)). Many older systems don't support characters outside the BMP.

Characters outside the BMP are usually CJK characters; I don't know if that's the case with you here. :-)

View more solutions