How to decode a string that has been UTF-8 encoded twice to simple UTF-8?

Question

I have a huge MySQL table which has its rows encoded in UTF-8 twice. For example "Újratárgyalja" is stored as "Újratárgyalja".

The MySQL .Net connector downloads them this way. I tried lots of combinations with System.Text.Encoding.Convert() but none of them worked.

Sending set names 'utf8' (or other charset) won't solve it.

How can I decode them from double UTF-8 to UTF-8?

Answer 1

You could try using

SELECT CONVERT(`your_column` USING ascii)
FROM `your_table`

at the MySQL query level. This is a stab in the dark, though.

Answer 2

Peculiar problem, but I think I can reproduce it by a suitably-unholy mix of UTF-8 and Latin-1 (not by just two uses of UTF-8 without an interspersed mis-step in Latin-1 though). Here's the whole weird round trip, "there and back again" (Python 2.* or IronPython should both be able to reproduce this):

# -*- coding: utf-8 -*-
uni = u'Újratárgyalja'
enc1 = uni.encode('utf-8')
enc2 = enc1.decode('latin-1').encode('utf-8')
dec3 = enc2.decode('utf-8')
dec4 = dec3.encode('latin-1').decode('utf-8')

for x in (uni, enc1, enc2, dec3, dec4):
  print repr(x), x

This is the interesting output...:

u'\xdajrat\xe1rgyalja' Újratárgyalja
'\xc3\x9ajrat\xc3\xa1rgyalja' Újratárgyalja
'\xc3\x83\xc2\x9ajrat\xc3\x83\xc2\xa1rgyalja' Újratárgyalja
u'\xc3\x9ajrat\xc3\xa1rgyalja' Újratárgyalja
u'\xdajrat\xe1rgyalja' Újratárgyalja

The weird string starting with Ú appears as enc2, i.e. two utf-8 encodings WITH an interspersed latin-1 decoding thrown into the mix. And as you can see it can be undone by the exactly-converse sequence of operations: decode as utf-8, re-encode as latin-1, re-decode as utf-8 again -- and the original string is back (yay!).

I believe that the normal round-trip properties of both Latin-1 (aka ISO-8859-1) and UTF-8 should guarantee that this sequence will work (sorry, no C# around to try in that language right now, but I would expect that the encoding/decoding sequences should not depend on the specific programming language in use).

Answer 3

When you write "The MySQL .Net connector downloads them this way." there's a good chance this means the MySQL .Net connector believes it is speaking Latin-1 to MySQL, while MySQL believes the conversation is in UTF-8. There's also a chance the column is declared as Latin-1, but actually contains UTF-8 data.

If it's the latter (column labelled Latin-1 but data is actually UTF-8) you will get mysterious collation problems and other bugs if you make use of MySQL's text processing functions, ORDER BY on the column, or other situations where the text "means something" rather than just being bytes sent over the wire.

In either case you should try to fix the underlying problem, not least because it is going to be a complete headache for whoever has to maintain the system otherwise.

Answer 4

Too bad no-one has answered the problem? Yes, we all agree that it is the "wrong" way to solve it, but sometimes you need to do a temporary workaround... and right now, I would have needed that snippet of code. :-)

Will publish it if I manage to solve it.