Search code examples
mysqlmariadbplesk

Cannot create or delete db on Plesk server

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 'password';

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

Solution

  • Seems root@lo...got removed when you upgraded MariaDB few days ago.

    Choice 1: It would have removed root@127... also and you would have used both root user references to your databases, connected dbs, views, etc. Views mainly as the error points to that also. So try this too.

    CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'password';

    Choice 2: After re-creating root, try

    flush privileges

    If not Ok, you shall restart mysql and try.

    systemctl restart mariadb.service