MySQL provides the CONVERT(`tbl` AS utf8) expression to transcode text from one charset to another.

Sometimes that conversion cannot succeed, because the destination charset does not include characters in the source charset, or because the source byte values are not permitted by the rules of the transcoding.

How can I detect such failures within a MySQL query?

Here is a query with two kinds of transcoding failure. What expression would detect the failures?

    convert(_utf8mb4 'む' USING latin1) as 'no such character',   
    convert(X'97' USING utf8mb4) as 'byte value breaks rules';
no such character byte value breaks rules
? ?

The first column attempts to transcode a Japanese character to the latin1 charset, which cannot represent the character. The second column attempts to transcode a byte value to a character using utf8mb4 encoding rules, but this byte value by itself is not valid utf-8. In both cases the transcode fails.

What SQL expression would detect these failures?

Background: I want to detect such failures, as part of making queries to repair double encoding more robust. See my higher-level question, How can I detect double-encoded MySQL columns and rows, and validate the repair? . But an expression which detects transcoding failures is useful in its own right, and so deserves its own answer.

I believe I have correctly diagnosed my problem — UTF-8 code units in a TEXT and VARCHAR columns mis-interpreted as Latin1, and double-encoded into UTF-8. How did it happen? From the opening sentence of my other question:

My database provider, bless their hearts, migrated our MySQL databases to another server recently, and introduced double-encoding of UTF-8 data via Latin1 into our text data.

I am not asking for diagnosis or prevention. I'm asking for a failure detection expression.


1 Answer 1

  • Latin1 can handle less than 256 distinct characters.
  • む is not one of the latin1 characters. The "?" indicates such.
  • む, when encode in UTF-8 is 3 bytes long, hex E38280
  • Since む seems to already be in MySQL's utf8 or utf8mb4, there is no need to do the conversion. That is, the client's encoding is UTF-8.
  • The MySQL table you are storing that Hiragana 'MU' into needs to have the column declared CHARACTER SET utf8mb4 (or utf8).
  • Character sets big5, cp932, sjis, ujis, and several others, can handle the character. But these are not recommended.
  • "Mojibake" for that character is ã‚€, as seen from CONVERT(CONVERT(BINARY('む') USING latin1) USING utf8mb4)
  • So, get rid of the function call, and all may be fine.

As for "detecting failure", that is usually done by seeing garbage in output. The usual cure is to configure things correctly and have no conversion function calls. I can provide some techniques for selected failure cases, but no general method.

See https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

As mentioned in the above, http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases provides all the "how to fix" ALTERs that I have identified. Caution: It is critical to know which situation you have before attempting a fix-it-Alter.


Another trick (that works sometimes):

SELECT col, LENGTH(col), CHAR_LENGTH(char) ...

Then match the text against the CHAR_LENGTH. The count should match, but there are exceptions. Meanwhile, for Japanese, most (not all -- eg, space and Romanji are 1 byte) characters take 3 bytes, so LENGTH will be about 3 times as big as CHAR_LENGTH.

For European accented characters, the utf8 is almost always 2 bytes. But it is possible to "compose" an accented letter from the letter and the accent, thereby leading to CHAR_LENGTH=2 (I think) and LENGTH = about 4. This can easily be confused with double-encoding, which is often LENGTH = 4 or 5.

Most Emoji are 4 bytes and start with hex F0

A rule of thumb that often works to recognize double-encoding is when the hex is somewhat repetitious. European Cx, Cyrillic: Dx, Mid-Eastern - other things; etc.

The double encoding for that MU: hex C3 A3 E2 80 9A E2 82 AC or display ã‚€ -- the various accented "A" and "a" is a clue.


Your Answer

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

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