Mysql server does not support 4-byte encoded utf8

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?

Answer 1

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.

Answer 2

"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. :-)

Answer 3

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.

Answer 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.)

Answer 5

I was getting this error message with 1 client machine making an ODBC connection to pull data into an excel spreadsheet. Several other machines could update this same spreadsheet with no trouble.

After a bit of searching, I found nothing, so started experimenting. The ODBC connection was setup with the MySQL unicode driver. I recreated the connection using the ANSI driver and it works fine.

Hope this helps someone else.

Answer 6

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