Avatar

User list query could have an issue in the 2.5 branch (Technics)

by Auge ⌂, Wednesday, July 07, 2021, 10:48 (996 days ago)

Hello,

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 by the names. To differentiate between "Anni" and "Änni" we once changed the default collation of the field mlf2_userdata.user_name from utf8_general_ci to 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 defined as utf8mb4 and the wished collation for the column user_name.

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.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

RSS Feed of thread