Mysql 5.6 client is unable show UTF8 characters

Question

Mysql databases are hosted on AWS RDS Mysql 5.6

On one of the EC2 Cent OS 6.5 instance, I had Mysql Client 5.1. I used it to execute SELECT queries over the RDS-Mysql. There is one particular query that we do to generate a CSV file. The database contains some of the Indian languages, namely: Hindi, Marathi and Bengali. While using Mysql 5.1 client, these language texts were stored correctly in CSV.

I upgraded the client to mysql 5.6.26 (RPMs downloaded from : HERE for Redhat/Oracle Linux). Now, the same query gives garbled characters for the same query.

A short example of the difference in output :
Mysql 5.1 client:

"365253","121","publish","लेनोवो के3 नोट 4जी स्मार्टफोन आज फ्लैश सेल के लिए उपलब्ध है। हालांकि इस फ्लैश सेल में वहीं उपभोक्ता भाग ले पाएंगे जिन्होंने पहले ही इसके लिए रजिस्ट्रेशन किया हो। लेनोवो के3 नोट एक्सक्लूसिवली ईकाॅमर्स साइट

Mysql 5.6 client:

"365253","121","publish","लेनोवो का ससà¥<U+008D>ता 4जी फैबलेट के3 नोट आज फà¥<U+008D>लैश के लिà¤<U+008F> होगा उपलबà¥<U+008D>ध""

I have set character in '.my.cnf'

cat /root/.my.cnf


[client]
user=captain
password=GuruWOWKnowsA11
default-character-set = utf8

I also checked character set for database/RDS (show variables like 'char%'). There were couple of latin character set. I changed those to utf8 and tested the outcome of the SELECT query again---Nothing changed.
[A] Before parameter changes:

+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.22.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

[B] After parameter changes:

+--------------------------+-------------------------------------------+
| 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     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.22.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

What could be the issue here? Is it a bug in client ver 5.6 ?

Answer 1

I suppose the following happened: you (or someone else) created the non-utf-8 database and inserted the utf-8 data into it. Then you somehow (it's unclear to me if it happened, you didn't mention it; however this is not that important) upgraded your database to 5.6, or, at least, the mysql client. New client clearly sees the database encoding is latin1, and misinterprets the actual utf-8 data (and he has any right to do that).

I suggest you dump the database using mysql 5.1 client, you will get the dump containing valid utf-8 sequences, then you create database with utf-8 encoding and import data into it with newer client. pay attention to the set names directive in your dump, and change it if needed (to the utf-8). Also notice that no destructive actions (like dropping databases) should be performed unless you're absolutely happy with the result.