4

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?

hippietrail
  • 15,848
  • 18
  • 99
  • 158
YB.
  • 41
  • 1
  • 1
  • 3

8 Answers8

7

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.

prosfilaes
  • 1,268
  • 13
  • 17
  • Comment by anonymous user proposed as edit: Char set 'utf8mb4' does not seem to be supported in MySQL v5.1. – StuartLC Jul 29 '12 at 06:36
  • +1 Thank You for this GEM of a find. This was one of those finds that you know is out there but it takes you a few days to find. KUDOS. The driver crashed out on this text, "eccentricity [ˌɛksɛnˈtrɪsɪtɪ] n pl -ties" which was part of a dictionary description for the word eccentricity. It appeared to crash right at the dot after the right bracket or the first odd character after the dot. – Rich Bianco Sep 16 '14 at 20:14
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.

Trevor Pilley
  • 16,156
  • 5
  • 44
  • 60
dbscissors
  • 21
  • 1
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. :-)

C. K. Young
  • 219,335
  • 46
  • 382
  • 435
1

  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

andrewsi
  • 10,807
  • 132
  • 35
  • 51
shootsoft
  • 21
  • 1
1

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

wallyk
  • 56,922
  • 16
  • 83
  • 148
0

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
mrk
  • 4,999
  • 3
  • 27
  • 42
Stefan Brendle
  • 1,545
  • 6
  • 20
  • 39
  • Solved it by setting the charset "utf8" directly in the MySQL ODBC driver settings on the "advanced" tab on the bottom. This helps, but the string breaks, if I'm using german special chars like "ö" or "ü". I couldn't find a solution for this problem, so I wrote a simple MS-SQL-Server-function, which converts this chars. – Stefan Brendle Jan 21 '13 at 10:04
0

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

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

Shannon Looper
  • 1
0

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.

DaveNOk
  • 1