Dear Rick,
Thanks for your kind reply. Since I am not overly familiar with MySql, I did not understand everything you wrote but I read all the references you provided and I applied the suggestions. There is some progress, but the problem is not solved yet. I understand that it is needed “to declare both ends to be latin2”. I continue to have problems with the declaration, let me explain it. The characters that are not saved and displayed correctly are the latin capital letter O with double acute (U+0150), the latin capital letter U with double acute (U+0170) and their small counterparts (U+0151, U+0171). These are supported both by latin2 and utf8 character set. The problem seems to be that the character_set_server and character_set_database of my hosting provider’s server is set to latin1, which simply “removes” the double acute accents.
Where I had no problem was to set my database and the columns using the alter database and alter table commands. Where I have problems is to set the other parameters, such as character_set_server.
To experiment with the settings, first I used my own machine as localhost. The initial situation was that all the character and collation settings were latin1 (as configured with the Instance Config Wizard). Then, using the SET command, I changed the character_set_database into utf8. At this stage my utf8 characters were not saved/displayed correctly (using my website). Then, using the set command, I changed the character_set_server into utf8. At this stage my utf8 characters were not saved/displayed correctly (using my website). Then, using the set command, I changed the character_set_connection into utf8. At this stage my utf8 characters were not saved/displayed correctly (using my website). Then, using the set command, I changed the character_set_client into utf8. At this stage my utf8 characters were not saved/displayed correctly (using my website). Then, using the set command, I changed the character_set_results into utf8. With the show variables command I checked the corresponding variables and all character and collation related variables were set to utf8. So I expected that finally, my utf8 characters will be saved and displayed correctly on my website. However, my utf8 characters were not still saved/displayed correctly! It implies a MySql bug: if a user (me) follows your suggestion and sets all the variables to utf8, it does not help. (SET NAMES utf8 did not help either.)
I observed another phenomenon which may imply another MySql bug. When I closed the MySql Command Line Client displaying all the character and collation related variables set to utf8, and reopened it without making any changes, all utf8 character and collation settings reverted to latin1! Seemingly, the set command is a kind of illusion, the result of which is displayed on the command line, but it has no real effect.
As the next step, I used the Instance Configuration Wizard to set the character set to utf8. Suddenly, my websites saved the utf8 characters correctly! Besides, MySql does not „forget” these variables settings compared to when using the SET NAMES command. (Actually, I get the correct characters if I run the Config Wizard with latin2 and set my database to latin2).
But my problem remains to exists: on my hosting provider’s server, for obvious reasons, I have no Instance Configuration Wizard in order to change the character set variables into utf8 (or latin2). I only have a phpMyAdmin surface. On this surface I am allowed to change on database level the database settings to utf8 which I have done (character_set_client, character set connection, character set results, collation connection), but the character_set_client global value, character_set _connection global value, character_ set_database, character_set_results global value, collation_connection_global value, collation_server and collation_database remain latin1! And I do not know how to change them into utf8.
If I try to change these values on localhost level, I get the following results:
set character_set_server=utf8;# MySQL returned an empty result set (i.e. zero rows).
set collation_server=utf8_general_ci# MySQL returned an empty result set (i.e. zero rows).
set character_set_database=utf8;# MySQL returned an empty result set (i.e. zero rows).
set collation_database=utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).
phpMyAdmin says the query was executed successfully, but if I use the show variables command, I have to conclude that nothing was changed, the settings are still in latin1!
My question remains: How can I save and display utf8 characters on my website, under such conditions and restrictions? As I mentioned, it is possible to do it on my own machine using the Instance Config Wizard, but I cannot do it on the hosting server, and unfortunately some of the character set variables are set to latin1 which I cannot change. This is where I need help.
You have mentioned in the thread
http://forums.mysql.com/read.php?103,370149,370653#msg-370653 that the SET NAMES utf8 should be applied when connecting. My question: How and where exactly? On my local testing machine I have set it the character set parameters in advance using the Instance Config Wizard. Is it possible to set such variables from the client side, when the application (website) is connecting to the server? My website uses various aspx pages to „connect” (I use .NET 3.5 and Windows server), I do not know where to apply the SET NAMES command, and it is questionable whether it has any affect (as I mentioned above). I use ADO.NET entities to retrieve data from and save data to MySql, it is embedded into C# language, and it does not understand the SET NAMES command. Maybe you could describe an example so that I can understand how to use it. I am trying to get the MySql work with utf8 characters for over 2 months, so any help is appreciated.
You have written that “do some settings after connecting”. There are zillions of options to do some settings after connecting, please provide me with the concrete settings I need to apply, thank you.
For MySql communication I use ADO.NET entity data models. The coding looks like this:
me = new Model.Models();
// Models1.models dbRow = me.models.First(p => p.UserName == User.Identity.Name);
var models = from p in me.models
where p.PStatus1 == true &&
It understands the where command, but it does not understand the “SET NAMES” command. It delivers the following error message: “cannot convert lambda expression into type string because it is not a delegate type.”
The requestEncoding="UTF-8" responseEncoding="UTF-8" did not help either in the globalization element in the system.config file. The <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> element did not help either in the head section of the website.
I have also tried in the Url request the ?characterEncoding=utf-8, but it did not work either.
These are my current hosting server settings shown in phpMyAdmin that do not work (with many latin1 settings I cannot change to utf8):
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\
collation connection utf8_general_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
completion type
I have working on fixing this problem for about 2 months, and I would need a solution, possibly a simple one. I assume MySql supports uft8/or latin2 characters if the hosting server is set to latin1, but I would like to know how to do it.
Best Regards,
Zoltan