mariadb - How can i reset mysql root password on linux manjaro

Question

When i try to use :

sudo mysql -u root
use mysql;
SELECT user, plugin FROM user;
UPDATE user SET plugin = "mysql_native_password" WHERE user = "root" ;
exit
service mysql restart`

it seems an error-->

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Update and Alter is not working

question from:https://stackoverflow.com/questions/65895518/how-can-i-reset-mysql-root-password-on-linux-manjaro

Answer 1

It is a very bad practice to modify system data directly instead of using SQL commands.

The solution is to use ALTER USER command (see https://mariadb.com/kb/en/alter-user/) instead of trying to modify a system view.

In recent versions of MariaDB user data is stored in mysql.global_priv table, mysql.user is just a complex view which aggregates information from mysql.global_prov.