MySQL Bugs: #18832: MySQL can’t store characters for different languages

Bug #18832	MySQL can’t store characters for different languages
Submitted:	6 Apr 2006 7:25	Modified:	22 Jun 2006 7:08
Reporter:	Grace Coronado	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
We have an existing MS Access table which contains columns that can store different characters (from languages of different countries).  When the same data set were appended in a MySQL table, the data were not stored properly.

Our current settings:
    Windows XP 5.1
    MS Access 2002
    MySQL Server 5.0.18
    MyODBC 3.51.12 
    MS Jet Engine 4.0

How to repeat:
Define DSN where: 
    Connect Options/Initial Statement: set names utf8

Create MySQL table:
     CREATE TABLE `commnames_x` (
       `ID` int(11) NOT NULL auto_increment,
       `CommonName` varchar(255) default NULL,
       `Language` varchar(255) default NULL,
       `UniTextCode` longtext,
       PRIMARY KEY  (`ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

See attached UnicodeDB.mdb file for the MS Access table.  The default character is set to: Arial Unicode MS.  Open the CommNames table, select and COPY all the rows.

From Navicat, open CommNames_x table then PASTE the records from Access.

The records will be converted as follows:

mysql> select commonname, language from commnames;
+----------------+------------------+
| commonname     | language         |
+----------------+------------------+
| ????           | Tamil            |
| ????           | Gujarati         |
| ??????         | Arabic           |
| ???????        | Kannada          |
| ?????????      | Bengali          |
| ?a??a??a?      | Greek            |
| Karagöz baligi | Turkish          |
| ??????         | Thai             |
| ????           | Japanese         |
| ???            | Mandarin Chinese |
+----------------+------------------+
10 rows in set

Sample MS Access table with Unicode

Attachment: UnicodeDB.mdb (application/msaccess, text), 124.00 KiB.

Thank you for a problem report. Please, send the results of the

SHOW VARIABLES LIKE 'char%';

from the same mysql command line window where you get that ???? from the table.

Thanks for your speedy reply.  Here's the result of show variables command that you suggested.  - Grace C.

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | latin1                                                  |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_results    | latin1                                                  |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
7 rows in set
 
mysql>

Please, execute 

set names utf8;

in mysql command line window, repeat same your query again and send the results.

I don't think setting the char to utf8 at mysql prompt is the solution.  Running a SELECT statement will not change the way the data are stored in the MySQL server.  

What I did is change the character setting from mysql.ini.  Here’s the new setting:

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | latin1                                                  |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_results    | latin1                                                  |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
7 rows in set
 
mysql> show variables like '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set
 
mysql>

Delete the existing records, then append again the records from MS Access to MySQL tables (through Navicat).

I still got the same result for the SELECT statement: 

mysql> set names utf8;
Query OK, 0 rows affected 
 
mysql> select commonname, language from commnames;
+-----------------+------------------+
| commonname      | language         |
+-----------------+------------------+
| KaragÃz baligi | Turkish          |
| ?a??a??a?       | Greek            |
| ??????          | Arabic           |
| ?????????       | Bengali          |
| ????Â           | Gujarati         |
| ????            | Tamil            |
| ???????Â        | Kannada          |
| ??????          | Thai             |
| ????            | Japanese         |
| ???             | Mandarin Chinese |
+-----------------+------------------+
10 rows in set
 
mysql>

Please, copy data from Access table (just one row, say, for Arabic), then open mysql command line client, execute 'set names utf8;', then execute insert statement to insert that data into the table. Select data and check if they are stored properly. Inform about the results.

I want to check is it a server issue, really, or a problem somewhere between Access and server.

In response to your last message, I have done the following:

Insert a record to COMMNAMES table from MySQL prompt.  The problem with this is, I can’t even paste the value for special character.
    
    a) From Navicat/Tool/MySQL Console, the insert command will look like this:
mysql> INSERT INTO commnames ( CommonName, Language) VALUES ( '??????', 'Arabic'  );

    b) The same thing happens if I write a SQL script to be executed under MySQL prompt.

We tried to compare notes with a Chinese programmer based in Beijing who visited us just recently.  While he was here in the Philippines, he tried to remotely connect to his computer in China.  He was able to show us their database, also in MySQL storing Chinese characters and can be viewed in Navicat (the same version that we are using).  However, when he tried to access their database by linking it to the computer that we are using here, the Chinese characters can’t be shown properly.  The only difference that we can think of is the operating system.  They are using Chinese version of MS Windows.  So could it be a problem in the OS and not in the MySQL server?

We're planning to test it further, by asking him to migrate the MS Access COMMNAMES table to MySQL at his end, as he has gone back to China.

Thanks.  Hope we can already find a solution to this.

- Grace C.

Yes, it can be OS-related problem also. Do you have any news/results of your further checks? I have no Chinese version of Windows anyway, hence the question.

Here’s the replay from our Chinese counter-part:

“I have done the steps you told me on my computer,but the special characters can't  display correctly.But after I changed Navicat->Design Table->Options->Character Set to 'Utf-8',I input Chinese character, Japnese character,Cyrillic in the table and it show correctly but other character such as Arabic, Russian, Tamil is not correct.”

In addition, we have also tested entering data in MySQL using PHP form from our end.  The text box can display special characters, but at the background the data were stored as Unicode.

We also tried to install all the possible characters in our server.  I still didn’t work.  But we noticed that for the Control Panel/Regional and Language options, some characters can be displayed correctly (e.g. Arabic, Greek) but not for some (e.g. Chinese, Japanese)

I think there’s no OS (at least Windows OS) running MySQL that can support all the possible languages, maybe for some languages only like the case of the Chinese version for Windows.  We haven’t tried it under a different OS like Linux.

Can I close this issue, as it was proved that MySQL server CAN store characters for different languages (using utf8 character sets e.g.)?

As for this:

> In addition, we have also tested entering data in MySQL using PHP form
> from our end.  The text box can display special characters, but at the
> background the data were stored as Unicode.

it's another story with PHP and how it can be used in this context. Anyway, Unicode was created just for people to be able to store texts on all the different languages we have, so utf8-based character sets are the only way to achieve the goal in MySQL.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".