Cannot create or delete db on Plesk server

Question

Today I came to the realization that I cannot add or delete any of the databases that belong to any of my clients on my Plesk server.

I had been receiving this error:

Error: Unable to remove some of the selected databases. The user specified as a definer ('root'@'localhost') does not exist

So I found and ran this fix:

# plesk db
# CREATE USER 'root'@'localhost' IDENTIFIED BY 'typepasswordhere';

Now when trying to add or remove a database I receive a new error:

Error: Connection to the database server failed: View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

(I'm not sure if it is related or not, but I updated MariaDB a few days ago. I'm pretty sure that I created a new database after doing so though).

‪CentOS Linux 7.7.1908 (Core)‬ Plesk Onyx Version 17.8.11 Update #80 mysql 10.4.11-1.el7.centos 10.4.11-MariaDB MariaDB Server

Answer 1

A bit late, but may be useful for someone else.

There is a reported MariaDB bug where the secure installation which is required by Plesk is not working correctly.

Starting from 10.4, mysql_secure_installation gets stuck at the very beginning of execution if the database doesn't have root@localhost and the script is executed under another superuser.

It results in the impossibility to switch the definer from 'root' to another user (admin like it is required by Plesk)

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

You have no root user because it was renamed by the initial Plesk installation to the admin user

So, as a result of this Plesk could not connect to the database, because the definer was not changed from root to admin due to secure installation failed during installation or update

And any action fails with this error:

[root@hostname ~]# plesk db
...
MariaDB [psa]> SELECT User FROM mysql.user;
ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist

The solution for this is to create a root@localhost user:

MariaDB [psa]> CREATE USER root@localhost IDENTIFIED VIA unix_socket USING 'invalid';
Query OK, 0 rows affected (0.015 sec)

In order not to receive this error:

Error: Connection to the database server failed: View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Grant privileges:

MariaDB [psa]> GRANT SELECT ON *.* TO root@localhost IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.533 sec)simply grant privileges for the new user:

From this the database operations will be working in Plesk. But it's good to remove an unnecessary user with full database access (good for security) you need to alter the DEFINER back to admin (I've done it manually in the file, but you may perform it via ALTER VIEW in MySQL):

[root@localhost]# grep root /var/lib/mysql/mysql/*.frm
user.frm:definer_user=root
>>>
[root@localhost]# grep root /var/lib/mysql/mysql/*.frm
user.frm:definer_user=admin

Flush tables to apply changes:
MariaDB [psa]> FLUSH TABLES;

Query OK, 0 rows affected (0.003 sec)

After that ensure that definer is changed:

MariaDB [psa]> SHOW CREATE VIEW mysql.user;
...
| user | CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` 

And remove the root database user:

MariaDB [psa]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'root'@'localhost';

Query OK, 0 rows affected (0.114 sec)



MariaDB [psa]> DROP USER 'root'@'localhost';

Query OK, 0 rows affected (0.025 sec)

Check Plesk DB actions and make sure they are all working fine.