Worldcubeassociation.org: persons_check_finished.php incorrectly detects names differing due to accents as the same name

[jfly]

See this check for duplicates query.

Unfortunately, our version of mysql (5.6) does not support accent sensitive collations (they were added in mysql 8, see this comment for more details). Ideally we'd use utf8mb4_0900_as_cs.

utf8mb4_unicode_ci treats "Nguyễn" as equal to "Nguyen"

MariaDB [(none)]> select convert('Nguyễn' using utf8mb4) = convert('Nguyen' using utf8mb4) collate utf8mb4_unicode_ci;
+------------------------------------------------------------------------------------------------+
| convert('Nguyễn' using utf8mb4) = convert('Nguyen' using utf8mb4) collate utf8mb4_unicode_ci   |
+------------------------------------------------------------------------------------------------+
|                                                                                              1 |
+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

utf8mb4_unicode_520_ci treats "Nguyễn" as equal to "Nguyen"

MariaDB [(none)]> select convert('Nguyễn' using utf8mb4) = convert('Nguyen' using utf8mb4) collate utf8mb4_unicode_520_ci;
+----------------------------------------------------------------------------------------------------+
| convert('Nguyễn' using utf8mb4) = convert('Nguyen' using utf8mb4) collate utf8mb4_unicode_520_ci   |
+----------------------------------------------------------------------------------------------------+
|                                                                                                  1 |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[AlbertoPdRF]

Should this issue be added to https://github.com/thewca/worldcubeassociation.org/projects/4?

[jfly]

Good call, added!

[SAuroux]

@tussosedan as we have upgraded to MySQL 8 since, could this issue now be resolved easily?

[tussosedan]

That's an interesting issue! MySQL 8 does have case sensitive and accent sensitive collations for the utf8mb4 character set that we're using, while we currently use the case-insensitive collation utf8mb4_unicode_ci.

However, in the above and linked issues I see two different and possibly colliding needs -- on one hand, searching for Lapicki instead of Łapicki should perhaps find it, meaning the search should be insensitive to the difference. On the other hand, the duplicates search maybe should treat Nguyễn and Nguyen as different (although, shouldn't it be really recognized as a duplicate?).

On the technical side of things, we can ALTER specific columns to change their collations. This could be a heavy blocking operation that would require some downtime, and more importantly would need extensive testing to make sure nothing breaks unexpectedly afterwards.

Another option is to convert collations on the fly inside specific queries, without changing the actual data in the tables themselves. This is easier for having a different behavior just for some scenarios, but could have performance issues on large data sets as it would prevent index seeks -- although it might be good enough, and/or overcome by defining and indexing virtual columns with different collations.

I think first of all we need to decide what is the correct behavior for each use case. Happy to hear more thoughts from @jonatanklosko, @viroulep and @jfly too.