Bug #6722 Not able to insert data using greek symbols
Submitted: 19 Nov 2004 16:38 Modified: 6 Jan 2005 21:44
Reporter: Nicholas Large Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.0.0-alpha-max-debug OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[19 Nov 2004 16:38] Nicholas Large
Description:
Hello Mysql.

Hello  

I found myself recreating a currently running Access Database into Microsoft Visual Studio .NET 2003 Academic, using a mySQL back end. I am running WinXP on my Dell machine. I have created a mysql server, and have created some tables to recieve data from the current development MS-Access database. 

The company I work for has clients all over the E.U., France, Spain, Holland, Sweden, Denmark, Finland ... and also Greece. 

Some project applications that we recieve for funding have Project Titles or Organisation Names written in Greek, so they use these kinds of characters: theta, pi etc. These characters are viewable in Access, and also in the textboxes available in Visual Studio, but when I insert these into the mysql database they change to question mark-?S- characters. 

Without the ability to add these as the correct characters in the table, the users will not be impressed and the project (and moving to mySQL from access) will fail.  The users may also wish to add organisations in any variation of european language, but particularly those within the European Community, so it shouldn't be just greek or english that should be accepted, but hungarian, turkish, slav etc.

Until I find the answer then this project is on hold, that is why it has such importance - I would really like to move to MySQL from Access, and with the answer to this question, I believe we can.

FYI: 

mysql> Show character set;

big5 - big5_chinese_ci
latin1 - latin1_swedish_ci
ujis - ujis_japanese_ci
sjis - sjis_japanese_ci
tis620 - tis620_thai_ci
euckr - euckr_korean_ci
gb2312 - gb2312_chinese_ci
gbk - gbk_chinese_ci
utf8 - utf8_general_ci

mysql> show collation;

Collation	             Charset	Id	Default	Compiled	 Sortlen
--------               -------   --          -------   --------  -------
big5_chinese_ci	big5	1	Yes	Yes	 1
big5_bin	             big5	84	Yes        Yes	1
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_german2_ci	latin1	31	Yes	2
latin1_bin	             latin1	47		Yes	1
ujis_japanese_ci	ujis	12	Yes	Yes	1
ujis_bin	             ujis	91		Yes	1
sjis_japanese_ci	sjis	13	Yes	Yes	1
sjis_bin	             sjis	88		Yes	1
tis620_thai_ci	tis620	18	Yes	Yes	4
tis620_bin	tis620	89		Yes	1
euckr_korean_ci	euckr	19	Yes	Yes	1
euckr_bin	             euckr	85		Yes	1
gb2312_chinese_ci	gb2312	24	Yes	Yes	1
gb2312_bin	gb2312	86		Yes	1
gbk_chinese_ci	gbk	28	Yes	Yes	1
gbk_bin	             gbk	87		Yes	1
utf8_general_ci	utf8	33	Yes	Yes	1
utf8_bin	             utf8	83		Yes	1
ucs2_general_ci	ucs2	35	Yes	Yes	1
ucs2_bin	             ucs2	90		Yes	1
binary	             binary	63	Yes	Yes	1

Variables:-

character_set_server	latin1
character_set_system	utf8
character_set_database	latin1
character_set_client	latin1
character_set_connection	latin1
character-sets-dir	C:\\mysql\\share\\charsets/
character_set_results	latin1
collation_connection	latin1_swedish_ci
collation_database	latin1_swedish_ci
collation_server	latin1_swedish_ci
concurrent_insert	ON

Hope this is enough info?

Regards,
Nick.
 

Regards, 
Nick

How to repeat:
Unknown
[6 Jan 2005 21:42] Hartmut Holzgraefe
looks like your installation is missing the greek latin7 character set as it doesn't show up in "SHOW CHARACTER SET"

but anyway, if you want to mix regular latin1 european characters with greek latin7 your only option is to configure your MySQL server to use UTF-8 as its default character set all over the place

right now you are using latin1 for anything but the system tables so non-latin1 characters
have to be replaced by something else, question marks in this case

see http://dev.mysql.com/doc/mysql/en/Charset.html for details on character set support
in MySQL
[6 Jan 2005 21:44] Hartmut Holzgraefe
PS: the bug system to ask this kind of questions, please use our mailing lists or public web forums for this in the future

see http://lists.mysql.com/ and http://forums.mysql.com/
[11 Apr 2005 13:29] Mircea Pop
Hi Nicholas,
I have the same problem and I don't know how to fix it. Can you help me?
I am using Mysql 4.0.24.

Thanks,
Mircea
[11 Apr 2005 15:23] Nicholas Large
Hello Mircea

A lot has changed since I wrote this email originally.  If your still having problems, you can contact me on nlarge@nobodyleargas.ie, just take the nobody out.

Cheers,
Nick.
[12 Apr 2005 6:34] Mircea Pop
Hi,
I've developed an application that stores some data in a mysql database. The application is
written in VB .NET. I have only a table with a few fields, one of which is a TEXT field. In this
field I want to store text in greek and also normal text. Then I publish the table using php ( a
simple html generated page). How do I have to setup mysql so that the greek characters are insered
correctly, and how do I sent them from vb.NET.

Also the email address nlarge@nobodyleargas.ien is not good.

Thanks,
Mircea
[12 Apr 2005 9:02] Nicholas Large
Hi Mircea.

If you do not include the word "nobody" in the email address nlarge@nobodyleargas.ie you will get the correct email address.  I only write the incorrect email to ensure that no spammers can get at it.

Also, You have to ensure that the database is in utf-8 format.  This is done by using the "show full columns from" command.  

Eg.  Show full columns from mytable

This will produce a more detailed list than "describe mytable" does.  In this list is a column called "collation".  This needs to be utf8_unicode_ci.  How do we do this?  Well, you need to execute the following commands, but it will be worth backing up your database first if this database has plenty of data in it, as some inconsistencies may arise (you just don't know!?):

    use databasename
    show variables like 'c%'

look at the list of variables (its worth flicking through the manual to get a read on what they mean.

    set character_set_database = utf8;
    set character_set_server = utf8;
    set character_set_system = utf8;
    set collation_database = utf8_general_ci;
    set collation_server = utf8_general_ci;

    set names utf8;

Now all your collations and character sets should be set correctly.  Try this and try an insert.  

Another note is that the "set names" command only lasts for as long as the connection is open, not for as long as the server is up, and is specific only to a particular connection.  Upon initializing a connection to a database it is worth sending the "set names utf8" command to ensure that items are returned correctly.  If you use Visual Studio .NET as a development tool you will have to use the --ansi-quotes option in order to use Visual studio .net server explorer with mysql when dragging items from server explorer to a dataset.

Hope this helps.
NL.