Resolved Take Two! Error: SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('root'@'localhost') does not exist

[Gene Steinberg]

This is the second time I am posting on a thread about this.

First time brought out the trolls, so here's the deal, all over again:

Following instructions to look for the latest supported version from MariaDB, I ended up with a 10.4.x version.

However, it munges the username, evidently.

So when I attempt to perform any database action, such as adding/removing a user or database I get:

Error: SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('root'@'localhost') does not exist

Alas, the directions for fixing this are essentially unhelpful. Someone at the MariaDB support forum referred me to a document that discusses different options of adding a user, but none specifically designed to fix the above error.

Obviously restoring the server/image/whatever is a non-starter.

Plesk doesn't support anything beyond 10.3.x for now, and I assume that'll change over time (since it's a stable version).

In the meantime, this SQL (???) directive seems to offer a potential solution:

grant all on *.* to 'root'@'%' identified by 'password' with grant option;

But it's not at all clear where I put this (I'm not an SQL expert).

I assume in PHPMyAdmin, but that app in Plesk Obsidian is linked to specific databases, rather than having a "root" version as you do in WHM.

Any guidance?

Again, no trolls please. I assume this will take all of 30 seconds to resole with proper guidance.

Peace,
Gene

 

[IgorG]

Following instructions to look for the latest supported version from MariaDB, I ended up with a 10.4.x version.

Sorry, but I don’t know why you persistently want to make that version of MariaDB work, which is not officially supported.

Software Requirements for Plesk Obsidian

Read all about the software requirements, specifications and other important details to take full advantage of Plesk Obsidian.

docs.plesk.com

In MariaDB 10.4 and later, the mysql.global_priv table has replaced the mysql.user table, and mysql.user is now a view.

But you can try all actions below on your own risk:

Execute the following SQL commands:

mysql> DROP VIEW mysql.user;

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` SQL SECURITY DEFINER VIEW `mysql`.`user` AS select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,'N' AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv`;

 

[Gene Steinberg]

You forget that there doesn’t appear to be a quick way to downgrade.

 

[Gene Steinberg]

It says:

Error
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` SQL SECURITY DEFIN' at line 1

Try again.

 

[Gene Steinberg]

Sorry, but I don’t know why you persistently want to make that version of MariaDB work, which is not officially supported.

Sorry, I don't know why you want to try to force me to have to reload my server because of this horrible transgression. I think it is reasonable to assume that the current stable version will be supported eventually.

Meantime, I did find a workable solution, and have come to realize that you may have had a hidden motive in giving me advice that was wrong.

Says my tech colleague:

Hello Gene

MySQL issue is fixed. I am able to create databases now. I created below database under theparacast.com for testing. You can delete it.

Also I found some mysql operations in the history which you must never do in future. Because there is high possibility that it may break the plesk completely and only option will be to reinstall plesk.

Based on this experience, clearly some of you have a problem giving useful advice when asked.

A word to the wise for others who want help here: Be careful, be very careful.

Gene

 

[IgorG]

Be careful, be very careful.

I would add here - do not repeat my mistakes and do not try to use officially unsupported versions of components.