Diacritics breaks order and search

[elaborator]

Last year, I reported a bug (CRM-10379)[1] regarding the search function not discriminating between letters with different diacritics.

Example: The search term "åhl" returns both surnames "Ahl" and "Åhlberg" where only the latter is a proper search hit in Swedish.

Some other languages are more liberal regarding those rings and umlauts, thus just treats most diacritics as variants of the basic letter. However Scandinavian languages do not. Å, Ä, Ö, Æ and Ø are considered distinct letters, while most foreign accents (to us) such as É and Ô are treated as variants of the main letter. Complicated, yes - but it should not really be much of a concern to CiviCRM as long as it entrusts these issues to MySQL, as it is capable of doing the right thing with these diacritics when given the chance.[2]

Until very recently (up to version 4.4.2), a heavy handed hack worked sufficiently. I just changed the collation of every table from utf8_general_ci to utf8_swedish_ci without any visible problems. Both searches and the order of names showed up just as expected. This hack was of course bound to break some day. However, I hoped that the bug I reported would be fixed before this hack became ineffective. Now this unresolved bug has finally caught up with me. That fix I used now breaks the Quick Search function. I just wanted to raise this issue with the developers once again.

[1] http://issues.civicrm.org/jira/browse/CRM-10379
[2] http://collation-charts.org/mysql60/mysql604.utf8_swedish_ci.html

[Donald Lobo]

any idea why the fix you used breaks in 4.4?

i dont know what a good solution to this issue is, i'll see of some of our i18n folks will take a look

lobo

[mathieu]

Hi,

If you can check in your CiviCRM log (in template_c/ConfigAndLog/), there should be a log of the query that failed. It is probably related to a "join" between two tables of different 'collate'. For example, if you create a new custom group, you need to change the collate of the resulting new mysql table. Another option, might be caused by temporary tables. Just an assumption, of course, and you may have already checked that, but the log should have the answer.

Any thoughts on how other systems handle the collate issue? For example, we could have an extension that would take care of ensuring that the collate corresponds to the one set by the administrator.

Mathieu

[elaborator]

Thank you! I'll check the logs and report back to this thread in a few days.