Search/sort having trouble with special characters

[jfly]

https://www.worldcubeassociation.org/search?q=lapicki doesn't find https://www.worldcubeassociation.org/results/p.php?i=2006LAPI01.

Neither does https://www.worldcubeassociation.org/results/persons.php?eventId=&regionId=&pattern=lapicki&search=Search

[jfly]

A related bug from Radu talking about the registrations page:

And one more observation for the software team. It seems that competitors are sorted by name, but "ș" letter is at the end, after all others, while normally it should be after "s".

[pedrosino]

Searching for "klo" doesn't find https://www.worldcubeassociation.org/results/p.php?i=2013KOSK01
(probably because of the L with a tick)
But it does find https://www.worldcubeassociation.org/results/p.php?i=2016KLOS01, which has the same L.

[pedrosino]

I meant the search at persons.php

[jfly]

There's a pretty heated discussion about the Ł character on the mysql bug tracker: https://bugs.mysql.com/bug.php?id=9604. There's a lot of confusion/unsatisfying answers, until you get to Peter Gulutzan's well written explanation of what's going on here. The answer is that Mysql's utf8_unicode_ci collation uses the Unicode Collation Algorithm (UCA) 4.0.0, which doesn't treat Ł as level 1 equivalent to L (see http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt). This was changed in UCA 4.1.0:

Data tables for 4.1.0 contain the following changes:
...
2. The change of weights for characters Æ, Ǽ, Ǣ; Đ, Ð; Ħ; Ł, Ŀ; and Ø, Ǿ (and their lowercase and accented forms) to have secondary (accent) differences from AE; D; H; L; and O, respectively. This is to provide a much better default for languages in which those characters are not tailored. See also the section on user expectations.

UCA 4.1.0 and above do treat Ł as level 1 equal to L (see jfly/translighterate#2 (comment) for some more information).

One fix would be to change the collation of our tables to utf8_unicode_520_ci, which does treat Ł as level 1 equal to L:

MariaDB [(none)]> select "L" = "Ł" COLLATE utf8_unicode_520_ci;
+----------------------------------------+
| "L" = "Ł" COLLATE utf8_unicode_520_ci  |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select "L" = "Ł" COLLATE utf8_unicode_ci;
+------------------------------------+
| "L" = "Ł" COLLATE utf8_unicode_ci  |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

Right now, our default table collation is ``:

and the Persons.name column has COLLATE utf8_unicode_ci,

I think the right thing to do here would be to change all our table column COLLATE's to utf8_unicode_520_ci. @timhabermaas, @larspetrus, @FatBoyXPC do you have any experience making such a change? Anything we should worry about?

[jfly]

Unfortunately, it turns out our production version of Mysql is 5.5.49:

~ @production> mysql -e "SELECT VERSION();"
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.5.49-0ubuntu0.14.04.1 |
+-------------------------+

which doesn't support utf8_unicode_520_ci:

mysql> show collation where Collation like '%utf8_unicode_%';
+-----------------+---------+-----+---------+----------+---------+
| Collation       | Charset | Id  | Default | Compiled | Sortlen |
+-----------------+---------+-----+---------+----------+---------+
| utf8_unicode_ci | utf8    | 192 |         | Yes      |       8 |
+-----------------+---------+-----+---------+----------+---------+
1 row in set (0.01 sec)

It's hard for me to find out when support for utf8_unicode_520_ci was added, but according to Peter Gulutzan's comment here, it seems like support was added in Mysql 5.6.

EDIT: Updating our version of mysql should be as simple as changing this one line in our Chef configuration: https://github.com/jfly/worldcubeassociation.org/blob/66a9530b6ab0e6dd23839a3f552862901363243c/chef/site-cookbooks/wca/recipes/default.rb#L74.

[jfly]

Note that thanks to #1489, we're actually using utf8mb4 instead of utf8. That means we want to use utf8mb4_unicode_520_ci instead of utf8_unicode_520_ci as mentioned above.

However, after reading this article, it sounds like we might want to switch to utf8mb4_0900_as_cs for accent sensitive and case sensitive search (I cannot imagine why we would want case insensitive search by default). Of course, mysql 8 isn't currently supported by AWS RDS =(

[toadfrommariokart64]

unsure if this is the same issue, or should be made a seperate post, but searching for Denmark & Friends will show the comp with the name "Denmark &amp ; Friends" (except there's no space before the semicolon), so there seems to be an issue when un-escaping characters?