I am currently working on a project which is translated in 18 languages like russian, german, swedish or chinese. I have some issues with sorting countries names in different languages. For example, countries names in french are sorted like that : - États-Unis - Éthiopie - Afghanistan
I don't have this issue on my local server using MAMP. My database's character set is configured as utf8 and the collation is utf8_unicode_ci. I have exactly the same configuration on the distant server.
I created a my.cnf file on my local server with the following instructions in order to correctly display special characters :
[mysqld]
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8
On the distant server, the my.cnf file does not contain these lines. When I tried to add them, MySQL did not recognise special characters anymore like if it was interpreting them as latin1. I checked collation_database and all character_set variables but they are all set as utf8 / utf8_unicode_ci.
Here is the SQL code for the creation of the table :
CREATE TABLE esth_countries ( country_id varchar(2) COLLATE utf8_unicode_ci NOT NULL, name varchar(100) COLLATE utf8_unicode_ci NOT NULL, region varchar(40) COLLATE utf8_unicode_ci NOT NULL, language_id varchar(2) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (country_id,language_id), KEY language_id (language_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Special characters are correctly displayed on my distant server. The only problem concerns sorting using ORDER BY clause.
It seems like there is something wrong with the distant server's configuration but I can't figure out what.