The same database is opened each time when accessing different databases via phpMyAdmin in Plesk

Symptoms

• Several database users are created and they are correctly configured to have access to all databases for the domain

• For one specific database user, only one specific database is opened each time when accessing any of the different databases he has permissions to access via Plesk > Domains > example.com > Databases > press on phpMyAdmin for any of the databases

Cause

The config_data column of the pma__userconfig table within the database named  phpmyadmin for the affected user contains "Server\/hide_db" and "Server\/only_db":"db_name" among other configurations, which are responsible for this behavior:

MariaDB [phpmyadmin]> select * from pma__userconfig where username="admin";
+----------+---------------------+-----------------------------------------------------------+
| username | timevalue           | config_data                                               |
+----------+---------------------+-----------------------------------------------------------+
| admin    | 2023-10-20 12:22:56 | {…,"Server\/hide_db":"","Server\/only_db":"wp_example",…} |
+----------+---------------------+-----------------------------------------------------------+

MariaDB [phpmyadmin]> select * from pma__userconfig where username="admin";
+----------+---------------------+-----------------------------------------------------------+
| username | timevalue           | config_data                                               |
+----------+---------------------+-----------------------------------------------------------+
| admin    | 2023-10-20 12:22:56 | {"Console\/Mode":"collapse"}                              |
+----------+---------------------+-----------------------------------------------------------+

Resolution

In order to resolve the issue, you would need to update the config_data for the affected database user within the phpmyadmin database by following these steps:
 
1. Log into the server via SSH as the root user
2. Make a database backup for the phpmyadmin database:

# plesk db dump phpmyadmin > /root/plesk_phpmyadmin_dump.sql

3. Enter MariaDB:

# plesk db

4. Switch to the phpmyadmin database:

MYSQL_LIN: use phpmyadmin;

5. Check the current config_data for the selected database user name (admin in the example below) by executing the following command:

MYSQL_LIN: select * from pma__userconfig where username="admin";

The output would be similar to the following:

+----------+---------------------+-----------------------------------------------------------+
| username | timevalue           | config_data                                               |
+----------+---------------------+-----------------------------------------------------------+
| admin    | 2023-10-20 12:22:56 | {…,"Server\/hide_db":"","Server\/only_db":"wp_example",…} |
+----------+---------------------+-----------------------------------------------------------+

6. Remove the Server\/hide_db" and "Server\/only_db":"db_name from theconfig_data options for the affected user and leave all others by using a command that is similar to the following:

MYSQL_LIN: update pma__userconfig set config_data='"Console\\/Mode":"collapse"' where username='admin';

7. Exit MariaDB

MYSQL_LIN: exit