SQL error when accessing Privileges tab on any database

[relm]

I am getting a SQL error when accessing the privileges table on any database table. Happens on newly created tables too.

It returns the following error:

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

And the query output shows the following (the error box truncates the output a bit):

( 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`, `[...]`

To Reproduce

Steps to reproduce the behavior:

1. Click on database table in the left pane
2. Click Privileges, error shows

Expected behavior

Should load the privileges page.

http://i.picpar.com/28Cd.png

• Operating system: Ubuntu 18.04 LTS

• Database version: 10.4.7-MariaDB-1:10.4.7+maria~bionic-log

• PHP version: PHP 7.2.19-0ubuntu0.18.04.2

• phpMyAdmin version: 4.9.0.1

• Browser: Chrome

• Operating system: macOS 10.14.5

I'm not sure if the fact I have the global defaults set to utf8mb4_unicode_ci for collations has anything to do with this. I don't think that should be an issue though.

[relm]

I fixed it. It seems that MariaDB since version 10.4.1 creates the mysql.user table as a view and have moved storing the actual credentials to a mysql.global_priv table.

For anyone else experiencing this, the problem is this collations in this view don't automatically update when you change collations from the my.cnf.

To fix that, I clicked on the view within phpmyAdmin, then went to Structure -> Edit View. Here, just click on Go to recreate the view. Now the view is using utf8mb4_unicode_ci and I can access the Privileges tab once again since the collations match.

[jordanmiguel]

I fixed it. It seems that MariaDB since version 10.4.1 creates the mysql.user table as a view and have moved storing the actual credentials to a mysql.global_priv table.

For anyone else experiencing this, the problem is this collations in this view don't automatically update when you change collations from the my.cnf.

To fix that, I clicked on the view within phpmyAdmin, then went to Structure -> Edit View. Here, just click on Go to recreate the view. Now the view is using utf8mb4_unicode_ci and I can access the Privileges tab once again since the collations match.

This is really useful. Worked here!

[vesamet]

There was some errors when I did what relm suggested, but it effectively worked as well. I just hope it didn't mess up with something crucial...

[vishwajeetmallick]

I fixed it. It seems that MariaDB since version 10.4.1 creates the mysql.user table as a view and have moved storing the actual credentials to a mysql.global_priv table.

For anyone else experiencing this, the problem is this collations in this view don't automatically update when you change collations from the my.cnf.

To fix that, I clicked on the view within phpmyAdmin, then went to Structure -> Edit View. Here, just click on Go to recreate the view. Now the view is using utf8mb4_unicode_ci and I can access the Privileges tab once again since the collations match.

[vishwajeetmallick]

Thank you. This solved the problem. March 4, 2020

• phpMyAdmin 5.0.1
• Server: Localhost via UNIX socket
  Server type: MariaDB
  Server connection: SSL is not being used Documentation
  Server version: 10.4.12-MariaDB - MariaDB package
  Protocol version: 10
  User: root@localhost
  Server charset: UTF-8 Unicode (utf8mb4)

[wedi]

Can't this be handled by phpMyAdmin? I checked adminer and it works without a problem. It took me half an hour till I finally found this issue. It works but messing with the mysql schema makes me feel really uncomfortable.

PhpMyAdmin version 5.0.2 (docker image tagged latest as of writing).

$ mysql --version
mysql  Ver 15.1 Distrib 10.5.4-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[usb248]

Still here in 5.0.2 & MariaDB 10.5.4....

[ismet]

Still ongoing issue for phpMyAdmin v5.0.4 and MariaDB v10.5.6.

Btw @relm 's method solves it manually.

[ibennetch]

It seems this is related to the "Server connection charset" dropdown on the main page; if set to utf8mb4_general_ci, then the collations match and no error is shown. However, the default (at least for the Docker image and my Debian system) is utf8mb4_unicode_ci, which does cause the error (and presumably any other setting would fail here, as well).

Rather than changing the view, I believe a better solution would be for phpMyAdmin to force the comparison string (the 'N' in the query) to the correct collation.

[ibennetch]

For fellow developers, here's an example of the failing query:

            (
                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 'tryme' 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;