character set handling with MySQL 4.1

sounds reasonable, but the question is why it works for so many other people who use G2 with mysql 4 or 4.1 and their non-ascii characters. probably because they're using utf-8 DBs and it defaults to utf-8 connections.

there's no choice for the internal character set G2 uses. G2 handles everything in UTF-8. UTF-8 output (generated HTML), UTF-8 input (data from browser), and a UTF-8 storage (database). Input from the filesystem (add from local server) is converted to UTF-8, if needed.
so specifying UTF-8 as the charset in the client handshake is correct.
the question is whether it is needed. For my mysql 4.1.10 / 4.1.11 installs, this wasn't necessary.

All right, here is my test: create a new album and enter the description "Věruš

All right, here is my test: create a new album and enter the description "VXruYZino" there. Unfortunatelly it seems that the Drupal installation has some problems with the MySQL character set, too, so I have to write it this way: please replace X with the character which is available on the key 2 on the czech keyboard, Y for key 3 and Z for key 4. Will it be really shown correctly with MySQL 4.1.10/4.1.11? It was not for me until I added that mysql_query('set character set utf-8'). I bet that Drupal has exactly the same problem with MySQL 4.1 as Gallery - that's not surprising, both are PHP applications.

just tested with mysql 4.1.10a and it worked. i had to view your previous post ( Posted: Tue, 2005-09-20 12:36) in internet explorer to see the correct characters, in firefox it looks garbled.
i don't have a czech keyboard, but it wasn't needed.

but let's stop this example thing.
the question is: couldn't you just configure your mysql server to handle everything in UTF-8 by default?
or isn't this a property that can be set for each database? is your mysql g2 database using utf-8?
or is it a good idea to use this set-charset command and is it only required for mysql 4.1+ or since what exact version? or can we query the mysql server if this is supported? etc.

To be more specific: the library, which would need to be modified (or you could call it "hacked" as well), is libmysqlclient.so.14. In the source file sql-common/client.c as distributed from mysql.com you can see this:

/* Set character set */
if (!mysql->options.charset_name &&
!(mysql->options.charset_name=
my_strdup(MYSQL_DEFAULT_CHARSET_NAME,MYF(MY_WME))))
goto error;

Basically it means that either mysql->options.charset_name is set previously (unfortunatelly PHP does not do this), or you get the value of MYSQL_DEFAULT_CHARSET_NAME, which is "latin1". However, modification to this library could break other applications, so I do not recommend it. Better possibility would be to add call to mysql_options(&mysql->conn, MYSQL_SET_CHARSET_NAME, (const char *)&desired_character_set) to ext/mysql/php_mysql.c in the sources of PHP. The variable desired_character_set should be set based on the administrator's requirements, the best approach seems to be a new configuration directive in php.ini. Again - modification and recompilation is needed, which is not what an average Gallery user would like to do, I think.

I wrote all this detailed information here only to demonstrate that modifying the PHP scripts (Gallery, Drupal, etc.) to call mysql_query('set character set utf8') seems to be the easiest solution, even though it is just a workaround for the IMO incorrect PHP+MYSQL cooperation.

That one letter (entered as something like c, but shown as something like L) is not an IE issue. It is what I try to explain since the beginning of this thread - it is a result of incorrect usage of MySQL charsets. That one character similar to c is not compatible with the latin1 character set and therefore gets corrupted (translated to another character, similar to L).

Let me explain how charsets work in MySQL:

When the client stores any textual data (for example char or varchar) to the server, it has to specify their charset. If it does not specify the charset explicitly, with MySQL 4.1 it defaults to latin1. With MySQL 4.0 it defaults to the default charset of the server. The server then translates the received data from the charset of the client to its own charset and stores them.

When the client retreives any textual data (for example char or varchar) from the server, it has to specify in which charset it would like to receive the data. If it does not specify the charset explicitly, with MySQL 4.1 it defaults to latin1. With MySQL 4.0 it defaults to the default charset of the database. The server translates the stored data from their saved charset to the charset which the client wants and then sends them to the client.

In my case I have configured the MySQL server to store the data in UTF-8 (with default-character-set=utf8 in my.cnf). Gallery 2.0 has the data in UTF-8, too. So I would like no translation to happen. To acheive this, I need to tell the MySQL server that the client charset is UTF-8, too. That is done by mysql_query('set character set utf8'). If I do not do it, MySQL assigns me the default client charset, which is latin1, and does the inappropriate translation, which corrupts some characters.

For MySQL versions 4.1 and newer I beleive that the best solution is what I have done - ask the server to store the data in UTF-8, either globally in my.cnf or for one specific table: CREATE TABLE xyz (...) ENGINE=InnoDB DEFAULT CHARSET=utf8, and do mysql_query('set character set utf8') to let the server know that the client character set is UTF-8, too. No translation happens and everything works nicely.

For MySQL versions 4.0 and older it is more difficult. Those versions do not support UTF-8 yet, but they support other charsets (8-bit only). That enables them to do the charset translations, which could eventually corrupt the textual data. After reading http://dev.mysql.com/doc/mysql/en/binary-varbinary.html please try to change the used column data types from "char(x) to "char(x) binary" or "binary(x)" and from "varchar(x)" to "varchar(x) binary" or "varbinary(x)". Will the character similar to c show correctly then, or will it again incorrectly show as similar to L?

I hope that this quite long post will explain you how the character sets really work in MySQL. If you understand how the character sets work with Oracle, you should find that MySQL is actually the same. The database stores the data in one character set and enables the clients to access it in different character sets, doing the translation automatically - provided that the clients specify their character set correctly.

nope.
but it's on my uber - long to do list and i'm writing exams these days.

plus:
bharat let me know that mysql and postgresl can handle our utf8 characters even when using latin1 db tables. i don't get why, but as you see, it works in my g2 installs (except for this one character).
but in the installer we recommend utf-8 for mysql and unicode for postgresql, so i don't know yet how this stuff works.
i'll read into it, but not right now.

you'd have very strong arguments if you want to convince us to change to binary formats.

we've created the following task:
http://sourceforge.net/pm/task.php?func=detailtask&project_task_id=122508&group_id=7130&group_project_id=14056

thanks for the update. good that drupal did the hard work for us concerning mysql and pg. we'll also have to look into settings for oracle and db2.
the linked discussion is very informative and can be used as a resource when patching g2.