Troubleshooting Issues with MySQL Character Sets Q & A

Want create site? Find Free WordPress Themes and plugins.

MySQL Character SetsIn this blog, I will provide answers to the Q & A for the Troubleshooting Issues with MySQL Character Sets webinar.

First, I want to thank everybody for attending the March 9 MySQL character sets troubleshooting webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: We’ve had some issues converting tables from
utf8  to
utf8mb4. Our issue was that the collation we wanted to use –
utf8mb4_unicode_520_ci – did not distinguish between spaces and ideographic (Japanese) spaces, so we were getting unique constraint violations for the 
varchar fields when two entries had the same text with different kinds of spaces. Have you seen this problem and is there a workaround? We were wondering if this was related to the mother-child character bug with this collation.

A: Unfortunately this issue exists for many languages. For example, in Russian you cannot distinguish “е” and “ё” if you use
utf8 or
utf8mb4. However, there is hope for Japanese: Oracle announced that they will implement new language-specific
utf8mb4 collations in MySQL 8.0. I already see 21 new collations in my 8.0.0 installation.

In 8.0.1 they promised new case-sensitive and Japanese collations. Please see this blog post for details. The note about the planned Japanese support is at the end.

Meanwhile, I can only suggest that you implement your own collation as described here. You may use
utf8_russian_ci collation from Bug #51976 as an example.

Although the user manual does not list
utf8mb4 as a character set for which it’s possible to create new collations, you can actually do it. What you need to do is add a record about the character set
utf8mb4 and the new collation into
Index.xml, then restart the server.

Q: If receiving
utf8 on
latin1 charset it will be corrupted. Just want to confirm that you can reformat as
utf8 and un-corrupt the data? Also, is there a time limit on how quickly this needs to be done?

A: It will be corrupted only if you store
utf8 data in the 
latin1 column. For example, if you have a table, defined as:

And then insert a word in
utf8 format into it that contains characters that are not in the 
latin1 character set:

The data in
UTF8 will be corrupted and can never be recovered:

However, if your data is stored in the 
UTF8 column and you use
latin1 for a connection, you will only get a corrupted result set. The data itself will be left untouched:

Q: Can you discuss how charsets affect mysqldump? Specifically, how do we dump a database containing tables with different default charsets?

A: Yes, you can. MySQL can successfully convert data that uses different character sets, so your only job is to specify option
defaultcharacterset for
mysqldump. In this case, strings in any character set you use can be converted to the character set specified. For example, if you use
cp1251 and
latin1, you may set option
defaultcharacterset to
cp1251,
utf8 and 
utf8mb4. However, you cannot set it to
latin1 because Cyrillic characters exist in the 
cp1251 character set, but do not exist in
latin1.

The default value for
mysqldump is
utf8. You only need to change this default if you use values that are outside of the range supported by 
utf8 (for example, the smileys in
utf8mb4).

Q: But if you use the 
singletransaction option for
mysqldump, you can only specify one character set in the default?

A: Yes, and this is OK: all data will be converted into this character set. And then, when you will restore the dump, it will be converted back to the character set specified in column definitions.

Q: I noticed that MySQL doesn’t support case-sensitive
UTF8 character sets. What do you recommend for implementing case-sensitive
UTF8, if it’s at all possible?

A: In the link I provided earlier, Oracle promises to implement case-sensitive collations for
utf8mb4 in version 8.0.1. Before that happens, I recommend you to implement your own case-sensitive collation.

Q: How are tools like
pttablechecksum affected by charsets? Is it safe to use a 4-byte charset (like
utf8mb4) as the default charset for all comparisons? Assuming our tables are a mix of
latin1 ,
utf8 and
utf8mb4.

A: With this combination, you won’t have any issues:
pttablechecksum uses a complicated set of functions that joins columns and calculates a 
crc32 checksum on them. In your case, all data will be converted to
utf8mb4 and no conflicts will happen.

However, if you use incompatible character sets in a single table, you may get the error
“Illegal mix of collations for operation ‘concat_ws’ “:

The tool continues working, and will process the rest of your tables. I reported this behavior as Bug #1674266.

Thanks for attending the Troubleshooting Issues with MySQL Character Sets webinar.

Sveta Smirnova

Sveta joined Percona in 2015. Her main professional interests are problem solving, working with tricky issues, bugs, finding patterns that can solve typical issues quicker and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as a Support Engineer in the MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle.
She is the author of the book “MySQL Troubleshooting” and JSON UDF functions for MySQL.

Source link

Did you find apk for android? You can find new Free Android Games and apps.

LEAVE A REPLY

Please enter your comment!
Please enter your name here