Working on the forum software in my testing forum I encountered an error message on the user management page in the admin area. Because of the message, telling me something about a subsequent error, not about the error itself, it took me around two hours to get the cause.
The script asks for a cut-out of the user list and sorts the list by default for the names. To differentiate between "Anni" and "Änni" we once changed the default collation of the field
utf8_bin after a correspondent bug was report. To get a more natural sorting of names, beginning with lowercase or capital letters, the select-query for the user data list alters the collation back to
utf8_general_ci in MLF 2.4.x and to
utf8mb4_general_ci in MLF 2.4.99.x/2.5.
As I learned yesterday, it is possible that the query breaks depending on the settings of the MySQL server and further circumstances. I ran obviously a mix of 2.4.x (which is prepared for the database charset utf8) and database tables with the charset
utf8mb4. So the following query (it's around line #1200 of the curent master) was broken because of a conflict between the charset defied as
utf8mb4 and the wished collation for the column
SELECT user_id, user_name COLLATE utf8_general_ci AS user_name, user_type, … FROM mlf2_userdata ORDER BY user_name ASC LIMIT 0, 25
But even after a check of the column definition and the change of the collation to
utf8mb4_general_ci I got the same error message. Only with a further change to
utf8mb4_unicode_ci (see the next code example) I got results again.
SELECT user_id, user_name COLLATE utf8mb4_unicode_ci AS user_name, user_type, … FROM mlf2_userdata ORDER BY user_name ASC LIMIT 0, 25
I have to investigate, if any server setting prevents the collation
utf8mb4_general_ci to work or if I did anything wrong (it was late at the evening). So this posting is an aide memoire and it's the reason for the "could have an issue" in the subject.
Trenne niemals Müll, denn er hat nur eine Silbe!