3

I have been tasked with migrating a SQL Server 2005 database to MySQL 5.6 (these are both database servers runnig locally) and would really appreciate some help.

-------Edit - Answered-------

This issue has now been resolved. I used Remus Rusanu's suggestion for finding the rows with these surrogate pair characters using CHARINDEX and have decided to use SUBSTRING to exclude the troublesome characters like so:

UPDATE test
SET a = SUBSTRING(a,  1,   (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 - 1) -- string before the unwanted character
+ SUBSTRING(a, (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 +1, LEN(a) ) -- string after the unwanted character
WHERE CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000))) % 2 = 1 -- only odd numbered charindexes (to signify match at beginning of byte pair character)

-----------------------------

  • SQL Server source database has latin1 collation (so has ISO 8859-1 character set right?) but doesn't have any char/varchar fields (any string field is nvarchar/nchar) so all this data should be using the UCS-2 character set.

  • MySQL target database wants the character set UTF-8.

I decided to use the database migration toolkit in the latest version of the MySQL workbench. at first it worked fine and migrated everything as expected. But I have been totally tripped up upon encountering UCS-2 surrogate pair characters in the SQL Server database.

The migration toolkit copytable program did not provide a very useful error message:

Error during charset conversion of wstring: No error.

It also did not provide any field/row information on the problem-causing data and would fail within chunks of 100 rows. So after searching through the 100 rows after the last successful insert I found that the issue seemed to be caused by two UCS-2 characters in one of the nvarchar fields. They are listed as surrogate pairs in the UCS-2 character set. They were specifically the characters DBC0 and DC83 (I got this by looking at the binary data for the field and comparing byte pairs (little endian) with data that was being migrated successfully).

When this surrogate pair was removed from the SQL Server database the row was migrated successfully to MySQL.

Here is the problem:

I have tried to search for these characters in a test SQL Server table (this chartest table is just various test strings an nvarchar field) to prepare a replacement script and keep getting strange results... I must be doing something incorrectly.

Searching for

SELECT * FROM chartest WHERE text LIKE NCHAR(0xdc83)

Will return any surrogate pair character (whether or not it uses DC83), but obviously, only if it is the only character (or part of the pair) in that field. This isn't a big deal since I would like to remove any instance of these anyway (I dont like to remove data like this but I think we can afford it).

Searching for

SELECT * FROM chartest WHERE text LIKE '%' + NCHAR(0xdc83) + '%'

Will return every row! Regardless of whether it even has a unicode character present in the field let alone the DC83 character. Is there a better way to find and replace these characters? Or something else I should try?

I have also tried setting the target databse, table, and field character set to UCS-2 but it seems as though it does not make a difference.

I should also mention that this migration is using live data (~50GB database!) while one of the sites that feeds it is taken offline so any solutions to this need to have a quick running time...

I would appreciate any suggestions very much! Please let me know if there is any information I have left out.

3
  • Hi, I also got this error when migrating sqlserver to mysql. Could you please tell how to find the problemtic characters? I mean the details of "I got this by looking at the binary data for the field and comparing byte pairs (little endian) with data that was being migrated successfully"
    – Sean
    Oct 18, 2013 at 0:56
  • @Sean From what I remember, I got away with only having to surrogate pair characters in the database. So I only had to search for two patterns (one of them being 0xDC83). Little endian meant that I had to search for the bytes in reverse order when looking directly at the binary (0x83DC). I also had to make sure that the index of the occurrence of the pattern in the binary data was odd (because the SQL Server Unicode charset USC-2 has every character as 2 bytes long), as I didn't want to get any false positives (matching half of one char and half of the other).
    – JonM
    Oct 21, 2013 at 12:59
  • If you do have surrogate pair characters preventing you from migrating to MySQL then you may need to search for a whole range of characters... (U+D800 to U+DFFF). As for finding which character was causing this... I think I just eyeballed a set of rows around where the migration was failing.
    – JonM
    Oct 21, 2013 at 13:02

2 Answers 2

4

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;
6
  • Thanks for your response. The source database data is UCS-2 (SQL Server nvarchar) and the target database is UTF8 (MySQL (it doesn't need to be UTF-8 but I'm encountering this problem using UTF8, UTF8mb4, ucs2, and utf16). I am getting the unicode code points DBC0 DC83 in the source database SQL Server. The migration toolkit is having trouble converting these characters presumably.
    – JonM
    Mar 23, 2013 at 8:43
  • Hello, thanks for the update. I like this idea. Your query will definitely return records that have the DC83 character in the row but since it is working with flat binary it wouldn't know when a character starts and ends right? For example: insert into test(a) values (NCHAR(0x00DB)+NCHAR(0x0053)); -- inserts ÛS This row (which doesn't contain any part of a surrogate pair) will be returned when searching for the surrogate character DB00.
    – JonM
    Mar 25, 2013 at 9:22
  • 1
    Yes, smart find. Make sure that the CHARINDEX is odd :) Mar 25, 2013 at 9:30
  • Of course! Since all the characters in UCS-2 are 2 bytes long then the beginning byte will always be at an odd CHARINDEX! Thanks a bunch! I'm going to test this with the data and get back to you.
    – JonM
    Mar 25, 2013 at 10:12
  • I used your solution. Thanks for the help.
    – JonM
    Mar 25, 2013 at 12:17
1

I am migrating data from MSSQL to MySQL, and the content being migrated is html-content from Sitecore CMS (target CMS is Drupal, btw).

I've found, that I get this error when converting the database and hitting records, that contain Instagram-embeds. Instagram-embeds work in the way, that the embedded post data is copied to the embed code (instead of being loaded async., et.c. - even the image is included as base64-css), and the young people nowadays tend to put a lot of emoji's in their image-descriptions (using their iPhones with Emoji keyboard). Emoji's are represented by 4-byte encoded characters, but MySQL utf8 only allows for 3-byte encoded unicode characters.

My initial error from running wbcopytables.exe (which is the non-GUI way of doing Migration Wizard in MySQL Workbench) was the

Error during charset conversion of wstring: No error

but upgrading MySQL Workbench to recent version (from 5.something to 6.x) makes the error a bit more descriptive, hinting table and column (alas, not row):

ERROR: Could not successfully convert UCS-2 string to UTF-8 in table [MyDatabase].[dbo].[MyTable] (column MyColumn). Original string: ...

Anyway - a solution *could* be to use utf8mb4 which would allow for the emoji's. Read more here.

But it looks like, it's a bad idea to do this in e.g. my case with Drupal.

So - the solution I ended up with was simply to strip these characters in my migrate-script. There is no point in keeping these for users of the site in question, since they are being displayed as rectangles on the webpage anyway. Since you can't search-and-replace with regex in SQL Server, I processed the data using a DAL and c# .NET, and I found the help here (thanks a ton, Jon Skeet) - turns out there is a regex-pattern for matching one half of a surrogate pair in UTF-16. See below (and use the pattern in another language if needed).

var noUcs2SurrogatePairsString = Regex.Replace(stringWithUcs2SurrogatePairs, @"\p{Cs}", string.Empty);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.