utf8 in database but order does not sort correct

[Markus Rietzler]

we have the following problem:

we use mysql 5.0.19 on linux.
default-charset for database is set to latin1.
collation is latin1_swedish_ci

we have setup one of the databases with default-charset utf8 and collation utf8_unicode_ci. all the tables of this database also have utf8/utf8_unicode_ci, even the columns are utf8_unicode_ci.

when we now do a SELECT with ORDER the sort order is not correct. german umlauts are not sorted correct. eg:

| Anwenderdienste
| Anwendungserlass zur Abgabenordnung
| AO-Kartei
| ÃÅber uns <== Über uns
| ÃÅbernahme der lokalen Seiten UNSER HAUS <== Übernahme
| Arbeiter

even if we use a order by title COLLATE utf8_unicode_ci it does not work. also a set names 'utf8' doesn't help. i have used mysql-client with charset utf8.

mysql> show variables like "%coll%"
-> \g
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_unicode_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

on the webpage the umlauts/utf8-characters are displayed correct.

any idea where to look?

markus



Edited 1 time(s). Last edit at 04/13/2006 03:06AM by Markus Rietzler.