Weirdness around COLLATION between MariaDB 10.11 vs 11.6

[restena-sw]

Hello!

We have recently upgraded our MariaDB backend, which the sqlattribs module uses for getting user attributes.

It turned out that this major version upgrade introduced breakage inside the module, at an entirely unexpected place:

simplesamlphp-module-sqlattribs/src/Auth/Process/AttributeFromSQL.php

Line 142 in b036403

$db->exec("SET NAMES 'utf8'");

Not being in-depth into MariaDB, my understanding is that there used to be a default encoding for UTF-8 which used three bytes (utf8mb3) but that default changed in 11 towards a (more proper) four-byte representation, utf8mb4. As it is an upgrade, the existing DB remained on utf8mb3.

The concrete error we got was not on the referenced statement, which seemed to go okay, but on a subsequent query:

Caused by: SimpleSAML\Error\Exception: AttributeFromSQL: execute(value1, value2) failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb3_general_ci,COERCIBLE) and (utf8mb3_uca1400_ai_ci,COERCIBLE) for operation '='

uca1400_ai_ci ? Nothing I've ever heard of.

What I can say is that the fix was a patch to the referenced line:

$db->exec("SET NAMES 'utf8'");
+$db->exec("SET NAMES 'utf8' COLLATE 'utf8mb3_general_ci'");

Not sure what the way forward here is. Adding a conditional check in the module to catch such utter weirdness? Or rather change our collations to utfmb4 so that it matches the default? Maybe the module documentation should have some advice in that regards.

[ghalse]

I guess one way to handle this would be to remove the set names in favour of putting it in the DSN. Or perhaps, for better backwards compatibility, not sending the set names if a charset was in the DSN. It would be useful to know if commenting that out and adding ;charset=utfmb3 to the DSN helped?

I do think that now utf8mb4 is the recommended default for both mariadb and mysql, maybe that should be the default in set names.

[restena-sw]

Our MariaDB guru meanwhile shed some extra light on this. The problem is NOT the charset; both client and server agree that they talk utfmb3. The error message is specifically about the applied collations:

(utf8mb3_general_ci,COERCIBLE)
(utf8mb3_uca1400_ai_ci,COERCIBLE)

Apparently the COERCIBLE suffix is the problem: it seems to mean "insist on this one, and coerce anything else into this one". Now both the client and the server insist on one collation, and the two collations are different. Hence the fatal mismatch.

Funny thing is that I have no idea where the COERCIBLE flag comes from. I don't consciously set it client-side (there is no applicable my.cnf at all). Maybe it's MariaDB client-side default? Or something in SSP sets it deliberately?