Mysql server does not support 4-byte encoded utf8

问题:

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?

回答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.

回答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.

回答3:

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

回答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.)

回答5:

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

回答6:

Got the same problem. Is it possible to convert the chars before insert into mysql database?

I've got a MySQL database on a remote web server, nothing special. Now I'm using a ODBC Driver to connect the remote MySQL Database with my local MS SQL Server 2008. This works great, but when I'm trying to insert string values from MSSQL, the error occurs. With numbers (int) it works great.

Syntax looks like

INSERT INTO OPENQUERY(
   MYSQL, 
   'SELECT Adresse, Mandant, Matchcode FROM   usr_p171552_2.Adressen' 
) SELECT Adresse, Mandant, Matchcode FROM Adressen

Okay.

I'm look for something like

INSERT INTO OPENQUERY(
    MYSQL, 
    'SELECT Adresse, Mandant, Matchcode FROM usr_p171552_2.KHKAdressen' 
) SELECT Adresse, Mandant, Matchcode FROM KHKAdressen CONVERTTOCHARSET compatiblecharset

回答7:

It looks like a known bug. Here is the issue on MySQL bugs.

http://bugs.mysql.com/bug.php?id=67428

回答8:

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.