phpMyAdmin的库存安装出现“非法混合排序规则”错误

1aaf6o9v  于 2022-11-09  发布在  PHP
关注(0)|答案(2)|浏览(198)

问题

我有一个Debian 11的库存安装(测试),有一个全新的MariaDB(10.5.9-MariaDB-1),phpMyAdmin(5.0.4deb2)和Lighttpd(1.4.59)安装。在添加用户并登录后,点击任何数据库和权限选项卡都会出现以下错误:


# 1267 - Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '<>'

我的问题:

这是Debian在某个时候要解决的问题,但是在近期是否有全局排序规则设置来修复这个错误?
“正确”的排序往往是一场圣战。我想要一些合理的东西,这将工作,直到我可以更详细地看排序。

进一步的错误详细信息

当您单击特权选项卡时,phpMyAdmin自身生成的查询会导致此错误。生成此错误的查询如下所示。该查询似乎只合并了mysql数据库的'db'表和'user'视图中的数据,而这些视图从'global_priv'中提取数据
MariaDB的内部mysql数据库表在默认情况下看起来是一堆混乱的排序规则。
大多数表都是utf8_bin,但也有一些是utf8_general_ci,甚至还有一个latin1_swedish_ci。表中的列也是一团乱麻,有些采用了表的默认排序规则,有些被专门设置为utf8_general_ci和utf8mb4_bin。mysql.db和mysql.global_priv都是utf8_bin,但global_priv ix中的一列是utf8mb4_bin,mysql.db中的许多列是utf8_general_ci。

生成错误的查询:

( 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;
egdjgwm8

egdjgwm81#

这个问题是由phpMyAdmin的默认collation_connection设置为“utfmb4_unicode_ci”引起的,而Debian的默认MariaDB服务器排序规则是“utfmb4_general_ci”。不幸的是,Unicode和一般排序顺序在MariaDB查询和视图中不兼容。
每个登录到phpMyAdmin的新用户都需要在phpMyAdmin的主菜单中将他们的连接排序规则更改为与utfmb4_general_ci兼容的排序规则。

xlpyo6sf

xlpyo6sf2#

由于Debian MariaDB上的排序规则不同,只需重新创建mysql.user视图。

相关问题