Some Unicode characters don't show up in list names

[soliloquist-tatoeba]

Some Unicode characters can't be used in list names while some others can be used. They all appear on the edit screen, but some of them don't show up and turn into question marks when the list is saved.

The ones that don't show up are from Unicode 6.0 and further versions. There seems to be no problem with previous versions. I noticed this when trying to create a list about basketball. Soccer ball and baseball show up fine, but basketball doesn't. This is not an important issue, but I just wanted to report it anyway.

[AndiPersti]

The reason is that we use utf8 for storing the list name in the database (*), but this character set only uses 3 bytes in MariaDB, i.e. we can only store code points up to FFFF. We would need to use utf8mb4 for storing higher code points.

(*) Actually that is just an educated guess, because I can't reproduce this problem in my dev environment. My sentences_lists table still uses the latin1 character set because it was created with the outdated docs/database/tables/sentences_lists.sql. But 4d05f00 changed the character set for the name column.

[ftumsh]

Having looked into this, I can confirm that changing sentences_lists.name to utf8mb4_general_ci fixes the problem.
How do I submit the SQL to change the column's collation?

[jiru]

@ftumsh Thanks for investigating! 😄 The best way is to create a migration if possible. First, execute cake migrations create FooBar (replace FooBar with an appropriate name) to generate the migration file. Then, edit the file to make it does what you want. Have a look at the migrations documentation.