Issue Can't add or remove any databases

[stevland]

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 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

 

[Brujo]

well you can try to run via cli # plesk repair db
about mariadb 10.4 you have seen and read the warnings about supported versions: How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.0/10.1/10.2 on Linux?

 

[stevland]

well you can try to run via cli # plesk repair db
about mariadb 10.4 you have seen and read the warnings: How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.0/10.1/10.2 on Linux?

Yes, thank you I did read those warnings and I used the instructions on that page to perform the upgrade. I didn't create a server snapshot though.

I ran plesk repair db and it removed a bunch of unnecessary rows from quite a few tables. Unfortunately my issue persists.

Any other ideas?

 

[IgorG]

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

I do not know what did you do for this error message, but note that there is no root user for mysql in Plesk. Use admin user instead.

 

[stevland]

Thank you for your help, guys.

If anyone else runs into these issues, here is what worked for me.

# plesk db
MariaDB [psa]> CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
MariaDB [psa]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
MariaDB [psa]> FLUSH PRIVILEGES;
MariaDB [psa]> exit;

 

[IgorG]

CREATE USER 'root'@'localhost'

Why do you need a root user?

 

[stevland]

Why do you need a root user?

Honestly, I have no idea. I'm not a db guy. This is all way over my head.

I had some help here:

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 so...

stackoverflow.com

Is there any danger with creating a root user?

 

[IgorG]

Is there any danger with creating a root user?

As I said above there is admin user instead of root in Plesk. So adding another unnecessary user with admin privileges look like a not good idea from the security point of view and can lead to unpredictable consequences in further Plesk usage.

 

[stevland]

I appreciate your concern. Thank you.

You've had a chance to see everything I've posted here. If you were me, what would you do?

 

[IgorG]

If you were me, what would you do?

I would not add a root user for sql server.

 

[stevland]

Fair enough. Please understand that I know very little about MySQL. I have no idea what went wrong, or even how I fixed it.

Are you suggesting that I remove the root user?

And, if yes, do you think it will be possible for me to apply the same fix using the admin user?

# plesk db
MariaDB [psa]> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
MariaDB [psa]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
MariaDB [psa]> FLUSH PRIVILEGES;
MariaDB [psa]> exit;

 

[IgorG]

Yes, remove root mysql user and if you meet any error after that related to mysql permissions post it here in a separate thread with all necessary details.

 

[stevland]

Copy that. I will do so when time permits. Thank you.

 

[Bitpalast]

The root user was probably necessary, because you had added views into a database to which only a user named "root" would have access. Further, MariaDB 10.4 is unsupported. The highest valid version on Plesk Onyx is 10.2, on Obsidian 10.3.

 

[AlanD]

I have the same problem after an upgrade to MariaDB 10. Igor, I think you are misunderstanding slightly. I too get the error message about root user, same as Steve, but we are not trying to use root. I've never used root as the MySQL user but no matter what user name I try to specify when creating the database, the error message says

"The user specified as a definer ('root'@'localhost') does not exist"

Existing databases created in the earlier version of MySQL continue to work okay and can be accessed with phpMyAdmin etc, but it is impossible to creater new databases.

 

[Brujo]

I have the same problem after an upgrade to MariaDB 10.

Please take into consider to name the exact Version of MariaDB

 

[AlanD]

We have now got to the bottom of our situation. Like many people, we rent dedicated servers which are supplied preconfigured with Plesk, Apache, etc. We had a few small problems importing a website which had been running fine on MySQL 5.6 on another server. We realised our server had been set up with the very much out of date and out of support MySQL 5.5, and asked our provider if they could upgrade us to 5.6? They said they would install MariaDB 10.x for us. They installed the update and told us they had installed the latest stable release of MariaDB. At the time we had no reason to question that. It was only some weeks later when we tried to import the database that we discovered there was now this huge problem, and then later, as a result of info on pages like this one, and the link Brujo supplied, made the connection that it was to do with the earlier upgrade and found out that the version of Plesk we have installed (Onyx 17.8.11) only supports up to 10.2, and we had 10.4. After a lot of pain we have managed to get 10.2 installed and working properly but it was far from straightforward. Thankfully, we had our own independent backups of our databases.

So if anyone else arrives here, as I did, after Googling the error message, before you do anything else, make sure you have backups of all your databases, and then check the version numbers of Plesk and MySQL/MariaDB. For those of us who want to avoid command lines and switches etc, the version number of your plesk installation is on the home page of your plesk hosting panel. To find the version of MySQL/MariaDB you are using, go into Databases, click on Database Servers, and click on localhost and the info is listed on there. Alternatively, go into phpMyAdmin and enter the SQL command

select version()

At the time of writing this, Onyx needs Maria 10.2 or earlier, Obsidian needs Maria 10.3 or earlier.

 

[Cebina]

hallo,i get this error if i want create database user and password,
SQLSTATE[HY000]: General error: 1290 The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement.
br Cebina