problem

Database alter file 371-TRANSLATE-2988-fix-opentm2-prefix-usage.sql produces an error with MariaDB > 11.5.1.

 [ERROR] SQLSTATE[HY000]: General error: 1270 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT),                   
         (utf8mb4_bin,NONE), (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation 'replace'                                          
         Error on Importing a SQL file.; called file:
/var/www/translate5/application/modules/editor/database/371-TRANSLATE-2988-fix-opentm2-prefix-usage.sql

reason

https://jira.mariadb.org/browse/MDEV-25829

The default collation was changed and in that todo some other internals too.

This was leading with the usage of user variables in the alter file to that error message.

solution

Fixing that on our side by adding an explicit collation to the usage of the variable. Still it is not clear if that is desired behaviour in MariaDB since when the charset if the connection is utf8mb3 instead utf8mb4 all is fine.

additional todo

Understanding the backgrounds of the problem (finally the found MDEV-25829) and coming to the conclusion that this should not be a bigger problem since not using user variables very often.