Collation for MySQL Database Connection

Collation for MySQL Database Connection

by John Percival -
Number of replies: 0

Hello, I know collations are pretty tricky, but can someone help me out here? I've discovered that the MySQL connection used by Moodle does not have the collation set to match the one in config.php


This means very occasionally a query fails with the error

"Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'like'"

e.g.,

SELECT 'bob'
FROM
prefix_user user
INNER JOIN 
(SELECT @varname := 'john%' 
/* remove next line to generate error
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'like'
*/
 COLLATE utf8mb4_unicode_ci
/* remove line above to generate error */
) varset
WHERE user.username LIKE @varname

So my question is whether there is a good reason for /lib/dml/mysqli_native_moodle_database.php#L594 not to set the collation immediately after setting the character set:

        $this->mysqli->set_charset($charset);
        $this->mysqli->query("SET collation_connection = " . $collation);

I thought I'd check this out on the forum first before posting anything in the tracker...

Thanks!



Average of ratings: -