MariaDB password reset not working

Question

I am running MariaDB and i am trying to reset password but its not working.

[root@osdial-99a8c941 ~]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 11125

Next set password:

[root@osdial-99a8c941 ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.37-MariaDB-wsrep-log MariaDB Stylite Build (GPL), wsrep_25.10.r3980

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set password=PASSWORD("new-password") where User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MariaDB [mysql]> select * from user;
Empty set (0.00 sec)

If you look user table is still empty :(

Even i tried to create account but got following error:

MariaDB [mysql]> CREATE USER 'root'@'localhost' IDENTIFIED BY 'new-password';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

What i am doing wrong??

Solution

FLUSH PRIVILEGES; is needed to load the password table. Do this after the UPDATE or GRANT.

See the User manual. (MariaDB and MySQL should be identical in this area.)

Comments

[Matt Clark]

actually, this should be done after the update or grant as it flushes the in memory cache, and reloads the privileges from the table. Per MySQL Documentation

[Rick James]

Belatedly fixed Answer to align with Matt's comment.