1

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 ?

  • How did you "changed" database parameters exactly from latin1 to utf-8 ? From my point of view you should dump/restore it using proper codepage each time. – drookie Sep 25 '15 at 8:48
  • Databases are hosted on Amazon's RDS. The change was made to 'Parameter Group' used by it. It is a web-based console showing parameters enabled for the RDS Instance. AWS applied the changes with a compulsory reboot. Now, I am not sure how they do it. I made these changes based on some suggestion I found of the WEB. What bothers me is that, the same query returned perfect data under Mysql client version 5.1 (with 'character_set_database | latin1'), but it returns junk characters in version 5.6. The only change is the client. Should I be modifying anything on server side at all ? – anup Sep 25 '15 at 10:36
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.

  • Clarification on DB creation: { Mysql Databases were not upgraded to 5.6. These are provided by Amazon and it was always 5.6. One of the engineers created a new Linux instance/server which will be used by operations team for their task, including DB activities. I noticed that client is version 5.1 on this server. Since the version is not in sync with Mysql DB, I updated it to 5.6. } – anup Sep 25 '15 at 10:52
  • Query based on your answer: { Is it possible that someone used Mysql client 5.1 to dump and restore a DB to server version 5.6 may have caused this issue ? } – anup Sep 25 '15 at 10:54
  • It's hard to guess. What bothers me most, is how the change latin1 -> utf-8 was done for the database, and I doubt it was done right. So I guess you should do it manually and see if it helps. – drookie Sep 25 '15 at 10:56
  • I tried a few things: (1) Downgraded client to 5.1 and checked the query (without .my.cnf). It returned non-english language correctly. (2) In .my.cnf I enforced default character set to utf8 and executed the query. Msql 5.1 too returned junk characters just like 5.6 did. (3) Checked 'show create tables' and found character encoding as latin1. (4) Removed 5.1, and installed 5.6. Enforced character-set to latin1 in .my.cnf. Executed query and got expected results (non-english text appeared correctly). I think that Mysql 5.6 client defaults to utf-8, while 5.1 client may be latin1 – anup Sep 25 '15 at 11:25
  • You're probably right. But notice, even with character_set_database = latin1 and character_set_client = latin1, when utf-8 sequences look correct in output, you still not getting proper sorting in queries, because sort rules are applied to data like it was in latin1. – drookie Sep 25 '15 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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