ERROR 1064 (42000): ... for the right syntax to use near 'password("")' at line 1

[kimhxsong]

MariaDB Info

(main)⚡ % mariadb --version                           ~/Inception/srcs/mariadb
mariadb  Ver 15.1 Distrib 10.8.3-MariaDB, for osx10.17 (arm64) using  EditLine wrapper

Error

(main)⚡ [1] % mysql -uroot                            ~/Inception/srcs/mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.29 Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> set password for 'root'@'localhost' = password("");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password("")' at line 1
MySQL [(none)]>

Example For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

If you want to delete a password for a user, you would do:

SET PASSWORD FOR 'bob'@localhost = PASSWORD("");

Reference: https://mariadb.com/kb/en/set-password/

Solved

(main)⚡ % mysql -uroot
MySQL [(none)]> alter user 'root'@'localhost' identified with mysql_native_password by 'db1234';
Query OK, 0 rows affected (0.006 sec)

MySQL [(none)]> exit
Bye
(main)⚡ % mysql -uroot -pdb1234                       ~/Inception/srcs/mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.29 Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]>

To change the root password, type the following at the MySQL/MariaDB command prompt:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd';
flush privileges;
exit;

Reference: Configuring a default root password for MySQL/MariaDB