Problem
I have a stock install of Debian 11 (testing) with a brand new install of MariaDB (10.5.9-MariaDB-1), phpMyAdmin (5.0.4deb2) and Lighttpd (1.4.59). After adding a user and logging in, clicking on any database and the privileges tab renders the following error:
#1267 - Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '<>'
My question:
This is something to be addressed with Debian at some point, but in the near term is there a global collation setting to fix this error?
The "right" collation is often a holy war. I want something reasonable and which will work until I can look at the collations in more detail.
Further Error Details
This error occurs from a query generated by phpMyAdmin itself, when you click on the privileges tab. The query which generated the error is reproduced below. The query seems to be incorporating data from only from the mysql database's 'db' table and 'user' viee which pull data from 'global_priv'
No change to the stock MariaDB collations has been made. The MariaDB's internal mysql database tables seem themselves by default to be a hot mess of collations.
Tables are mostly utf8_bin but some utf8_general_ci and even one latin1_swedish_ci. Columns within the tables are a similar hot mess, some taking the table's default collation, some being set specifically to utf8_general_ci, and utf8mb4_bin. Both mysql.db and mysql.global_priv are utf8_bin, but one column in global_priv ix utf8mb4_bin and many columns in mysql.db are utf8_general_ci.
Query Generating the Error:
( SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`,
`Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `References_priv`, `Create_tmp_table_priv`,
`Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`,
`Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`, '*' AS `Db`, 'g' AS `Type` FROM
`mysql`.`user` WHERE NOT (`Select_priv` = 'N' AND `Insert_priv` = 'N' AND `Update_priv` = 'N' AND
`Delete_priv` = 'N' AND `Create_priv` = 'N' AND `Drop_priv` = 'N' AND `Grant_priv` = 'N' AND
`References_priv` = 'N' AND `Create_tmp_table_priv` = 'N' AND `Lock_tables_priv` = 'N' AND
`Create_view_priv` = 'N' AND `Show_view_priv` = 'N' AND `Create_routine_priv` = 'N' AND
`Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND `Event_priv` = 'N' AND `Trigger_priv` =
'N')) UNION ( SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`,
`Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `References_priv`,
`Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`,
`Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`, `Db`,
'd' AS `Type` FROM `mysql`.`db` WHERE 'phpmyadmin' LIKE `Db` AND NOT (`Select_priv` = 'N' AND
`Insert_priv` = 'N' AND `Update_priv` = 'N' AND `Delete_priv` = 'N' AND `Create_priv` = 'N' AND
`Drop_priv` = 'N' AND `Grant_priv` = 'N' AND `References_priv` = 'N' AND `Create_tmp_table_priv` =
'N' AND `Lock_tables_priv` = 'N' AND `Create_view_priv` = 'N' AND `Show_view_priv` = 'N' AND
`Create_routine_priv` = 'N' AND `Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND
`Event_priv` = 'N' AND `Trigger_priv` = 'N')) ORDER BY `User` ASC, `Host` ASC, `Db` ASC;
This problem is caused by the phpMyAdmin's default collation_connection setting being "utfmb4_unicode_ci", whereas Debian's default MariaDB server collation is "utfmb4_general_ci". Unfortunately unicode and general sorting orders are not compatible in MariaDB queries and views.
Each new user that logs into phpMyAdmin will need to change their connection collation in phpMyAdmin's home menu to something that is compatible with utfmb4_general_ci.