mysqlmariadb

How to set root password in MariaDB 10.4 on macOS?

I have just installed MariaDB 10.4 on macOS Mojave and I now need to set a password for the root user. I read on other SO questions that I can achieve this via

sudo mysql -u root
MariaDB [mysql]> UPDATE mysql.user SET authentication_string = PASSOWRD('mypassword') WHERE user = 'root';

but this raises error

ERROR 1348 (HY000): Column 'authentication_string' is not updatable

and if I try

UPDATE mysql.user SET Password=PASSWORD('mypassword') WHERE User='root';

raises error

ERROR 1348 (HY000): Column 'Password' is not updatable

This

set password for 'root'@'localhost' = 'mypassword';

throws

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

and this

alter user 'root'@'localhost' identified with mysql_native_password by 'mypassword';

throws

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version...

How can I solve this?

Solution

  • Since MariaDB 10.4 mysql.global_priv has replaced mysql.user. Latter one is now a view, which can't be updated anymore.

    Since you already logged in as root, keep it simple and change your password with:

    SET PASSWORD=PASSWORD('mypassword')

    Since the user can't be authenticated over socket anymore, the authentication method will be automatically set to mysql_native_password.