Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a fairly unique situation, my team has ended up with UTF-8 bytes in a database that thinks the data is encoded as latin1.

At least, I'm 85% certain that this is the situation at hand.

For example, a right single quotation mark was handed to the database by a programming language that had no concept of encodings (Ruby 1.8) and just treated the data as raw bytes (0xE2 0x80 0x99). This data, as far as I can tell (how to verify?), was stored as those actual bytes. So now when the data is read out by a more intelligent programming language (Ruby 1.9), the database helpfully says "Oh! 0xE2 is 'â', 0x80 is '€', 0x99 is '™'", and so instead of "Mike’s", we end up with "Mike’s". This is also what I get in the mysql prompt when SELECTing that value.

So, essentially, we have a bunch of utf-8 encoded data stored in a database that thinks the data is encoded as latin1.

This makes me to somehow tell the database "No, no matter what you think, this stuff is actually utf-8". CONVERT TO doesn't seem like the right tool, because then I'll end up with permanent "Mike’s".


Failed/moronic attempt #1

I noticed this:

> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | utf8              |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | utf8_unicode_ci   |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

And thought that maybe changing character_set_results to latin1 would trick it into not doing any conversion of the bytes, resulting in the proper display of data on my utf8 OS.

Sure enough, SET character_set_results=latin1; results in instead of ’. Cool!

So I added this to my ~/.my.cnf (which is the only my.cnf, I checked):

[mysqld]
...
character-set-results=latin1

and when I go back to the MySQL prompt & check the character_set_% variables, it's still utf8.

Yes, it just occurred to me that mysqld is a deamon, which means I probably need to restart the whole mysql process for this to take effect. But whoever installed MySQL on this machine used the dmg instead of the brew (wasn't me!) and the MySQL pref pane is currently telling me that MySQL isn't running even though it clearly is, and anyhow before I go down that rabbit hole, I want to check with an actual DBA and see how ridiculous this is, or if there's just a better, cleaner way to do it.

share|improve this question
add comment

2 Answers 2

The solution isn't precisely the same but this question is where I originally found direction for a similar issue and the concepts there should take you where you want to go. MySQL has a BINARY character set and from all appearances, by converting through it, you can prevent MySQL from realizing what you're actually doing and being "too helpful."

Test case with character_set_client = utf8:

mysql> select CONVERT(CONVERT(CONVERT('Mike’s' USING latin1) USING binary) USING utf8);
+--------------------------------------------------------------------------------+
| CONVERT(CONVERT(CONVERT('Mike’s' USING latin1) USING binary) USING utf8)     |
+--------------------------------------------------------------------------------+
| Mike’s                                                                         |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You could use that logic to populate a new column that MySQL believes to be utf8.

share|improve this answer
    
Thanks! This led me to the correct solution, especially the helpful link you posted. I answered my own question describing my exact strategy (as well as posting a script to automate the conversion). –  chadoh Jan 24 '13 at 21:38
add comment
up vote 0 down vote accepted

Using CONVERT TO to switch a whole table is bad news for a few reasons, as discussed on the mysql performance blog (synopsis: CONVERT TO may change your text-type fields to mediumtext).

But it also seems better to actually convert your data to utf8 than to hack it with my.cnf tweaks.

To do that, go through and

ALTER TABLE `t1`
MODIFY COLUMN `c1` text CHARACTER SET binary,
MODIFY COLUMN `c2` varchar(255) CHARACTER SET binary;
ALTER TABLE `t1`
DEFAULT CHARSET=utf8,
MODIFY COLUMN `c1` text CHARACTER SET utf8,
MODIFY COLUMN `c2` varchar(255) CHARACTER SET utf8;

for every single table and its relevant columns in your database.

What this does: Changing to binary encoding makes MySQL keep the same bytes, but disregard their meaning. Then when you switch to utf8, the bytes still stay the same. Success!

This conversion will destroy any FULLTEXT indexes on these fields (binary encoding doesn't support FULLTEXT indexes), so make sure you recreate those, if you have them.

I wrote a bash script to automate this. It does not take care of the FULLTEXT index issue, but it should give you a good starting point.

share|improve this answer
    
To correct a single field that's already utf8 encoded to proper latin1: UPDATE table_name SET field=CONVERT(CONVERT(field USING binary) USING utf8) WHERE id = 12345; –  chadoh Sep 18 '13 at 13:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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